Re: Doing ANYTHING in PL/SQL is such a chore!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 28 Feb 2002 12:10:53 -0800
Message-ID: <a5m2sd02qch@drn.newsguy.com>

In article <1014863716.571971_at_irys.nyx.net>, jreynold_at_nyx.nyx.net says...
>
>I've been programming in PL/SQL on-and-off for a few years, writing mainly
>small packages for data manipulation. Now I'm working on a larger project
>and I'm discovering how difficult it is to write good, modular code for
>use across the enterprise.
>
>Can either of these basic tasks be done without a kludge?
>
> #1 - a CSV procedure that takes an arbitrary row of data and returns a
>comma-delimited string
>

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:235814350980

> #2 - run a stored procedure in SQL*Plus and have output go to stdout
>(like running a select does)
>

SQL> set serveroutput on (just put that in your login.sql) SQL> begin

2 dbms_output.put_line( 'Hello World!' ); 3 end;
4 /
Hello World!

but reading on -- that is not really what you mean... see below for more.

>IMHO, the inability to determine the fields in a %ROWTYPE variable is a
>*huge* shortcoming of the language and contributes to large amounts of
>non-reusable code all of the place. Am I missing something obvious here?
>

give me a concrete, real world example where this would be useful. I use SQL and DBMS_SQL to do many "generic" things.

>As far as #2 goes... I understand why it's not possible-- a procedure runs
>on the database server with no access to client file descriptors. But I'd
>expect there to be some way to "unload" the DBMS_OUTPUT buffer as it fills
>up. I want to output 2GB of data to a file and redirect the output from
>SQL*Plus to a file. I can do some of what I want with a function, but it's
>not as general as it could be.

will, for that UTL_FILE is the way to go. In 9i you can go a step further -- pipelined functions can do this. You can select * from plsql function and as the function produces data -- the client gets it. If the client is sqlplus, you see data back from the routine as it is generated (depending on the array size).

For example:

tkyte_at_TKYTE9I.US.ORACLE.COM> create or replace type vcArrayType 2 as table of varchar(80)
3 /

Type created.

tkyte_at_TKYTE9I.US.ORACLE.COM>
tkyte_at_TKYTE9I.US.ORACLE.COM> create or replace   2 function pipeline_demo
  3 ( p_nrows_to_generate in number )
  4 return vcArrayType
  5 PIPELINED
  6 as
  7 l_msg varchar2(80);
  8 begin

  9      for i in 1 .. p_nrows_to_generate
 10      loop
11          l_msg := 'This message came at seconds ' ||
to_char(sysdate,'sssss');
 12          pipe row(l_msg);
 13          dbms_lock.sleep( 1 );
 14      end loop;
 15          return;

16 end;
17 /

Function created.

tkyte_at_TKYTE9I.US.ORACLE.COM>
tkyte_at_TKYTE9I.US.ORACLE.COM> set arraysize 1
tkyte_at_TKYTE9I.US.ORACLE.COM> select * from table( pipeline_demo( 5 ) );

COLUMN_VALUE

This message came at seconds 54474
This message came at seconds 54475
This message came at seconds 54476
This message came at seconds 54477
This message came at seconds 54478

I saw each row displayed second by second. I did not wait till the end.

>
>Can anyone offer any workarounds that aren't too ugly and perform well on
>high volumes of data?
>
>Thanks in advance.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Thu Feb 28 2002 - 14:10:53 CST