Re: PL/SQL UTL_FILE output

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 19 Mar 2005 14:29:30 -0800
Message-ID: <1111271174.789701@yasure>

Randy Harris wrote:

> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1111262663.460426_at_yasure...
> 

>>Randy Harris wrote:
>>
>>
>>>"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
>>>news:sn0o31hvilna0q2f947mbhimjacohbpaqc_at_4ax.com...
>>>
>>>
>>>>On Sat, 19 Mar 2005 09:54:15 GMT, "Randy Harris" <randy_at_SpamFree.com>
>>>>wrote:
>>>>
>>>>
>>>>
>>>>>I'm just starting out with PL/SQL and would appreciate some advice.

> 
> I've
> 


>>>>>written a simple procedure to write some data out to a file.  I would

>>>

>>>like

>>>

>>>

>>>>>the file to have fixed width fields, seperated by pipes.  The data gets

>>>

>>>out

>>>

>>>

>>>>>to the file OK, but not fixed width.  What can I do to correct that?

>>>

>>>Also,

>>>

>>>

>>>>>please point out my rookie mistakes.

>>>>

>>>>If the original columns are varchar2 you would need to use RPAD

>>>>everywhere to properly lineup your data. As you already have a

>>>>separator, I'm not sure what compelling reason there is (other than to

>>>>waste disk space), to do this.

>>>>

>>>>Your sql statement is of course horrible. It is not formatted at all,

>>>>and it uses hardcoded literals all over the place. Procedures and

>>>>cursors have parameters. In your case you would probably create 1000

>>>>almost identical procedures, except for the hardcoded literals, and

>>>>choke your CPU to death by doing so.

>>>>

>>>>

>>>>--

>>>>Sybrand Bakker, Senior Oracle DBA

>>>

>>>
>>>Sybrand, if those same literals will "ALWAYS" be used to select the data

> 
> how
> 

>>>can I make the SQL more efficient?
>>
>>I have yet to see the system that over the course of its lifetime
>>"ALWAYS" uses a single set of hard-coded parameters.
>>
>>If that is true in your system then one must presume that you only
>>have a single value in the column and that begs the question of why
>>put it into the database at all.
>>--
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace 'x' with 'u' to respond)

> 
> 
> I don't understand your statement.  There are thousands of values in the
> column, but only three that are excluded in this query.  Please explain what
> you mean.

Precisely why we are advising you to dump the literals. You are thinking that somehow what you do in your system is independent of what happens in other systems. A great recipe for disaster. No doubt the other people working in the same instance are all thinking the same thing ... me me me while Oracle is saying ALL OF YOU.

Dump the literals. And make sure everyone else does too.

For more information on why read Tom Kyte's book "Expert one-on-one Oracle."

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Sat Mar 19 2005 - 16:29:30 CST