Here is an example of the CAST.
May be you shall try this approach.
set serveroutput on size 1000000;
set echo on
DROP TYPE my_table_type
/
CREATE or REPLACE TYPE my_record_type as OBJECT
( MSG_SYS_NO NUMBER(12) )
/
CREATE or REPLACE TYPE my_table_type as TABLE of my_record_type
/
declare
buf_data my_table_type := my_table_type() ;
begin
buf_data.EXTEND ;
buf_data(1) := my_record_type(123456789012) ;
buf_data.EXTEND ;
buf_data(2) := my_record_type(123456789012) ;
FOR CX in
(
select MSG_SYS_NO from
TABLE ( cast( buf_data as my_table_Type ) )
)
loop
dbms_output.put_line('msg_sys_no = '||cx.msg_sys_no);
end loop;
end;
/
set echo off
HTH
GovindanK
On Sun, 14 Sep 2003 08:59 , Ryan <[EMAIL PROTECTED]> sent:
>it was on asktom. you create a type in the database and use a CAST. it was
>pretty easy. you just need to add an object.
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]>
>Sent: Sunday, September 14, 2003 12:44 PM
>
>
>> I am catching up on the mail from the list and I think you can use dynamic
>> sql.
>> Something like:
>>
>> mysql := 'insert into tab2 select col1 from tab1 where col2 in (';
>> open cursor
>> mysql := mysql || value || ',';
>> loop
>> substr(mysql,-1,1) := ');';
>> execute immediate '&mysql';
>>
>> Yechiel Adar
>> Mehish
>> ----- Original Message -----
>> To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]>
>> Sent: Friday, August 29, 2003 4:31 PM
>>
>>
>> > I need to do an insert select of the form
>> >
>> > insert into tab2
>> > select col1
>> > from tab1
>> > where col2 in (inlist of numbers);
>> >
>> > I do not know how many values will be in my inlist at runtime. With
>> strings I just build a big string. How do I build an 'inlist' of numbers
>at
>> runtime?
>> >
>> > Im using a cursor to determine which values need to be added to my
>inlist.
>> I think I can do some kind of cast, but im not familiar with it.
>> >
>> > Im on 8i. I do not want to j ust run this inside my cursor. It could
>then
>> execute 300-400 times and will run all day.
>> >
>> > --
---- Message sent via Zuvio Mail
Get your own FREE email account with SPAM and Antivirus protection!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Govindan K
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
