Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Doing ANYTHING in PL/SQL is such a chore!

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

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 28 Feb 2002 19:51:04 +0300
Message-ID: <a5ln0o$563$1@babylon.agtel.net>


#1 If the requirement is to convert ABSOLUTELY ARBITRARY (that is, any) queries to a comma-delimited strings, then DBMS_SQL seems the only solution to set up a dynamic cursor for a query, fetch data from it and concatenate columns with commas appended. That's pretty trivial and only needs to be written once (and I'm sure a lot of people already reinvented this wheel). And if you are using %rowtype then there is NO need to find the column names/types at runtime because %rowtype is always applied to a particular table or strongly typed cursor and you are supposed to be already aware of its layout at design time...

#2 (with regard to your particular problem) Why not dbms_lob.writeappend() the data into CLOB and then dump it from SQLPLus into a file. SQLPLus has no problems with printing CLOBs... Just declare a function that returns a filled CLOB and do

set feedback off heading off
SELECT your_clob_function from dual

spool file.txt
/
spool off

And there are no shortcomings in %rowtype (at least not the one that you think of being such). %rowtype enforces strong typing on cursors without all the hassle of record type declarations (and redeclarations when table layout changes). Same's with %type. You'll find them of much help when you get used to them. And if that's not what you want, DBMS_SQL is always there to help. :)

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"James Reynolds" <jreynold_at_nyx.nyx.net> 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.
Received on Thu Feb 28 2002 - 10:51:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US