1

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?

Morteza
  • 55
  • 1
  • 10
  • You are right. I forgot to explain that I'm using oracle Apex! – Morteza Mar 05 '18 at 11:10
  • 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 Mar 05 '18 at 11:50
  • @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 Mar 05 '18 at 21:11

3 Answers3

2

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?
kara
  • 3,205
  • 4
  • 20
  • 34
  • These aren't necessarily two different datasources, but two different queries. What's different? We don't know; maybe just a WHERE clause ... – Littlefoot Mar 05 '18 at 10:00
  • Tnx but as you said it is a little bit strange. Is there any exception handling with interactive grid?? And answers : No I don't want to modify the data. No row will be inserted. Yes the user understands all and he will see all the process. – Morteza Mar 05 '18 at 10:00
  • Hello Littlefoot. the queries are very large. That's I really want is the exception handling with interactive grid when no_data_found happens. – Morteza Mar 05 '18 at 10:02
  • If you're using a `interactive grid` you have to deliver your data with a query. – kara Mar 05 '18 at 10:30
  • 1
    Maybe have two grids on the same page, set in the condition when displaying one or another. (Is it possible to have two grids on one page?) – romeuBraga Mar 08 '18 at 18:49
0

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.

Pedro Magalhães
  • 101
  • 1
  • 5
0

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.

Jeff M
  • 1