I used Toad to create a package with one simple stored function:
==============================================CREA TE OR REPLACE PACKAGE pkg_PUBLIC_SPs
TYPE typ_ref_cursor IS REF CURSOR;
CREATE OR REPLACE PACKAGE BODY pkg_PUBLIC_SPs
OPEN p_refcursor FOR
NVL(p.ProjectName, '') ProjectName,
NVL(p.ProjectDescription, '') ProjectDescription
FROM Project p,
WHERE b.ProjectID = p.ProjectID
AND b.BasisBaseline = 1
AND t.ProjectID = p.ProjectID
AND t.TaskIsControlAccount = 1
ORDER BY ProjectName ASC;
There are no parameters. The SQL works when tested outside of the stored function. However, when I try to execute the function using
I get an error "Invalid SQL statement" -- Toad/Oracle doesn't like the word "exec".
When I try
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".
"============================================= =" 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.
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.
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.