Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

counting records in a PL/SQL function.

499240Sep 14 2006 — edited Sep 16 2006
Hi,
I've coded a PL/SQL function that sits in an After Parameter Form trigger in an Oracle 10g Report. There are a number of parameters on the Report's Parameter Form, and this function returns the Where Clause porion of an SQL query based on which Parameters have data in them and such.

So, if if there are 10 parameters, and only 1, 2 and 4 have been filled in then the :P_WHERE_CLAUSE parameter will get the value 'AND param1 = 1 AND param2 = 2 and param3 = 3.

The client would like functionality where if there are no records returned by the query, then the Report does not run. How do I do this in PL/SQL?

In the function after the WHERE clause has been calcuated, I have tried
SELECT Count(r.batch_id) INTO btchID
WHERE r.opstamp = p.user_id AND batch_stage = 'TIN'
FROM roo_batches r, pes_users p
WHERE r.opstamp = p.user_id || :P_WHERE_CLAUSE

ORDER BY :P_ORDER_BY;

if btchID = '0' then
return (FALSE);
else
return (TRUE);
end if;

btchID is declared as a VARCHAR(3000);

This code doesn't work. btchID is always 0, even if 1 or more records that are actually being returned. Also, I get a message that says 'After Form Trigger failed'.
Can SQL statements not handle having variables in them? What other ways are there to figure out how many records are being returned in a Report?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 14 2006
Added on Sep 14 2006
4 comments
806 views