From bigplanet34@hotmail.com Thu, 04 Oct 2001 14:31:26 -0700 From: "Big Planet" Date: Thu, 04 Oct 2001 14:31:26 -0700 Subject: Re: intermediate table In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 -----
From: Amar Kumar Padhi To: 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 amar   -----Original Message-----From: Shishir [mailto:shishir@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*/end; If  there is any alternative for intermediate table let me know. thanx in advance shishir kumar mishrashishir@agnisoft.com