Re: Can you select from a PL/SQL table (Collections in Oracle 8)
Date: 2000/06/13
Message-ID: <8i5egp$v1q$1_at_nnrp1.deja.com>
In article <39453BA1.E9368A8D_at_pwgsc.gc.ca>,
Paul Arscott <Paul.Arscott_at_pwgsc.gc.ca> wrote:
>
>
> Paul Arscott wrote:
>
> > Does anyone know if you select from a PL/SQL table (called
Collections
> > in Oracle 8)?
> >
> > ie
> > DEFINE
> > TYPE testtype IS TABLE OF testtable%ROWTYPE
> > INDEX BY BINARY_INTEGER;
> > mytest testtype;
> >
> > BEGIN
> > ......populate mytest.
> >
> > select * from mytest;
> >
> > END;
> >
> > Paul
> > p.s. Im using oracle 7.3.4
>
> Please reply to paul.arscott_at_pwgsc.gc.ca
>
>
In a manner of speaking -- yes, sort of. In Oracle8.0 and up this is trivial. In 7.x you can but it takes a bit of work.
In Oracle8.0 it looks like this:
create or replace type myTableType as table of varchar2(64); /
create or replace
function demo_proc2( p_rows_to_make_up in number )
return myTableType
as
l_data myTableType := myTableType(); begin
for i in 1 .. p_rows_to_make_up
loop
l_data.extend;
l_data(i) := 'Made up row ' || i;
end loop;
return l_data;
end;
/
select *
from the ( select cast( demo_proc2(5) as mytableType )
from dual );
we create a SQL type -- and then we can SELECT from that sql (either a function that returns that type as above OR from a local variable of that type in plsql directly)
Here is an example I have for a "dbms_output" replacement that gets around some limits of dbms_output. It works in 7.3 and uses SQL to "query" a plsql table. You could sort it, where on it and so on.
....
use a plsql table type and a select -- write your own
dbms_output (that has no 1,000,000 byte limit and limits the
linesize to 2000 bytes/4000 bytes in Oracle7/8, not 255). I
tested this in 7.3.4 and it works well. it would look like
this:
tkyte_at_ORA734.WORLD> create or replace package my_dbms_output 2 as
3 procedure put( s in varchar2 ); 4 procedure put_line( s in varchar2 ); 5 procedure new_line; 6 6 function get_line( n in number ) return varchar2; 7 pragma restrict_references( get_line, wnds, rnds ); 8 8 function get_line_count return number; 9 pragma restrict_references( get_line_count, wnds, rnds,wnps );
10
10 pragma restrict_references( my_dbms_output, wnds, rnds, wnps, rnps );
11 end;
12 /
Package created.
thats our interface, much like the existing dbms_output (but no disable/enable -- this is always enabled). We implement the package body as such:
tkyte_at_ORA734.WORLD> create or replace package body
my_dbms_output
2 as
3
3 type Array is table of varchar2(4000) index by
binary_integer; 4 g_data array; 5 g_cnt number default 1; 6 6 procedure put( s in varchar2 ) 7 is 8 begin 9 if ( g_data.last is not null ) then 10 g_data(g_data.last) := g_data(g_data.last) || s; 11 else 12 g_data(1) := s; 13 end if; 14 end; 15 15 procedure put_line( s in varchar2 ) 16 is 17 begin 18 put( s ); 19 g_data(g_data.last+1) := null; 20 end; 21 21 procedure new_line 22 is 23 begin 24 put( null ); 25 g_data(g_data.last+1) := null; 26 end; 27 27 function get_line( n in number ) return varchar2 28 is 29 l_str varchar2(4000) default g_data(n); 30 begin 31 g_data.delete(n); 32 return l_str; 33 end; 34 34 function get_line_count return number 35 is 36 begin 37 return g_data.count+1; 38 end;
39
39 end;
40 /
Package body created.
The reason we use the pragmas on the get_line and get_line_count is so we can "select * from PLSQL_TABLE" to print the data (we don't want to use dbms_output to print for obvious reasons ;)
We can create a view on a table with lots of rows (all_objects is typically good with 1,000 or more rows in most cases) as such:
tkyte_at_ORA734.WORLD>
tkyte_at_ORA734.WORLD> create or replace view my_dbms_output_view
2 as
3 select rownum lineno, my_dbms_output.get_line( rownum )
text
4 from all_objects
5 where rownum < ( select my_dbms_output.get_line_count from
dual );
View created.
That view will
- find the number of lines to print from my_dbms_output.get_line_count
- invoke the get_line procedure with rownum for each line of output we generated
To test it and see how it works, we:
tkyte_at_ORA734.WORLD> begin
2 my_dbms_output.put_line( 'Hello World' ); 3 my_dbms_output.put_line( ' hi there' ); 4 my_dbms_output.put( 'This is one line on many ' ); 5 my_dbms_output.put( ' calls to put,' ); 6 my_dbms_output.put( ' just to see it working' ); 7 my_dbms_output.new_line; 8 my_dbms_output.put( 'end of data...' );9 end;
10 /
PL/SQL procedure successfully completed.
tkyte_at_ORA734.WORLD> select * from my_dbms_output_view;
LINENO TEXT
1 Hello World
2 hi there
3 This is one line on many calls to put, just to see
it working
4 end of data...
tkyte_at_ORA734.WORLD> select * from my_dbms_output_view; no rows selected
So, after we run the block, we have to select * from the view (or just select TEXT from the view to get the data we really want to dump -- you would "set heading off, set pagesize 10000, set feedback off, spool somefile, select text from view" to capture the output). The very next time we try to select * from the view -- its empty (it empties itself as it fetches)
Another test -- to show that after getting empty it works again:
tkyte_at_ORA734.WORLD> begin
2 for x in ( select rpad( ' ', rownum, ' ' ) || username
data from all_users where rownum < 11 )
3 loop 4 my_dbms_output.put_line( x.data ); 5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
tkyte_at_ORA734.WORLD>
tkyte_at_ORA734.WORLD> select * from my_dbms_output_view;
LINENO TEXT
---------- --------------------------------------------------
1 SYS
2 SYSTEM
3 DBSNMP
4 SCOTT
5 TKYTE
6 USERA
7 USERB
8
8 rows selected.
tkyte_at_ORA734.WORLD>
(this little trick has other uses -- you can now select * from plsql_table in other places knowing this. Have you ever wanted a variable length in list? eg: select * from T where x in ( "some string of variables, how many you don't know until run time" ) )
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Tue Jun 13 2000 - 00:00:00 CEST