Passing bind variable from pl/sql to sql block.

From: <doriskg_at_my-deja.com>
Date: Mon, 06 Dec 1999 17:28:49 GMT
Message-ID: <82gro9$l5h$1@nnrp1.deja.com>

Hi there,

I have 2 questions:

1. I have a sqlplus command file that accepts 3 input parameters. And I run it with START command.

Inside of the command file I have sqlplus commands pl/sql block and sql select statement.

I need to do some checking on the input parameter, so I wrote plsql with if - else and assign a value to sqlplus global var. depend on the condition. Then I wonted to reference the global var. in the SQL where clause that is outside of the PL/SQL block, I am getting and error MSG.

Is there any body can help me with that I really appreciated this is how the command file looks like:

    spool outfile
    set verify off
    …
    …
    variable g_aff varchar(20)

    declare

        firstarg varchar2(20) := '&3';
    begin

       if firstarg = 'all' then
           :g_aff  := '1=1';
       else
            :g_aff := ' a.aff_name = "&3"  ';
      end if;

   end;
/

print g_aff

select distinct .....

        .....
from ....
where.....
and ......
and   g_aff  ====> here where I want that the
                   value that g_aff get from
                   plsql block will be assign.

and .....
order by ....
My question HOW I CAN ASSIGN THE VAR. FROM PLSQL TO SQL STATEMENT? 2. I want to write plsql block that include SQL SELECT statement with out assign the columns into variables CAN I DO THAT?
I checked almost every oracle book not a clue!?

Here how the plsql look like:

   declare
     ....
   begin

     if  x =1 then
       select age,name,address
       from  emp
       where  name = 'dan';
     else
        select .....
        from….....
        where…..;
     end if;

  end;
 /
I am getting error msg. That need select into….. If any body know the answers for those questions I will Appreciated if you can reply ASAP.

Thanks in advance,
Dori

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 06 1999 - 11:28:49 CST