RE: Why use Pro*C for spooling result sets to flat files when you can do the same thing in SQL*Plus?
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Sep 2010 17:04:23 +0000
"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,
//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:
