Calling Oracle overloaded stored procedures from CF 6.1

[dragoshbalan]

Did any of you tried to call an Oracle overloaded stored procedure from CF 6.1?
You will be amazed to know that it doesn't work. :))

Please test this :
Write two Oracle stored procedures having the same name but different
parameters :

CREATE OR REPLACE PACKAGE BODY Test
AS

-- one parameter --
PROCEDURE TestProc(y OUT ref_cursor)
IS
BEGIN
OPEN y FOR SELECT 1 FROM dual;
END TestProc;

-- 2 parameters
PROCEDURE TestProc(x VARCHAR2, y OUT ref_cursor)
IS
BEGIN
OPEN y FOR SELECT 2 FROM dual;
END TestProc;

END;

Don't forget to write also the specification of the package and then try to
call one of these procedures from coldfusion :

<CFSTOREDPROC procedure="TEST.TESTPROC" datasource="DS" username="USER"
password="PASS">
<cfprocparam type="In" cfsqltype="cf_sql_varchar" value="param" >
<cfprocresult name="rezultat">
</CFSTOREDPROC>

Anctiously waiting.....

[CF_Oracle]

You don't use parameter x in the second stored procedure. Hopefully you created
these procedures first with ref_cursor declared. Please send full right code
and I could test it in CF 7 with Oracle9i to compare if problem still exists.

[dragoshbalan]

Nice to see that someoune watches over this forum,

Yes you're right I didn't use the x parameter , but remember this is only a
test procedure , I don't think it matters I only want to show you a "strange"
situation ....
Here is the complete source :
Oracle code :
1.Package specification :

CREATE OR REPLACE PACKAGE Test
AS
TYPE ref_cursor IS REF CURSOR;

PROCEDURE TestProc(y OUT ref_cursor);
PROCEDURE TestProc(x VARCHAR2, y OUT ref_cursor);
END;

2. Here is the body:

CREATE OR REPLACE PACKAGE BODY Test
AS

-- one parameter --
PROCEDURE TestProc(y OUT ref_cursor)
IS
BEGIN
OPEN y FOR SELECT 1 FROM dual;
END TestProc;

-- 2 parameters
PROCEDURE TestProc(x VARCHAR2, y OUT ref_cursor)
IS
BEGIN
OPEN y FOR SELECT 2 FROM dual;
END TestProc;

END;

3. The coldfusion code is the same , just place the call to one of these
procedures in any page.

<CFSTOREDPROC procedure="TEST.TESTPROC" datasource="DS" username="USER"
password="PASS">
<cfprocparam type="In" cfsqltype="cf_sql_varchar" value="param" >
<cfprocresult name="rezultat">
</CFSTOREDPROC>

For any other question don't hesitate to request.
Thanks .

[CF_Oracle]

Did you look at refcursor problem there?
[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=0 0000313.htm[/url]

[dragoshbalan]

As far as I can understand the topic refers to "cursors passed as parameters".
This is not our case .

Thanks.

[CF_Oracle]

I re-created this package in Oracle 9i and modified code for calling storedproc
to
<CFSTOREDPROC procedure="TEST.TESTPROC" datasource="#REQUEST.DSN#">
<cfprocparam type="OUT" cfsqltype="cf_sql_refcursor" value="2">
<cfprocresult name="rezultat">
</CFSTOREDPROC>
Got error:
Error Executing Database Query.
[Macromedia][Oracle JDBC Driver]The specified SQL type is not supported by
this driver.

The error occurred in C:\Inetpub\wwwroot\... line 38

36 :
37 : <cfprocparam type="OUT" cfsqltype="cf_sql_refcursor" value="2">
38 : <cfprocresult name="rezultat">
39 : </CFSTOREDPROC>

By the way we run CF 7 and according to documentation cf_sql_refcursor is
valid type.

Good luck in your search!

[CF_Oracle]

One more thing if it could help you: when I omit this refcursor parameter or
put different type there was another error:
[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TESTPROC' ORA-06550:
line 1, column 7: PL/SQL: Statement ignored

[dragoshbalan]

So you received the same error as mine ,only that you work with ColdFusioon 7.
I think somebody from macromedia support must be noticed !

[dragoshbalan]

I just received a notification from Macromedia's Documentation Manager sayin'
"I checked and am sorry to say that this isn't supported. There is a bug
for this issue, 53122, but it hasn't been addressed. "

So the bug is confirmed. Good to know that somebody watches over this forum .
Waiting the fix.
: