Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: Calling PL/SQL stored procedure using PHP

    Hello all.

    I have a stored procedure (called nonstop) in PL/SQL which returns available flights from the database according to the user's input of origin, destination, and date. This is its execution in sqlplus:

    SQL> exec nonstop('BUF','JFK','6-Nov-03');

    423 BUF JFK 06-NOV-03 0400 0500 $200 7

    PL/SQL procedure successfully completed.

    I am using Oracle 8i and SQLPlus 8.1.5.0.0. I need to call this procedure through PHP, but I get an error. I am using the following code (this comes after the code for making the connection...which works fine)

    $stmt = OCIParse($conn, "begin :result := nonstop('BUF','JFK','6-Nov-03'); end;");

    OCIBindByName($stmt, "result", &$result);

    OCIExecute($stmt);

    echo $result;

    OCIFreeStatement($stmt);

    OCILogOff($conn);

    This is the result I get when I run the file....

    Trying a persistant connection to the database via OCIplogon...

    Connection successful!

    Warning: OCIStmtExecute: ORA-06550: line 1, column 18: PLS-00222: no function with name 'NONSTOP' exists in this scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored in /home/sdhill01/homepage/my.php on line 28



    -It says that no function by the name of nonstop exists. But in sqlplus I checked it, and it exists...This is the result of the describe function.

    SQL> describe nonstop
    PROCEDURE nonstop
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    CUSTORIGIN VARCHAR2(3) IN
    CUSTDEST VARCHAR2(3) IN
    CUSTDATE DATE IN


    -I do not know how to approach this problem. Any help will be greatly appreciated.

    Thanks a lot.

    S.D.

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Calling PL/SQL stored procedure using PHP

    Originally posted by skribble5
    Hello all.

    I have a stored procedure (called nonstop) in PL/SQL which returns available flights from the database according to the user's input of origin, destination, and date. This is its execution in sqlplus:

    SQL> exec nonstop('BUF','JFK','6-Nov-03');

    423 BUF JFK 06-NOV-03 0400 0500 $200 7

    PL/SQL procedure successfully completed.

    I am using Oracle 8i and SQLPlus 8.1.5.0.0. I need to call this procedure through PHP, but I get an error. I am using the following code (this comes after the code for making the connection...which works fine)

    $stmt = OCIParse($conn, "begin :result := nonstop('BUF','JFK','6-Nov-03'); end;");

    OCIBindByName($stmt, "result", &$result);

    OCIExecute($stmt);

    echo $result;

    OCIFreeStatement($stmt);

    OCILogOff($conn);

    This is the result I get when I run the file....

    Trying a persistant connection to the database via OCIplogon...

    Connection successful!

    Warning: OCIStmtExecute: ORA-06550: line 1, column 18: PLS-00222: no function with name 'NONSTOP' exists in this scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored in /home/sdhill01/homepage/my.php on line 28



    -It says that no function by the name of nonstop exists. But in sqlplus I checked it, and it exists...This is the result of the describe function.

    SQL> describe nonstop
    PROCEDURE nonstop
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    CUSTORIGIN VARCHAR2(3) IN
    CUSTDEST VARCHAR2(3) IN
    CUSTDATE DATE IN


    -I do not know how to approach this problem. Any help will be greatly appreciated.

    Thanks a lot.

    S.D.
    This is working example:

    CREATE OR REPLACE PROCEDURE inoutdemo
    ( par_in IN VARCHAR2,
    par_in_out IN OUT VARCHAR2,
    par_out OUT VARCHAR2) IS
    BEGIN
    par_out := par_in;
    par_in_out := par_in || ' ' || par_in_out;
    END;

    <?
    $db = OCILogon("scott","tiger");
    $stmt = OCIParse($db,"BEGIN
    inoutdemo(:in,:inout,ut);
    END;");
    OCIBindByName($stmt,":in",$in,32);
    OCIBindByName($stmt,":inout",$inout,32);
    OCIBindByName($stmt,"ut",$out,32);

    $in = "Hello ";
    $inout = "World!";

    OCIExecute($stmt);

    echo $in."\n";
    echo $inout."\n";
    echo $out."\n";
    ?>

  3. #3
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Calling PL/SQL stored procedure using PHP

    Originally posted by ika
    This is working example:

    CREATE OR REPLACE PROCEDURE inoutdemo
    ( par_in IN VARCHAR2,
    par_in_out IN OUT VARCHAR2,
    par_out OUT VARCHAR2) IS
    BEGIN
    par_out := par_in;
    par_in_out := par_in || ' ' || par_in_out;
    END;

    <?
    $db = OCILogon("scott","tiger");
    $stmt = OCIParse($db,"BEGIN
    inoutdemo(:in,:inout,ut);
    END;");
    OCIBindByName($stmt,":in",$in,32);
    OCIBindByName($stmt,":inout",$inout,32);
    OCIBindByName($stmt,"ut",$out,32);

    $in = "Hello ";
    $inout = "World!";

    OCIExecute($stmt);

    echo $in."\n";
    echo $inout."\n";
    echo $out."\n";
    ?>
    more you can find here: http://conf.php.net/pres/index.php?p...s%2Foci&id=oci

  4. #4
    Join Date
    Dec 2003
    Posts
    2

    Re: Calling PL/SQL stored procedure using PHP

    Hi,
    Thanks for the response. I tried the code you provided. The "hello world" does get printed. But I do get a 'OCIExecute($stmt);' error.

    Warning: OCIStmtExecute: ORA-01460: unimplemented or unreasonable conversion requested in /home/sdhill01/homepage/world.php on line 33

    I have OCIExecute($stmt) in my code, and receive the same error. I do not think this is a syntax error, do you know how to fix it.

    Thanks again.

    Originally posted by ika
    This is working example:

    CREATE OR REPLACE PROCEDURE inoutdemo
    ( par_in IN VARCHAR2,
    par_in_out IN OUT VARCHAR2,
    par_out OUT VARCHAR2) IS
    BEGIN
    par_out := par_in;
    par_in_out := par_in || ' ' || par_in_out;
    END;

    <?
    $db = OCILogon("scott","tiger");
    $stmt = OCIParse($db,"BEGIN
    inoutdemo(:in,:inout,ut);
    END;");
    OCIBindByName($stmt,":in",$in,32);
    OCIBindByName($stmt,":inout",$inout,32);
    OCIBindByName($stmt,"ut",$out,32);

    $in = "Hello ";
    $inout = "World!";

    OCIExecute($stmt);

    echo $in."\n";
    echo $inout."\n";
    echo $out."\n";
    ?>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •