13 Replies Latest reply on Jul 21, 2009 3:39 PM by 713080

    Troubleshooting blocks based on stored procedures

    cindyconlin
      Hello,
      I'm creating a form block that is based on a stored procedure. I've followed the steps in note 66887.1 "Basing a Block on a Stored Procedure - Sample Code", but am still having problems. Currently, the stored procedure runs successfully when I call it from SQL*Developer, but I receive a "FRM-40301: Query caused no records to be retrieved. Re-enter" when I run it from the form.
      I'm not sure how to troubleshoot this problem. I tried using the Forms Debugger, but that didn't seem to provide any visibility to what Forms was doing. Can anyone suggest a way to troubleshoot problems like this?
      Thanks,
      Cindy
        • 1. Re: Troubleshooting blocks based on stored procedures
          637104
          Hi there,

          I would try breaking the query e.g. adding an extra column to the block, set it as a database column and then run the form.

          This should stop the query from working altogether but when you then view the error you should get a listing of the query that was sent to the database.
          Obviously this will include the 'error' you have introduced but you can at this point copy the SQL and remove your 'error' to allow you to see what else is wrong with the query.

          Not sure I've made total sense there so let me know how you get on.

          Mark
          • 2. Re: Troubleshooting blocks based on stored procedures
            19588
            hi
            just guessing..
            make sure that all the items you extract with your query-procedure have database item property = yes.
            and: remeber that when you mody your package you have to execute a "compile all" in your form.

            hope it helps
            • 3. Re: Troubleshooting blocks based on stored procedures
              cindyconlin
              Good idea Mark..thank you. Since this block is based on a stored procedure instead of a table, the only way I could break the query was by adding a bogus column to the "Query Data Source Arguments". Once I did this, I received the error "FRM-40505: ORACLE error: unable to perform query". But, when I tried to view the error through Forms, I received "FRM-42100: No errors encountered recently".

              Any ideas on how to get past this?
              • 4. Re: Troubleshooting blocks based on stored procedures
                637104
                Try trapping the 40505 error and raising a form trigger failure. If processing is halted immediately after the 40505 error then hopefully you will be able to view the underlying error which should display the query that is failing.
                • 5. Re: Troubleshooting blocks based on stored procedures
                  cindyconlin
                  Unfortunately, trapping the 40505 error and raising a form_trigger_failure didn't help. It just caused me to stay in 'Enter-Query' mode...and so I couldn't get either the "no records retrieved" or the Oracle error.

                  Any other ideas? Thanks for your help!
                  • 6. Re: Troubleshooting blocks based on stored procedures
                    cindyconlin
                    I also tried to use :system.last_query to see what Forms sent to the database, but it wasn't populated (I think maybe it only gets populated after a successful query).
                    • 7. Re: Troubleshooting blocks based on stored procedures
                      Tony Garabedian
                      Cindy,

                      Did youuse a REF CURSOR or table of records??

                      Do you have all the columns listed in the Query Data Source Columns? And did you set the Query Data Source Arguments??

                      The blocks based on the ref cursor should have all the items which are
                      there in the ref cursor type. If the number of items does not match the
                      individual items in the ref cursor type when you execute query you'll get FRM-40350 Query caused no records to be retrieved.

                      Make sure you have listed all the columns, and make sure your REF Cursor returns rows in SQL*Plus.

                      Tony
                      • 8. Re: Troubleshooting blocks based on stored procedures
                        cindyconlin
                        Hi Tony
                        Did youuse a REF CURSOR or table of records??
                        I'm using a REF CURSOR.

                        Here's the signature of the procedure I'm specifying as the "Query Data Source Name":
                        PROCEDURE do_query(p_combined IN varchar2,
                        p_legal IN varchar2,
                        p_maiden IN varchar2,
                        p_preferred IN varchar2,
                        p_indrfn IN varchar2,
                        p_birthdate IN varchar2,
                        p_sex IN number,
                        p_unit IN number,
                        p_writingsys1 IN varchar2,
                        p_writingsys2 IN varchar2,
                        results IN OUT name_query_cur)

                        Here's the definition of the name_query_cur ref cursor type:
                        TYPE return_rec IS RECORD(
                        indrfn cms_name.naindrfn%TYPE,
                        legal varchar2(485),
                        maiden varchar2(485),
                        preferred varchar2(485),
                        birthdate varchar2(8),
                        unit cms_unit.ununit%TYPE,
                        sex cms_individual.insex%TYPE,
                        baptized cms_individual.inbaptized%TYPE,
                        priesthood cms_individual.inpriesthood%TYPE,
                        unitcountry cms_individual.inunitcountry%TYPE,
                        secured cms_individual.insecured%TYPE,
                        phsecured cms_individual.inphsecured%TYPE,
                        privilege cms_individual.inprivilege%TYPE,
                        confidential cms_individual.inconfidential%TYPE,
                        recordstatus cms_individual.inrecordstatus%TYPE
                        );

                        TYPE name_query_cur IS REF CURSOR RETURN return_rec;

                        Do you have all the columns listed in the Query Data Source Columns?
                        In "Query Data Source Columns", I have listed all the columns of the ref cursor. So, I have the following columns specified there:
                        indrfn
                        legal
                        maiden
                        preferred
                        birthdate
                        unit
                        sex
                        baptized
                        priesthood
                        unitcountry
                        secured
                        phsecured
                        privilege
                        confidential
                        recordstatus
                        And did you set the Query Data Source Arguments??
                        Yes, I set this to match the arguments to the do_query routine. I gave each argument the exact same name as it had in the do_query routine. So, this is currently set as:
                        p_combined IN varchar2     value=:control.combined
                        p_legal IN varchar2 value= :member_lov.legal
                        p_maiden IN varchar2 value= :member_lov.maiden
                        p_preferred IN varchar2 value= :member_lov.preferred
                        p_indrfn IN varchar2 value= :member_lov.indrfn
                        p_birthdate IN varchar2 value= :member_lov.birthdate
                        p_sex IN number value= :member_lov.sex
                        p_unit IN number value= :member_lov.unit
                        p_writingsys1 IN varchar2 value= 'Latn'
                        p_writingsys2 IN varchar2 value= left this blank
                        results IN OUT name_query_cur value=left this blank

                        The blocks based on the ref cursor should have all the items which are
                        there in the ref cursor type. If the number of items does not match the
                        individual items in the ref cursor type when you execute query you'll get FRM-40350 Query caused no records to be retrieved.

                        The items in the block exactly match the ref cursor. They are the exact same names, types, and in the exact same order as the ref cursor columns.
                        Make sure you have listed all the columns, and make sure your REF Cursor returns rows in SQL*Plus.
                        The ref cursor returns rows in SQL*Plus, just not in Forms.

                        Can you see anything I've missed or done wrong?
                        • 9. Re: Troubleshooting blocks based on stored procedures
                          cindyconlin
                          I forgot to mention that on each item in the block, I've specified "Database Item"=Yes and set the "Column Name" attribute to match the column name as in the ref cursor. Is that the correct thing to do? The note mentioned that the Column Name should be null on the foreign key field of a detail block, but I didn't think that applied here.
                          • 10. Re: Troubleshooting blocks based on stored procedures
                            cindyconlin
                            I figured out the problem. I had been specifying a literal value of 'Latn' for one of the arguments and Forms didn't like the single quotes. Once I removed them, then it worked.
                            The only way I could find this was by starting with an extremely simple procedure and form, and gradually adding elements. If Forms could have provided a little visibility into what commands it was actually sending to the server, that would have saved me 3 days.
                            Thank you all for your help.
                            • 11. Re: Troubleshooting blocks based on stored procedures
                              Tony Garabedian
                              Cindy,

                              If you were passing the word 'Latn' as a search argument your query will search for this value, so initally Forms was informing you by the error it raised "FRM-40301: Query caused no records to be retrieved. Re-enter"
                              The single quotes will be interpreted literally when using them in Queries.

                              Tony
                              • 12. Re: Troubleshooting blocks based on stored procedures
                                oliverdm
                                I'm having the same problem as above, however I'm not passing any literals in my arguments. I have 2 blocks for a master-detail relationship. I removed the column name from both tables since I created the relationship. (I still have the copy value in the detail block, but have tried it both ways). I've shorted the selection to 3 fields to try and see what I am missing. My only arguments are the refcursor and the key to the master table.

                                I'm getting the FRM-40505: ORACLE error; unable to perform query, however if I look in the display error it says that no errors have been encountered.

                                Do you have any other suggestions....
                                • 13. Re: Troubleshooting blocks based on stored procedures
                                  713080
                                  Hi,

                                  I am an eixsting form.
                                  I have created an addtional block based on the package.
                                  and add the fileds in my existing canvas.
                                  now in once canvas data should be displayed from 2 different datablock which have no relation in them.
                                  So when user pressed SEARCH button we have when button pressed triiger which goes to block old block and executes the query but my new block PROD_REPCOM_REPLEV is not fetching any data.
                                  but the procedure on which this block is bases returns data when i ran it manually.I tried commenting out the GO_BLOCK('PROD_REPCOM_REPLEV');
                                  execute_query; still it doesn't work


                                  CODE --

                                  GO_BLOCK('ACX_V_COMM_ET_OIN_MAPPINGS');
                                  execute_query;
                                  message('afterhere');
                                  GO_BLOCK('PROD_REPCOM_REPLEV');
                                  execute_query;

                                  ---

                                  Please help.
                                  Many Thanks,
                                  Manisha