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 <[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.
> >> >
> >> > --
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
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).
