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

jreynold_at_nyx.nyx.net (James Reynolds) wrote in message news:<1014863716.571971_at_irys.nyx.net>...
> 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
>
> #2 - run a stored procedure in SQL*Plus and have output go to stdout
> (like running a select does)
>
> 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?
>
> 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.
>
> Can anyone offer any workarounds that aren't too ugly and perform well on
> high volumes of data?
>
> Thanks in advance.

I do not understand your complaint. Pl/sql provides a pretty simple and straight-forward way of doing a great many things. But it is primarily a tool for executing logic within the database and perhaps some of the things you are trying to use it for would be better accomplished in Pro*c, Forms, or Visual Basic.

And a select statement does not really write to stdout. It is writing to sqlplus, which is an application.

But putting the above aside here are some ideas that might be of use to you. If your rowtype is created on a select * then you can get the column names, types, and number of columns from all_tab_columns.

If you fetched your row into a pl/sql table using one column per table entry you should be able to write a generic routine to convert the table into a concatentated data item with the original entries separated by a delimiter.

Look at the dbms_util package for writing to the db server and look at dbms_pipe for writing to an application which in turn can write to the OS.

The co-operative FAQ has an article on writing comma delimited files from sqlplus: How do I export a database table to a flat file? To a comma or other character delimited file ? at url http://www.jlcomp.demon.co.uk/faq/flatfile.html

Another tool that may be useful to you are Reference Cursors. Have you had a chance to learn about them? I do not use them much, but I have seen some fairly interesting code samples.

And what about dynamic code. I am not found of dynamic code but if you want to build generic routines then dbms_sql with its ability to bind variables might be useful. For that matter the execute immediate statement has a using clause that allows binding variables at least in the where clause.

HTH -- Mark D Powell -- Received on Thu Feb 28 2002 - 09:44:41 CST