Calling an Oracle Stored Procedure.

Valorous Hero ,
Mar 17, 2011 Mar 17, 2011

Copy link to clipboard

Copied

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

TOPICS
Database access

Views

2.0K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

LEGEND , Mar 18, 2011 Mar 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

Likes

Translate

Translate
LEGEND ,
Mar 17, 2011 Mar 17, 2011

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 18, 2011 Mar 18, 2011

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 18, 2011 Mar 18, 2011

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 18, 2011 Mar 18, 2011

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 18, 2011 Mar 18, 2011

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 18, 2011 Mar 18, 2011

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 18, 2011 Mar 18, 2011

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 18, 2011 Mar 18, 2011

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 05, 2020 Feb 05, 2020

Copy link to clipboard

Copied

LATEST

@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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines