I have been messing with this code for weeks now and read a ton of forums and archives and I can't figure this out for the life of me.... I included the Stored Procedure and the PHP code and the ERROR I am getting below. Someone else in my company wrote the stored procedure. I am running PHP Version 4.3.9
THANK YOU SO MUCH TO ANYONE IN ADVANCE!
Oracle Stored Procedure
Procedure GetDBCodes(appl_type In Varchar2,
output_array Out str_varray,
rtn_cd Out Number,
rtn_errm Out Varchar2,
rtn_spinfo Out Varchar2) As
-- Program ID: GetDBCodes
-- Author: Chris Calder
-- Date Written: October 5, 2004
-- System: Engage Thoughtware
-- Purpose: Get the database codes for the application identified by appl_type
-- Arguments: IN: appl_type 'ETW' = ThoughtTree
-- 'ADM' = System Administration
-- OUT output_array array of fixed length delimited output:
-- company_name: 1 - 38
-- user_name: 39 - 48
-- db_code: 49 - 58
-- db_version 59 - 66
-- OUT rtn_cd: SQL return code
-- OUT rtn_errm: Oracle error message text
-- OUT rtn_spinfo: debugging info consisting of:
-- procedure name and input parameter list,
-- followed by the label of the SQL
-- statement that caused the exception
sql_stmt Varchar2(100);
db_schema_name Varchar2(10);
company_name Varchar2(40);
db_version Varchar2(10);
counter Number Default 0;
lv_appl_type Varchar2(10);
stmt_label Varchar2(40);
data_error Exception;
Begin
stmt_label := '<<get_dbcodes>>';
output_array := str_varray();
lv_appl_type := appl_type || '%';
For code_rec In (Select schema_name,
db_code,
dblink
From etw$map.DBCode
Where (dblink <> 'UTDEV' Or dblink Is Null)
And schema_name Like lv_appl_type
Order By substr(schema_name,
4,
3))
Loop
db_schema_name := 'ETWDB' || substr(code_rec.schema_name,
4,
3);
If code_rec.dblink Is Null Then
sql_stmt := 'select db_node_name, db_version from ' ||
db_schema_name || '.masterprofile';
Else
sql_stmt := 'select db_node_name, db_version from ' ||
db_schema_name || '.masterprofile' || '@' ||
code_rec.dblink;
End If;
Execute Immediate sql_stmt
Into company_name, db_version;
-- add arrary extend and assignment here
output_array.Extend;
counter := counter + 1;
output_array(counter) := rpad(company_name,
38,
' ') || rpad(code_rec.schema_name,
10,
' ') ||
rpad(code_rec.db_code,
10,
' ') || db_version;
End Loop;
Commit;
rtn_errm := 'NONE';
rtn_cd := Sqlcode;
rtn_spinfo := 'CodeMap.GetDBCodes' || ' successful';
Exception
When data_error Then
Rollback;
rtn_errm := 'Appl_type value: ' || appl_type || ' is not valid';
rtn_cd := -999;
rtn_spinfo := 'CodeMap.GetDBCodes' || ' unsuccessful';
When Others Then
--** select from value table failed
Rollback;
rtn_errm := Sqlerrm;
rtn_cd := Sqlcode;
rtn_spinfo := 'CodeMap.GetDBCodes ' || stmt_label;
End GetDBCodes;
PHP Calling the Oracle Stored Procedure
<?php header("cache-control: no-store, no-cache, must-revalidate"); header("Pragma: no-cache");
$con = OCILogon('user', 'pass', 'LINUX_CONSONUS');
$query = "BEGIN codemap.GETDBCODES('ETW',:results,:cd,:errm,:spinfo); END;";
$stmt = OCIParse($con, $query) or die ('Can not parse query');
OCIBindByName($stmt,":results", $output_array,-1);
OCIBindByName($stmt,":cd", $rtn_cd,-1);
OCIBindByName($stmt,":errm", $rtn_errm,-1);
OCIBindByName($stmt,":spinfo", $rtn_spinfo,-1);
OCIExecute($stmt);
?>
ERROR I AM GETTING
Warning: ociexecute(): OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GETDBCODES' ORA-06550: line 1, column 7: PL/SQL: Statement ignored in c:\inetpub\wwwroot\Projects\PopUpRef\v2\php\testOracle.php on line 17
