Calling an Oracle Stored Procedure.

[ilssac]

Stored procedures have always given me trouble, I'm not sure why.

I want to call an Oracle Stored Procedure from a piece of CFML code.

My CFML code:

<cfstoredproc datasource="purload" procedure="PUR_XML_LOAD.load_raw" returncode="yes">
    <cfprocparam cfsqltype="CF_SQL_FLOAT" value="999" type="out">
</cfstoredproc>

The error I am getting:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 18:
PLS-00306: wrong number or types of arguments in call to 'LOAD_RAW'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Test PL/SQL code that works in Oracle SQL Developer:

DECLARE 
 foo NUMBER :=999;
begin
  PUR_XML_LOAD.load_raw (foo);
  dbms_output.put_line('>' || foo || '<');
end;

The top of the stored procedure:

create or replace
PACKAGE BODY PUR_XML_load AS


   -- *** PROCEDURE: PUR_XML_load ***
   -- Loads values from previously-validated XML file
   -- into the raw_pur table
   PROCEDURE Load_raw (res_val OUT NUMBER)
   AS

So how can I make the CFML code function the same way as the PL/SQL code?

TIA

Ian

[Dan_Bracuk]

Make sure you have the correct value for the type attribute in your cfprocparam tag.

[ilssac]

I've tried all three types, 'In, Out, InOut".  I get the same error for each one.

This does not seem to be that hard.  There is only one paramter and it is a 'Number' type, which the chart tells me should be equivalent to "CF_SQL_FLOAT".

[Adam Cameron.]

I hate to say it, Ian, but RTFM!

You've specified it as an OUT param, but you're not saying what variable to put the result into.  And you're passing a value IN to it.

Don't you want something like:

<cfprocparam type="out" variable="myVar" cfsqltype="as apporpriate">

I'm surprised CF ain't just syntax-erroring on that (this in itself is a bug, in my view), even before passing it to JDBC.

--

Adam

[ilssac]

Adam Cameron. wrote:

I hate to say it, Ian, but RTFM!

Adam

To be fair, I had the Manual open in the tab preceding this tab.

Adding the variable property did not change the behavior.  I am still getting the same error.  I tried as many permutations of which I could think.

[Adam Cameron. · Accepted solution]

OK, maybe you're RTFMing, but maybe RWAS (Read What Adam Said) 😉

Does THIS work:

<cfstoredproc datasource="purload" procedure="PUR_XML_LOAD.load_raw">
    <cfprocparam cfsqltype="CF_SQL_FLOAT" variable="myresult" type="out">
</cfstoredproc>

?

--

Adam

[ilssac]

Ok Adam, now WHY does that work?

How is that equivalent to the PL/SQL code I was trying to replicate?

DECLARE 
 foo NUMBER :=999;
begin
  PUR_XML_LOAD.load_raw (foo);
  dbms_output.put_line('>' || foo || '<');
end;

Looking at that code, it seems to be passing IN a value, is it not?

[Adam Cameron.]

You're not really passing anything in, you're providing a variable name to put a result in.

Think of it like the NAME attribute of a CFQUERY tag, eg:

<cfquery name="q">

You're not passing q in, you're telling it to put the result into q.

--

Adam

[ilssac]

All right:

This WORKS:

<cfquery datasource="purload" name="foobar">
   DECLARE 
     foo NUMBER :=999;
   begin
     PUR_XML_LOAD.load_raw (foo);
   end;        
</cfquery>

This DOES NOT WORK:

<cfstoredproc datasource="purload" procedure="PUR_XML_LOAD.load_raw" returncode="yes">
    <cfprocparam cfsqltype="CF_SQL_FLOAT" value="999" type="in|out|inout">
</cfstoredproc>

Yes, I mean each of the three values for type does not work, not that I tried to put all three values into the type parameter at the same time.

So what is the difference?

[abhajha]

@ilsaac- HI,

I think you need to remove the returncode="yes" attributes from the stored procedure call and that will start working. i was facing same kind of the issue and by removing this attribute the stored procedure start working the main cause is due to return type result set for that you can look but removing the attributes will fix  ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

When you construct the call escape sequence, specify the return status parameter by using the ? (question mark) character. This character acts as a placeholder for the parameter value that will be returned from the stored procedure. To specify a value for a return status parameter, you must specify the data type of the parameter by using the registerOutParameter method of the SQLServerCallableStatement class, before executing the stored procedure.

Thanks,

ABhash Jha

Coldfusion developer

Concentrix india PVt ltd Bangalore