Re: Passing variables from pl/sql block to sql block that in the same command file.

From: John Blackburn <John.D.Blackburn_at_transport.qld.gov.au>
Date: Wed, 8 Dec 1999 15:22:55 +1000
Message-ID: <82kq4q$1c52@hp013.qdot.qld.gov.au>

Your g_aff variable is a bind variable. What you are trying to do requires a substitution variable. So you need to do something like this:

...
variable g_aff varchar2(20)

<your plsql block>

col a new_value a

select :g_faa a
from dual;

select distinct .....

        .....
from ....
where.....
and ......

and &a
and .....
order by ....

This will do what you are trying to do.

<doriskg_at_my-deja.com> wrote in message news:82hs7c$dqj$1_at_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 Tue Dec 07 1999 - 23:22:55 CST