Re: Conditional execution of a select statement

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/09
Message-ID: <8f9rh8$tvt$1@nnrp1.deja.com>#1/1

In article <8f7hov$bsg$1_at_nnrp1.deja.com>,   russellwright_at_my-deja.com wrote:
> In Oracle, how can I write a select statement that is different based
 on
> the user?
>
> SELECT User INTO sLoggedOnUser FROM Dual;
>
> IF sLoggedOnUser <> 'Admin' THEN
> SELECT ....
> ELSE
> SELECT ....
> END IF;
>
> Seems like it should be simple, but I get errors stating it is
 expecting
> SELECT INTO.
>
> Do you have to use a cursor for this?
>
> Thanks
>
> Russ
>
> RussellWright_at_earthling.net
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

sounds like you are trying to just put a select like:

 IF sLoggedOnUser <> 'Admin' THEN
   SELECT * from T1;
 ELSE
   SELECT * from T2;
 END IF; In Oracle -- to return result sets from stored procedures, we use ref cursors. see http://osi.oracle.com/~tkyte/ResultSets/index.html for various examples. In your case, a simple procedure might be:

SQL>create or replace package my_pkg
  2 as

  3          type refcur is ref cursor;
  4          procedure my_proc( p_cursor in out refcur );

  5 end;
  6 /

Package created.

SQL>
SQL>create or replace package body my_pkg   2 as

  3          procedure my_proc( p_cursor in out refcur )
  4          is
  5          begin
  6                  if ( USER = 'OPS$TKYTE' )
  7                  then
  8                          open p_cursor for select 'This is the
query for OPS$TKYTE' from dual;
  9                  else
 10                          open p_cursor for select 'This is the
other query...' from dual;
 11                  end if;
 12          end;

 13 end;
 14 /

Package body created.

SQL>set autoprint on
SQL>variable x refcursor
SQL>exec my_pkg.my_proc( :x )

PL/SQL procedure successfully completed.

'THISISTHEQUERYFOROPS$TKYTE'

---

This is the query for OPS$TKYTE

SQL>grant execute on my_pkg to scott;

Grant succeeded.

SQL>connect scott/tiger
Connected.
SQL>exec ops$tkyte.my_pkg.my_proc( :x )

PL/SQL procedure successfully completed.

'THISISTHEOTHERQUERY...'

---

This is the other query...

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue May 09 2000 - 00:00:00 CDT