Oracle-L: Re: how to do a variable in-list of numbers?

From: Govindan K <gkatteri_at_zuvio.com>
Date: Sun, 14 Sep 2003 13:49:24 -0800
Message-ID: <F001.005CFE15.20030914134924@fatcity.com>

('binary' encoding is not supported, stored as-is)
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 <rgaffuri_at_cox.net> 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" ORACLE-L_at_fatcity.com>
>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" ORACLE-L_at_fatcity.com>
>> 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: gkatteri_at_zuvio.com

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: ListGuru_at_fatcity.com (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). Received on Sun Sep 14 2003 - 16:49:24 CDT