Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: how to do a variable in-list of numbers?

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

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Sun, 14 Sep 2003 14:39:24 -0800
Message-ID: <F001.005CFE18.20030914143924@fatcity.com>


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).
Received on Sun Sep 14 2003 - 17:39:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US