Re: intermediate table

From: Big Planet <bigplanet34_at_hotmail.com>
Date: Thu, 04 Oct 2001 14:31:26 -0700
Message-ID: <F001.003A2A2C.20011004144945@fatcity.com>

create or replace type

typ_plsqltables_pkg_obj as object (sql_line varchar2(4000));/create 
or replace type typ_plsqltables_pkg_tab is table of 
typ_plsqltables_pkg_obj;/ CREATE OR REPLACE PACKAGE plsqltables_pkg 

IS TYPE t_out_cur_typ IS REF CURSOR; PROCEDURE tabledata(           
o_cur OUT t_out_cur_typ); END plsqltables_pkg;/CREATE OR REPLACE PACKAGE BODY plsqltables_pkg ISPROCEDURE tabledata(           o_cur
OUT t_out_cur_typ) IStab_plsqltables_pkg typ_plsqltables_pkg_tab;BEGINtab_plsqltables_pkg :=typ_plsqltables_pkg_tab(typ_plsqltables_pkg_obj('ab'));tab_plsqltables_pkg.extend;tab_plsqltables_pkg(2)
:=

typ_plsqltables_pkg_obj('a');tab_plsqltables_pkg.extend;tab_plsqltables_pkg(3)
:=

typ_plsqltables_pkg_obj('b');tab_plsqltables_pkg.extend;tab_plsqltables_pkg(4)
:=

typ_plsqltables_pkg_obj('c');tab_plsqltables_pkg.extend;tab_plsqltables_pkg(5)
:= typ_plsqltables_pkg_obj('d');OPEN o_cur FOR  select
t.sql_line    from table(cast(tabledata.tab_plsqltables_pkg astyp_plsqltables_pkg_tab)) t;END tabledata;END plsqltables_pkg;/ DECLARE  ocur 
plsqltables_pkg.t_out_cur_typ;  vc_b varchar2(4000);  BEGIN  plsqltables_pkg.tabledata(  OCUR );  LOOP  FETCH ocur INTO vc_b;    EXIT when ocur%NOTFOUND;    DBMS_OUTPUT.Put_Line(' '||vc_b);    END LOOP;END;
----- Original Message -----
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">   <DIV
  style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From:   Amar Kumar
  Padhi
  To: <A title=ORACLE-L_at_fatcity.com
  href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L   

  Sent: Thursday, October 04, 2001 8:25   AM
  Subject: RE: intermediate table   

  Try using temporary
  tables. You can then carry out formating. Using selects is not possible   on pl/sql tables, if that is what you're using.   rgds <FONT face=Courier
  color=#000000 size=2>amar
      

    <FONT face="Times New Roman"
    size=2>-----Original Message-----From: Shishir     [mailto:shishir_at_agnisoft.com]Sent: Wednesday, October 03, 2001     8:26 PMTo: Multiple recipients of list     ORACLE-LSubject: intermediate table
    Hi to all !
    I have one small problem and i am sure many of     u can reply .My problem is :i am
    storing data from different table into   intermediate     table but the
    i want to use sql statements to filter     data from that intermediate table, I can access it as array of user defined     object . but i want to use sql statement for extracting the data so plz help     me out.
    the below code is just prototype,not actual one     whoch is only one table TAX .     

    declaretype my_rec_type is record (c1     tax.col_lbl%type,c2 tax.subj_carr%type);type     my_plsql_table_type is table of my_rec_type     index by binary_integer;v_table
    my_plsql_table_type;rec my_rec_type ;num     number;cursor r is select rownum, col_lbl,     subj_carr from tax;beginopen
    r;loopFETCH r INTO num ,rec.c1,rec.c2;     EXIT WHEN r%NOTFOUND;v_table(num).c1 :=     rec.c1;v_table(num).c2 := rec.c2;end     loop;/* I want
    to use sql statements for fetch record like select rowi from v_table     where condition =conditionname*/<FONT     color=#000000>end;
    If  there is any alternative for intermediate table let me know.     thanx in
    advance
    shishir kumar mishra<A
    href="mailto:shishir_at_agnisoft.com">shishir_at_agnisoft.com     <FONT
color=#000000>  Received on Thu Oct 04 2001 - 16:31:26 CDT