| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: how to do a variable in-list of numbers?
('binary' encoding is not supported, stored as-is)
On Sun, 14 Sep 2003 23:03:47 , Govindan K <gkatteri_at_zuvio.com> sent:
We have applications where are session active. I mean, lot of data needs to be inserted during the session, select based on various criteria, lots of intermidiate steps involved. Finally select , send the output. Having intermediate tables/global temp tables does not really help in processing speed. This is better. I don't know what is other's experience.
GovindanK
>On Sun, 14 Sep 2003 15:14 , Ryan rgaffuri_at_cox.net> sent:
>
>>i used it because i needed i a cursor to do an 'insert values' so i grabbed
>>my data from the cursor i needed anyway. Plus I needed to use it multiple
>>times. Plus the tables are not indexed since they were designed to optimize
>>inserts.
>>
>>So basically if you are going to 'resuse' the inlist, your join isnt
>>optimized, and/or you need the cursor to grab the inlist for something else,
>>then its useful.
>>----- Original Message -----
>>To: "Multiple recipients of list ORACLE-L" ORACLE-L_at_fatcity.com>
>>Sent: Sunday, September 14, 2003 6:39 PM
>>
>>
>>> It's beginning to be late here and I'm feeling pretty tired, so I don't
>>> totally exclude my erring, but I believe that there is a basic
>>> conceptual flaw here. I see CAST as a way to turn into a relational
>>> object something which is not, by essence, a relational object (I would
>>> typically say the same of external tables). It's a great technique to
>>> map a PL/SQL table into something looking like a table - but only if the
>>> data you have to store into the PL/SQL table doesn't come form Oracle in
>>> the first place. Otherwise, could someone explain to me what is the
>>> benefit over a SELECT ... WHERE IN (SELECT ...) if a cursor is used to
>>> determine the in list ? The pleasure of loading more data into your PGA,
>>> perhaps ? Or is it 'why make simple when you can make complicated' in
>>> action ?
>>>
>>> SF
>>>
>>> Govindan K wrote:
>>> >
>>> > 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.
>>> > >> >
>>> > >> > --
>>> --
>>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>> --
>>> Author: Stephane Faroult
>>> INET: sfaroult_at_oriole.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).
>>
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>--
>>Author: Ryan
>> INET: rgaffuri_at_cox.net
>>
>>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).
>
>
>
>---- Message sent via Zuvio Mail
>Get your own FREE email account with SPAM and Antivirus protection!
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 Tue Sep 16 2003 - 15:49:56 CDT
 |
 |