Re: Oracle stored procedure help...

On Thu, Sep 14, 2000 at 10:00:14AM -0500, Paulson, Joseph V. Jay wrote: > 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? > > Here's the code I'm trying to use but I get the warning. > > 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"; > > //connect to db > $conn = OCILogon("username","password","db"); > $curs = OCINewCursor($conn); don't need OCINewCursor just do: $stmt = OCIParse($conn,"begin NAC_CONTACT_ADD(:P_RETURN_CODE, :P_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;"); declare is not sql - it's sqlplus'es way of creating bind variables. ocibindbyname($stmt,"P_RETURN_CODE",&$P_RETURN_CODE,28); ocibindbyname($stmt,"P_RETURN_MSG",&$P_RETURN_MSG,255); .. rest of the binds ... why would you want to use a cursor when you're expections a NUMBER and VARCHAR2 back? tc > $SQL = "DECLARE :P_RETURN_CODE NUMBER; :P_RETURN_MSG VARCHAR2; begin > NAC_CONTACT_ADD(:P_RETURN_CODE, :P_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",&$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_commit",&$p_commit,-1); > > ociexecute($stmt); > ociexecute($curs); > > OCIFreeCursor($curs); > OCIFreeStatement($stmt); > OCILogoff($conn); > > > Jay Paulson > Developer, Web Technologies > Viatel, Inc. > Phone: 979-691-4417 x4717 > http://www.viatel.com > 3383 S. Texas Avenue, Suite 280 > Bryan, Texas 77802 > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net -- Thies C. Arntzen "One Big-Mac, Small Fries and a Coke!" Digital Collections Phone +49 40 235350 Fax +49 40 23535180 Hammerbrookstr. 93 20097 Hamburg / Germany