19 Replies Latest reply on Apr 17, 2007 6:52 AM by emmim44

    CF-StoredProc Fails

    emmim44 Level 1
      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.
        • 1. Re: CF-StoredProc Fails
          BKBK Adobe Community Professional & MVP
          > PROCEDURE="your_pkg.your_proc"
          Is that the proper name?

          • 2. Re: CF-StoredProc Fails
            emmim44 Level 1
            yes BKBK
            • 3. Re: CF-StoredProc Fails
              BKBK Adobe Community Professional & MVP
              What's the point in the name? (Pun not intended)

              • 4. Re: CF-StoredProc Fails
                emmim44 Level 1
                What are you saying man ? so Do you have any clue or not ?
                • 5. Re: CF-StoredProc Fails
                  paross1 Level 2
                  I know that this is a cross-post, but you might want to look at my replies at your other post.

                  Phil
                  • 6. Re: CF-StoredProc Fails
                    paross1 Level 2
                    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
                    • 7. Re: CF-StoredProc Fails
                      emmim44 Level 1
                      He is right. That is from his sample. He thinks he knows too much :P..but he couldnt figure out why is not working..
                      • 8. CF-StoredProc Fails
                        paross1 Level 2
                        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
                        • 9. CF-StoredProc Fails
                          BKBK Adobe Community Professional & MVP
                          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?
                          • 10. CF-StoredProc Fails
                            paross1 Level 2
                            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
                            • 11. Re: CF-StoredProc Fails
                              BKBK Adobe Community Professional & MVP
                              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.

                              • 12. Re: CF-StoredProc Fails
                                paross1 Level 2
                                You need to set returncode = "No" instead of yes in your cfstoredproc tag. It is an Oracle thing....

                                Phil
                                • 13. Re: CF-StoredProc Fails
                                  emmim44 Level 1
                                  I am going to do it...dudes....
                                  • 14. Re: CF-StoredProc Fails
                                    BKBK Adobe Community Professional & MVP
                                    I am going to do it......dudes....
                                    Nah, think Nike.

                                    • 15. Re: CF-StoredProc Fails
                                      emmim44 Level 1
                                      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#">


                                      • 16. CF-StoredProc Fails
                                        paross1 Level 2
                                        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
                                        • 17. Re: CF-StoredProc Fails
                                          emmim44 Level 1
                                          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
                                          • 18. Re: CF-StoredProc Fails
                                            paross1 Level 2
                                            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
                                            • 19. Re: CF-StoredProc Fails
                                              emmim44 Level 1
                                              it is ok..At least you tried dude....Good luck....You might be answering my other threads in future :D