SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict lveale's Avatar
    Join Date
    Jun 2001
    Location
    Dublin
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP and Oracle Stored Functions ??

    Hi,

    I can call stored procedures no problem, but when it comes to functions, I've hit a brick wall. Can anyone help?

    Am I binding correctly?
    Is it right to declare the PL/SQL variable first?

    All help appreciated greatly.


    Code is included below....

    PHP Code:
    PHP Code:
    // logon
    $conn OCILogon ($usr$pwd$sid); 

    $strSQL "DECLARE " .                "result NUMBER; " .
        
    "BEGIN " .
        
    ":result :=package_name_pkg.function1(" .
        
    "10000, '10-JUN-2001', '23-JUL-2001');" .
        
    " END;";
    // parse
    $stmt OCIParse($conn$strSQL); 

    // bind
    OCIBindByName($stmt":result", &$answer22); 

    // execute
    OCIExecute($stmt); 
     
    // result
    OCIResult($stmt,$answer); 

    echo 
    "Value returned:" $answer
    PL/SQL declaration of function (within package_name_pkg):
    FUNCTION function1
    (myval IN NUMBER
    ,P_STARTDATE IN DATE
    ,P_ENDDATE IN DATE)
    RETURN NUMBER;

    The error I get back on screen is:
    Warning: OCIStmtExecute: ORA-06550: line 1, column 69: PLS-00201: identifier 'CALC.package_name_pkg' must be declared ORA-06550: line 1, column 44: PL/SQL: Statement ignored in /home/isadlve/public_html/phptesting/calc_interest1.php on line 28

    Last edited by lveale; Jul 31, 2001 at 16:50.

  2. #2
    SitePoint Addict lveale's Avatar
    Join Date
    Jun 2001
    Location
    Dublin
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Solution

    The trouble I actually had with that error was that the user name I was logging on to the database with did not have execute permissions for the stored function (yes, I do feel silly and I've lost a lot of hair for no reason!).

    Just as a note, you don't neccessarily have to call OCIResult, after the OCIExecute, the variable (if a value has been returned) should be accessible.

    The declaration of the PL/SQL variable "result" is neccessary as is the BEGIN and END within the SQL string.


Bookmarks

Posting Permissions

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