CF-StoredProc Fails

[emmim44]

What is wrong with this Stored Proc ? It works fine in sqlplus but not with a CF call..I have created a simple Hello proc , and called from CF it works fine...Any help ? Thanks.

[BKBK]

> PROCEDURE="your_pkg.your_proc"
Is that the proper name?

[emmim44]

yes BKBK

[BKBK]

What's the point in the name? (Pun not intended)

[emmim44]

What are you saying man ? so Do you have any clue or not ?

[paross1]

I know that this is a cross-post, but you might want to look at my replies at your other post.

Phil

[paross1]

BKBK, this looks like a sample PL/SQL package and stored procedure that I posted a while back in response to an earlier question, so he probably just used the names that I had used in in the example.

Phil

[emmim44]

He is right. That is from his sample. He thinks he knows too much :P..but he couldnt figure out why is not working..

[paross1]

I don't know about knowing too much, but I do know that I've been using stored procedures returning result sets from Oracle with versions going back to Oracle 8.0 and CF 4.5, and now with Oralce 9i and CF 7, so I can "figure out" how to make them work when I'm dealing with someone that has a clue.

You never answered the questions regarding your use of Oracle thin client, dataDirect version, advanced settings, etc. etc. etc. The fact that it is working with SQL*Plus indicates to me that it is on the ColdFusion end, and your code looks fine, so I would have to assume drivers, etc.

Phil

[BKBK]

Emmim44:
> so Do you have any clue or not ?
I'm a beginner in PL/SQL but brushed up to be able to offer assistance. I gathered this much. One must use a cursor variable to return the result set of a PL/SQL stored procedure. Thus you defined a REF CURSOR ref_cur_type in a PL/SQL package called your_pkg. Your stored procedure, your_proc, executes a select-query. The procedure's input is the numeric variable in_id. Its output, the ref_cur_type variable out_rec, is what we expect the procedure to return to Coldfusion. It contains the result set comprising the rows from the table bpar whose un value is in_id. Did I miss something?

Emmim44:
> He [BKBK] thinks he knows too much :P..but he couldnt figure out why is not working..
You are right, Emmim44. I haven't figured out why it is not working. Neither have you. We're all in the search together, and the main person to benefit is you. Don't rage against me, rage against yourself or against the machine.

Paross1:
> BKBK, this looks like a sample PL/SQL package and stored procedure that I posted a
> while back in response to an earlier question, so he probably just used the names that
> I had used in in the example.
Thanks, Paross1, for the information. I don't use Oracle, hence my question comes from ignorance. Is the naming convention package_name.procedure_name transparent to Coldfusion?

[paross1]

The naming convention would be package_name.procedure_name, and the actual values used don't matter as long as what you use in your cfstoredproc matches the PL/SQL in Oracle.

Also, I believe that the " He thinks he knows too much :P..but he couldnt figure out why is not working.. " comment was directed at me, since I have helped him in the past with trying to get his PL.SQL working. In fact, I gave him the initial examples, and his code looks fine. The fact that it works when called from SQL*Plus tells me that his PL/SQL is OK, and in my opinion, it points to a driver problem on the ColdFusion end.

The one thing that he hasn't done, at least I haven't found it in any of his posts, is answer whether or not he is using the Oracle JDBC ThinClient drivers. If he is, then this will never work..... and I have posted this many times, and cited numerous sources from Adobe as to why this is so.

Phil

[BKBK]

Paross1:
> The naming convention would be package_name.procedure_name
OK

> Also, I believe that the "He thinks he knows too much :P..but he couldnt
> figure out why is not working.. " comment was directed at me
What I said stands. In fact, it will even be more relevant if you are the target. I have looked at the other thread. You have demonstrated not only knowledge, but also the patience of Job.

[paross1]

You need to set returncode = "No" instead of yes in your cfstoredproc tag. It is an Oracle thing....

Phil

[emmim44]

I am going to do it...dudes....

[BKBK]

I am going to do it......dudes....
Nah, think Nike.

[emmim44]

I used the return as No...I am getting this err:

Error Executing Database Query.
[Macromedia][Oracle JDBC Driver]Internal error: Net8 protocol error.

The error occurred in D:\CFusionMX\wwwroot\StoreProc.cfm: line 12

10 : <CFSTOREDPROC DATASOURCE="#FormVector#" PROCEDURE="your_pkg.your_proc" returncode="no">
11 : <CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_NUMERIC" VALUE="2" NULL="No">
12 : <CFPROCRESULT NAME="view">
13 : </CFSTOREDPROC>
14 : <cfdump var="#view#">

[paross1]

Net8? What version of Oracle are you using? Do any other stored procedures work from ColdFusion (ones that do not return result sets via ref cursors)?

Phil

[emmim44]

It works fine in sqlplus but not with a CF call..I have created a simple Hello proc , and called from CF it works fine...I am using Ora 10g

[paross1]

Interesting... I'm still on oracle 9i, so this might give me something to look forward to when we upgrade to 10g later on this year... just wonderful!

Sorry that I can't be of any more help with this.

Phil

[emmim44]

it is ok..At least you tried dude....Good luck....You might be answering my other threads in future 😄