Skip to main content
ilssac
ilssac
Inspiring
March 17, 2011
Answered

Calling an Oracle Stored Procedure.

  • March 17, 2011
  • 4 replies
  • 3222 views

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

This topic has been closed for replies.
Correct answer Adam Cameron.

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

4 replies

A
abhajha
Participant
February 5, 2020

@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

ilssac
ilssacAuthor
Inspiring
March 18, 2011

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?

A
Adam Cameron.
Inspiring
March 18, 2011

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
ilssacAuthor
Inspiring
March 18, 2011

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.

A
Adam Cameron.Correct answer
Inspiring
March 18, 2011

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

D
Dan_Bracuk
Inspiring
March 18, 2011

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

ilssac
ilssacAuthor
Inspiring
March 18, 2011

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".

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices