oracle procedure calling

Anon

i am trying to execute an oracle procedure using php - there occurs an error while executing the sql query. i need help urgently. can someone please help. - preethi

Anon

I'm no oracle expert, but how are we supposed to help if you only tell us that you have a problem?

What code did you use? what is the error?

Anon

i used the regular oci statements - defined an sql statement which executes the procedure. when i try to execute this statement, i am getting an error message. is there a way to execute oracle procedures in oracle or can we execute it as other sql statements??

Anon

Again I ask, WHAT is the error that you get?

Anon

"Warning: OCIStmtExecute: ORA-00900: invalid SQL statement in d:\htdocs\jdglink.php3 on line 5
couldn't execute" is the error i get. the statement is
$sql="EXEC FETCHP_TXVP($id)";
$stat=OCIParse($con,$sql) or die("couldn't parse");
OCIExecute($stat) or die("couldn't execute");
the error occurs at the ociexecute statement.

Anon

the sql statement for executing procedures works in oracle. is there any limitation as to what statements ociexecute allows??

Anon

There are sql statments that oracle (sqlplus) will execute but thei are not normal sql. exec i suppose is one of those.

to execute a stored proc you go like this

$sql = "BEGIN FETCHP_TXVP($id); END;";
stat=OCIParse($con,$sql) or die("couldn't parse");
OCIExecute($stat) or die("couldn't execute");

assuming FETCHP_TXVP is the name of the stored procedure you're trying to execute. but i don't see how would that help you since you are supposed to get a result back, and I don't see it in the code. If this is a function returning some value try this

$sql = "BEGIN :ret = FETCHP_TXVP($id); END;";
stat=OCIParse($con,$sql) or die("couldn't parse");
ocibindbyname($stat,"ret",&$ret);
// if result is a varchar give length
// ocibindbyname($stat,"ret",&$ret,200);

OCIExecute($stat) or die("couldn't execute");

this would work on all normal types but not on lobs, cursors.

hope this helps

Anon

i get this error message in the sql statement : -
OCIStmtExecute: ORA-06550: line 1, column 14: PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; indicator in d:\htdocs\jdglink.php3 on line 7
couldn't execute

i tried it this way
$sql="BEGIN fetchp_txvp(:inid); END; ";
ocibindbyname($stat,":inid",&$id);
- however &id is the parameter to the procedure - will it work?? i am having trouble however with fetching the results (the procedure may return an array of results) into an array using ocifetchinto. i created a new cursor for this purpose and executed the new cursor. however i get an error : -
OCI_INVALID_HANDLE

what do i do??

Anon

Yes i made a mistake, the sql should be like this

$sql = \"BEGIN :ret := FETCHP_TXVP($id); END;\";

if you want me to help you you have to give me some more info, what are the parameters , to the procedure, is it a function or a procedure, is id an IN/OUT param or just in

Anon

i tried this - but still i get te same error while fetching into a new cursor - invalid oci handle. $id is an in parameter to the oracle procedure. the oracle procedure may return one value or an array of values.

Anon

Well i'm still not getting somthing if it is a procedure how is it returning the value, maybe it's a function, and what is the condition to whether the result is one or many. and still how can you have different data type returned, maybe you mean that it can return an array ONE element.

yavo

Anon

hi!! sorry for not being clear. the oracle call is a procedure returning only one value and taking as its input the id which is a number and returning a string. how do i retreive the value??