Oracle stored procedure in PHP

[Anon]

hello everyone--
I'm trying to call an oracle stored procedure in php and I keep getting the error below:

Warning: OCIStmtExecute: ORA-06550: line 1, column 9: PLS-00103: Encountered the symbol "" when expecting one of the following: begin function package pragma procedure subtype type use cursor form current on line 122

I've checked my syntext and everything and I don't have a clue as to where the ""s are coming from. Has anyone done stored procedures in php that has any knowledge about how to even get them to work?

Thanks,
Jay

[Anon]

I would like to see your code, how you are calling it.

[Anon]

Here's the code I'm trying to use but I get the warning (see previous message).

Thanks,
Jay Paulson

//inserts this data into the db
$p_acct_no = 1160000001;
$p_div = 116;
$p_CDE_ACTION = "Order";
$p_name = "Php Test";
$p_PHONE_NO = 6914417;
$p_PHONE_TYPE = "PHONE";
$p_login_id = "WEB";
$p_commit = "Y";

//this should be set to NULL but won't work
//if it's an empty string "" or if it's set
//to "NULL"
$p_contact_id = 0;

//connect to db
$conn = OCILogon("username","password","db");
$curs = OCINewCursor($conn);
$SQL = "DECLARE 😛_RETURN_CODE NUMBER; 😛_RETURN_MSG VARCHAR2; begin NAC_CONTACT_ADD(😛_RETURN_CODE, 😛_RETURN_MSG, :p_acct_no, :p_div, :p_CDE_ACTION, :p_name, :p_PHONE_NO, :p_PHONE_TYPE, :p_login_id, p_contact_id, :p_commit); end;";
$stmt = OCIParse($conn,$SQL);

//the return values from the stored procedure
ocibindbyname($stmt,"P_RETURN_CODE",&$curs,-1,OCI_B_CURSOR);
ocibindbyname($stmt,"P_RETURN_MSG",&$curs,-1,OCI_B_CURSOR);

//the values inserted into the db
ocibindbyname($stmt,"p_acct_no",&$p_acct_no,-1);
ocibindbyname($stmt,"p_div",&$p_div,-1);
ocibindbyname($stmt,"p_CDE_ACTION",&$p_CDE_ACTION,-1);
ocibindbyname($stmt,"p_name",&$p_name,-1);
ocibindbyname($stmt,"p_PHONE_NO",&$p_PHONE_NO,-1);
ocibindbyname($stmt,"p_PHONE_TYPE",&$p_PHONE_TYPE,-1);
ocibindbyname($stmt,"p_login_id",&$p_login_id,-1);
ocibindbyname($stmt,":p_contact_id",&$p_contact_id,-1);
ocibindbyname($stmt,"p_commit",&$p_commit,-1);

ociexecute($stmt);
ociexecute($curs);

OCIFreeCursor($curs);
OCIFreeStatement($stmt);
OCILogoff($conn);

[Anon]

Jay,

Our guess is that you ara not using a Pragma to handle the SQL embbeded in the ociexecute($stmt) function called from within your procedure.

-- **************************************************************************

--
-- PRAGMA is required to guarantee to the SQL engine
-- that a function can be embedded in an SQL statement without doing
-- any database or package updates.
--
PRAGMA RESTRICT_REFERENCES ( function_name,[RNDS],[WNDS],[WNPS],[RNPS] );

Refer to Oracle 8 PL/SQL Programing pgs (75,300-02,928-29)
If you can not get a copy I will fax pertinent info.

Basicly..
"this pragma is used to assert the purity level for user-defined functions and
methods. In order to be used in SQL statements, you must guarantee to the PL/SQL engine
that the function does not modify any database or package state."

Good luck and keep me posted.

Thanks,
John

[Anon]

Well just to let you all know here's the code for stored procedures in oracle. I actually got it working 🙂

Jay

//inserts this data into the db
$p_acct_no = 1160000001;
$p_div = -116;
$p_CDE_ACTION = "Order";
$p_name = "Php Test";
$p_PHONE_NO = 6914417;
$p_PHONE_TYPE = "PHONE";
$p_login_id = "WEB";
$p_commit = "Y";

//connect to db
$conn = OCILogon("username","password","db");
$SQL = "begin NAC_CONTACT_ADD(😛_RETURN_CODE, 😛_RETURN_MSG, :p_acct_no, :p_div, :p_CDE_ACTION, :p_name, :p_PHONE_NO, :p_PHONE_TYPE, :p_login_id, null, :p_commit); end;";
$stmt = OCIParse($conn,$SQL);

//the return values from the stored procedure
ocibindbyname($stmt,"P_RETURN_CODE",&$P_RETURN_CODE,28);
ocibindbyname($stmt,"P_RETURN_MSG",&$P_RETURN_MSG,255);

//the values inserted into the db
ocibindbyname($stmt,"p_acct_no",&$p_acct_no,-1);
ocibindbyname($stmt,"p_div",&$p_div,-1);
ocibindbyname($stmt,"p_CDE_ACTION",&$p_CDE_ACTION,-1);
ocibindbyname($stmt,"p_name",&$p_name,-1);
ocibindbyname($stmt,"p_PHONE_NO",&$p_PHONE_NO,-1);
ocibindbyname($stmt,"p_PHONE_TYPE",&$p_PHONE_TYPE,-1);
ocibindbyname($stmt,"p_login_id",&$p_login_id,-1);

ociexecute($stmt);

OCIFreeStatement($stmt);
OCILogoff($conn);