Troubleshooting blocks based on stored procedures

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?

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!

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

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?

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.

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.