Using PL/SQL block with an interactive grid (Oracle Apex)

1
votes

How can I use a plsql code block like this with an Interactive Grid (Using Oracle Apex) :

begin
     Query A;
exception when no_data_found then
     Query B;
end;

Actually sometimes 'Query A' returns nothing and I want to run 'Query B'. any solution?

3
oracleplsqloracle-apex
You are right. I forgot to explain that I'm using oracle Apex! – Morteza
After I posted my comment I noticed that you had tagged the question [apex] . I meant to delete my comment by I got distracted. ;-) Still, more clarity is only a good thing. – APC
@Professor, what advantage do you see in using an Interactive Grid, as opposed to an Interactive Report? (BTW, my choice in such situations is UNION, as suggested by @kara). – Littlefoot

3 Answers

1
votes

An interactive grid has to use a sql-query as source.

a. Write one query and use sql-query as source:

SELECT * FROM A
UNION ALL
SELECT * FROM B WHERE COUNT(SELECT * FROM A) = 0;

b. Write some function which does the work

Read this: How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?

But it sounds a little bit strange, that you got one grid for two datasources. This will bring up some problems when manipulating the data.

Open questions

  • Do you want to modify the data?
  • Do you want to insert new rows?
  • Does the user understand what's going on and what he is seeing?
0
votes

Since there is no apparent way to NOT use an SQL query as the Interactive Grid source, you could maybe (depending on your specific solution) think differently and create an Interactive Grid region for each query. Then you could show one or another when the page loads, using a region server-side condition or even a Dynamic Action.

0
votes

To expand on other answers with a little specificity, since this is about managing results of 2 different queries, you can put the 2 different queries in 2 different Grid regions. Then on the first region add a Server-side Condition of "Rows returned" and copy the SQL Query into the query input provided. On the 2nd region, you would set "No Rows returned" condition and again copy Query 1 into the SQL input provided.