Re: Passing bind variable from pl/sql to sql block.

From: <markp7832_at_my-deja.com>
Date: Tue, 07 Dec 1999 17:57:51 GMT
Message-ID: <82jhqr$jq5$1@nnrp1.deja.com>

Dori, you access the pl/sql value in sqlplus like this: (Notice colon on bind variabe, definition below query result)

 1* select * from cost_item where rownum = :v_charfld OPS2> /

PL ITEM_NO                                            CO


-- -------------------------------------------------- --

DESCRIPTION

---

---

DATE_LAST INCR_MATL INCR_LABOR INCR_SERVICE INCR_FIX_OVHD INCR_VAR_OVHD
--------- ---------- ---------- ------------ ------------- -------------
ACCUM_MATERIAL ACCUM_LABOR ACCUM_SERVICE ACCUM_FIX_OVHD ACCUM_VAR_OVHD
-------------- ----------- ------------- -------------- --------------
 UNIT_COST STOC ACCT_LOT_SIZE R LAST_RECA COST_E COST LAST_ROLL INCR_HOUR
---------- ---- ------------- - --------- ------ ---- --------- --------
--

ACCUM_HOUR

---

15 P4-0001                                            A
REFURBISH THE M-9 MEZZANINE FOR EDS.
10-MAY-94          0          0            0             0             0
             0           0             0              0              0
         0 EA               1
Y                                          0
         0

WHERE the value was filled like this:
set echo off
rem
rem Sample SQL and PL/SQL passing variable to SQL*Plus from PL/SQL rem
variable v_charfld number
begin
select count(*)
into :v_charfld <== The return value is defined in sqlplus from sys.dual
;
end ;
/
print v_charfld

In article <82gro6$l5e$1_at_nnrp1.deja.com>,   doriskg_at_my-deja.com wrote:

> 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.
>

........
--

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 07 1999 - 11:57:51 CST