accessing stored procedure in Oracle package

[leongek]

Hi,

I'm getting the following error in my CF page when trying to call a stored
procedure in an Oracle package:


[Macromedia][Oracle JDBC Driver][Oracle]ORA-06564: object
HSC.PKG_SR_TEST.SP_TEST_IT does not exist ORA-06512: at "SYS.DBMS_UTILITY",
line 114 ORA-06512: at line 1

The package is compile in Oracle and the proper permissions have been set as
can be seen in the SQL script to create the package.


--------------------------------------------------------------------
here is the CF code to call the procedure:

<cfstoredproc procedure="HSC.pkg_sr_test.sp_test_it" datasource="#dbsource#">
<cfprocparam type="in" cfsqltype="CF_SQL_INTEGER"
dbvarname="p_facultyid" value="30" null="No">
<!---not needed for ColdFusion MX--->
<!---<cfprocparam type="out" cfsqltype="CF_SQL_REFCURSOR"
variable="v">--->

<cfprocresult name="rstKeywords">
</cfstoredproc>


<CFDUMP VAR="#rstKeywords#">
--------------------------------------------------------------------
here is the SQL script to create the package body and grant permissions to the
required roles

CREATE OR REPLACE
PACKAGE BODY pkg_sr_test AS
/*
simple procedure to return keywords for faculty member
*/
PROCEDURE sp_test_it (p_facultyid IN NUMBER,
cur_sr_test OUT cur_sr_test_type)
IS
v_SQL LONG;

BEGIN

v_SQL := 'SELECT j.facultyid, k.keywordid, k.description, k.isactive ' ||
'FROM sr_keyword k, sr_jct_faculty_keyword j ' ||
'WHERE j.keywordid = k.keywordid ' ||
'AND j.facultyid = ' || p_facultyid || ' ';

OPEN cur_sr_test FOR
v_SQL;

END sp_test_it;
END pkg_sr_test;
/

-- Grants for Package Body
GRANT EXECUTE ON pkg_sr_test TO web_rp
/
GRANT DEBUG ON pkg_sr_test TO web_rp


Thanks in advance for any assistance in resolving the error.
~Ernest

[paross1]

It appears that it can not "find" your package under the HSC schema. Perhaps
you need to create a public synonym for package PKG_SR_TEST, or find out what
schema it actually belongs to and use that name. By the way, does it run OK
from SQL Plus?

Phil

[leongek]

Hi Phil,

I'm not sure how to execute a stored proc that has a REF CURSOR parameter in
SQL Plus. But for testing purposes, I've gone ahead and created a simple test
function in my package that accepts two numbers and returns the sum. This
function works in SQL Plus, but I'm getting the same error in my CFM page if I
use the <cfstoredproc>:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-06564: object
HSC.PKG_SR_TEST.SP_ADD_IT does not exist ORA-06512: at "SYS.DBMS_UTILITY", line
114 ORA-06512: at line 1

here is the CF code to call the function:
<cfstoredproc procedure="HSC.pkg_sr_test.sp_add_it" datasource="#dbsource#">
<cfprocparam type="in" cfsqltype="CF_SQL_INTEGER" dbvarname="p_1" value="30">
<cfprocparam type="in" cfsqltype="CF_SQL_INTEGER" dbvarname="p_2" value="30">
<cfprocresult name="rstKeywords">
</cfstoredproc>

and the following error if I call the function using a select...from dual in a
<cfquery> tag:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-00904: : invalid identifier

Here is my ColdFusion code to call this function using the <cfquery>:

--------------------------------------------------------------------------------
-------------------------------
<cfquery name="GetData" datasource="#dbsource#">
SELECT HSC.pkg_sr_test.sr_add_it(10,5) return_value FROM dual
</cfquery>

Thanks,
Ernest

[paross1]

To access a refcursor in SQL Plus, first you should create a variable of
refcursor type (var varname refcursor). Then, execute your procedure passing
your parameters, including the declared refcursor bind variable (using a :
character). Then the print command using the variable name of the refcursor
bind variable

In SQL Plus:

SQL> var out_var refcursor
SQL> exec pkg_sr_test.sp_add_it(30, 30, :out_var)
SQL> print out_var

Phil

[paross1]

Your problem is that package pkg_sr_test.sp_add_it does not exist in the data
dictionary under schema HSC. (Also, this is a procedure not a function, as they
are not the same in PL/SQL).

This
<cfquery name="GetData" datasource="#dbsource#">
SELECT HSC.pkg_sr_test.sr_add_it(10,5) return_value FROM dual
</cfquery>
will not work because you cannot return results sets from Oracle to ColdFusion
this way.

Once you figure out where pkg_sr_test.sp_add_it is, you should be able to run
it without any problem. Have you considered creating a public synonym for the
package name (and granting the EXECUTE privilege to the appropriate user or
users) so that you can execute it directly as pkg_sr_test.sp_add_it?

Phil

[paross1]

Also, isn't the query in your procedure declaration a little bit of overkill?
Why don't you just declare it directly instead of building the v_SQL variable?
You can use dynamic SQL, especially in Oracle 9i, but why do it the hard way if
you don't need to?

REATE OR REPLACE
PACKAGE BODY pkg_sr_test AS

PROCEDURE sp_test_it (p_facultyid IN NUMBER,
cur_sr_test OUT cur_sr_test_type)
IS

BEGIN

OPEN cur_sr_test FOR
SELECT j.facultyid, k.keywordid, k.description, k.isactive
FROM sr_keyword k, sr_jct_faculty_keyword j
WHERE j.keywordid = k.keywordid
AND j.facultyid = p_facultyid;

END sp_test_it;
END pkg_sr_test;
/

Phil