RE: Why use Pro*C for spooling result sets to flat files when you can do the same thing in SQL*Plus?

"set linesize 1000 and each line is 100 [SIC] characters long even if the data 
is only 2 characters long"

Add set trimspool on the the script to handle this issue.

I think the speed advantage with larger tables may be one reason and the 
ability to handle LONG columns may have been another reason pro*c was chosen 
over just using sqlplus to extract the data.  Once a method for doing specific 
tasks is in place it takes on a life of its own.


________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Goulet, Richard
Sent: Thursday, September 02, 2010 12:01 PM
To: bwmyers@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Why use Pro*C for spooling result sets to flat files when you can 
do the same thing in SQL*Plus?

Bill,

    Being that I've written more Pro*C than I care to remember and still do use 
the language from time to time.  There are a number of reasons for why you 
would want to do this, though a good number may be OBE at this juncture.  The 
biggest that I ran into were the line size limit, right padding of the result 
set(set linesize 1000 and each line is 100 characters long even if the data is 
only 2 characters long), line terminator issues (we normally always look for 
the "normal" line terminator, but their different on Windows (DOS), Unix, AIX, 
etc...  Pretty hard to do in SQL*Plus, easy in C), breaking up a query into 
parts that make it run faster (pre current versions (11g) if you needed to join 
tables together is was sometimes easier to do it outside of a single SQL 
statement).

I'm sure I could find additional ones if I had the think time.


Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International



________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Bill Myers
Sent: Wednesday, September 01, 2010 6:24 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Why use Pro*C for spooling result sets to flat files when you can do 
the same thing in SQL*Plus?

In my previous post, 
http://www.freelists.org/post/oracle-l/Any-valid-security-concerns-using-Data-Pump-over-conventional-expimp,
 I mentioned that the DBAs at my new job use Pro*C to export data to flat 
files. So I asked one of them in a meeting: "why not use SQL*Plus instead?" 
Response after a long awkward stare: "you tell me". I have searched the forums 
and docs to no avail (besides maybe some speed advantage).

So, is there any real advantage to using Pro*C over SQL*Plus to spool result 
sets to flat files? I certainly don't want to become a Pro*C expert, so any 
ideas would be greatly appreciated.

Thanks in advance.
Bill
9i OCA/10g OCP DBA

Other related posts: