How do I do this in PL/SQL?

From: <prince_kumar_at_my-deja.com>
Date: Tue, 29 Aug 2000 02:33:48 GMT
Message-ID: <8of7eb$r3e$1@nnrp1.deja.com>

Hello:

I have the following DML stmt. Is it possible to write the same DML in PL/SQL without using Dynamic SQL?

I read somewhere in the oracle forum that it is not possible to have the "TABLE" clause within PL/SQL.

Following is my DML:

Insert into Redemption
  ( Cas_ID, Login_ID, Quantity, Prod_ID, Line_Desc, Shipping_address,   opt1name, opt1val, opt2name, opt2val, opt3name, opt3val, opt4name, opt4val )

  Select :myCasID, :myLoginID, :myQnty, :myProdID, 'myLineDesc', null,
  p.opt1attr_name, o1.attr_val, p.opt2attr_name, o2.attr_val,
  p.opt3attr_name, o3.attr_val, p.opt4attr_name, o4.attr_val

  from
  products p
,table(opt1attr_val) (+) o1
,table(opt2attr_val) (+) o2
,table(opt3attr_val) (+) o3
,table(opt4attr_val) (+) o4

  where cas_id = :myCasID
  and prod_id = :myProdID

  and o1.attr_id(+) = nvl(:myArrt1id, 0)
  and o2.attr_id(+) = nvl(:myArrt2id, 0)
  and o3.attr_id(+) = nvl(:myArrt3id, 0)
  and o4.attr_id(+) = nvl(:myArrt4id, 0) ;

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Aug 28 2000 - 21:33:48 CDT