PL/SQL: Use 'IF' statement outside 'WITH ... AS' clause

Question

I am trying to write a procedure where I use Subquery Factoring 'WITH .. AS', but when I use 'IF .. THEN' before it, I get syntax error, I don't know how should I write it, any help?

  BEGIN
  OPEN my_SYS_REFCURSOR FOR
   IF .. IS NULL
   THEN
     WITH SomeName
          AS (SELECT.....);

Presumably, you get this error because `OPEN...FOR` expects a SQL statement, whereas `IF ... THEN` is a PL/SQL construct. What do you want to achieve? What does your `ELSE` part look like? — Frank Schmitt, Jan 23 '17 at 11:37
What I really need my procedure to look lije is that `BEGIN OPEN my_SYS_REFCURSOR FOR WITH SomeName AS ( IF .. IS NULL THEN SELECT.....);` But I got syntax error as well. — Safaa Mamdouh Salem, Jan 23 '17 at 11:51
As I mentioned before: that is not possible. You cannot use `IF` in a SQL statement. Please [edit] your question and add more information: what does your `ELSE` part look like? Or do you want to open the ref cursor only if the condition is met, and if it isn't met, you want to leave it uninitialized? — Frank Schmitt, Jan 23 '17 at 12:02
Safaa, your problem is purely syntaxic. Review SQL and PL/SQL cursor construction rules carefully and you should find the answer to your problem. IF THEN is a PL/SQL construct. You need to translate this into SQL. — Sebas, Jan 23 '17 at 12:05
If a condition is satisfied, you have to open a cursor reading from certain tables ; if the condition is not satisfied, what to do? Do not to open the cursor? Open it, but reading data from different tables? Open a cursor that gives no rows? — Aleksej, Jan 23 '17 at 12:10
@Aleksej If condition isn't satisfied, cursor will read data from different tables. — Safaa Mamdouh Salem, Jan 23 '17 at 12:58

Aleksej · Accepted Answer · 2017-01-23T13:34:29.723

You simply need to separate the IF statement from the OPEN:

declare
    my_sys_refcursor sys_refcursor;
begin
    if (1=1) then /* condition satisfied, cursor from some table */
        open my_sys_refcursor for
        with somename as ( select '1' as one from dual)
        select one
        from somename;
    else  /* condition not satisfied, select from different tables */
        open my_sys_refcursor for
        with someOthername as ( select 'one' as one from dual)
        select one
        from someOthername;
    end if;
end;

PL/SQL: Use 'IF' statement outside 'WITH ... AS' clause

1 Answers1