Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    McLean, VA
    Posts
    9

    Unanswered: Calling Oracle stored functions

    I used Toad to create a package with one simple stored function:
    ==============================================CREA TE OR REPLACE PACKAGE pkg_PUBLIC_SPs
    IS
    TYPE typ_ref_cursor IS REF CURSOR;

    Function sp_Get_All_Projects
    RETURN typ_ref_cursor;

    END pkg_PUBLIC_SPs;
    /

    CREATE OR REPLACE PACKAGE BODY pkg_PUBLIC_SPs
    IS

    FUNCTION sp_Get_All_Projects
    RETURN typ_ref_cursor
    IS
    p_refcursor typ_ref_cursor;

    BEGIN
    OPEN p_refcursor FOR
    SELECT DISTINCT
    p.ProjectID ProjectID,
    NVL(p.ProjectName, '') ProjectName,
    NVL(p.ProjectDescription, '') ProjectDescription
    FROM Project p,
    Basis b,
    Task t
    WHERE b.ProjectID = p.ProjectID
    AND b.BasisBaseline = 1
    AND t.ProjectID = p.ProjectID
    AND t.TaskIsControlAccount = 1
    ORDER BY ProjectName ASC;
    RETURN p_refcursor;
    END sp_Get_All_Projects;

    END pkg_PUBLIC_SPs;
    /
    ==============================================
    There are no parameters. The SQL works when tested outside of the stored function. However, when I try to execute the function using
    exec sp_Get_All_Projects
    I get an error "Invalid SQL statement" -- Toad/Oracle doesn't like the word "exec".
    When I try
    call sp_Get_All_Projects
    I get error: "ORA-06576: not a valid function or procedure name".

    I am using dba priveleges to create it. It compiles successfully.

    I also use the same login to test-execute it, so it's not a matter of permissions.

    Using the Database Schema Browser / Procs tab in Toad, I can SEE the package listed under "Packages", and I can see the function listed under the package, but I do not see the function listed under "Functions".

    Any suggestions?

  2. #2
    Join Date
    Dec 2003
    Location
    McLean, VA
    Posts
    9
    Ignore the
    "============================================= =" before the "CREATE OR REPLACE..."
    That was not in my code I compiled. As I said, the compiles successfully and the pkg and its single function show up in the database with no "red X" beside them (in Toad), so compilation was successful.

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    62

    Talking

    If you have created the function inside a package you MUST use the
    PACKAGE NAME. Function name to call the function.
    The function is not stand alone. It is a part of the package
    Try calling it by refering to the package name.

    pkg_PUBLIC_SPs.sp_Get_All_Projects
    Regards,

    Rushi

  4. #4
    Join Date
    Dec 2003
    Location
    McLean, VA
    Posts
    9
    Thanks, Rushi. I tried that, but now that I have more confidence in that I'll try it again.

    I have another question: Is there a difference in the command to call a stored procedure vs a stored function? -- e.g. using "exec" or "call"?

    I'll post back my results. Thanks again.

  5. #5
    Join Date
    Dec 2003
    Location
    McLean, VA
    Posts
    9
    Mystery -- still can't execute.
    Tried pkg_PUBLIC_SPs.sp_Get_All_Projects.
    Maybe it's the syntax calling it to show the recordset.
    "exec" won't work in a SQL window.

    Someone suggested compiling the function outside of a package; even though a book on PL/SQL shows an example like my code.

  6. #6
    Join Date
    Dec 2003
    Location
    McLean, VA
    Posts
    9
    OK. Solved the problem. I was compiling these in Free Toad, and I found by experimenting that
    1. I had to click "Commit" under Toad's "Database" menu item.
    2. I had to reference the package name as well as the SP name in my code that called it from VB.
    3. I can execute the stored procedure from an ADODB.Command object, but still cannot figure out the syntax to execute or call it within Toad. Go figger.

Posting Permissions

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