Running SQLPlus from command line
RLN
New to SQLPlus so bear with me.
I am trying to get SQLPlus to run from a command line in a batchfile
which executes a .SQL file.
(The files here are TEST12.BAT which runs TEST12.SQL)
Right now, TEST12.SQL runs fine in Toad via the GUI and returns 33 rows
of data. So I know syntactically the SQL script works.
I tried running Toad from the command line but many threads (and days
of brick walls) I was advised to try using SQLPlus from the command
line via a batchfile, so here I am (with questions).
My 5 line batch file runs, (listed below) but there are four problems I
can't seem to resolve.
1. echo statement (line 5) when completed: "Echo done running
TEST12.BAT"
2. After Line 4 executes SQLPlus the batch file hangs. I do not see
data, don't know if the query is finished, etc.
3. Is there a statement that I can use that will return whether SQLPlus
successfully connected to the database? I think it connects, but am
unsure actually.
4. I need to save the data output. When the query is completed, how do
I tell SQLPlus (via another line in the batch file) to save the
contents to either a .csv file or .xls file, and exit, thus passing
control back to the final line of the batch file to display the
'process completed' message?
I need to save this output file (be it .csv, .txt, or .xls) to the
local drive so an Access program can pick it up and do more processing
with it.
When using the SQLPlus Command Line in a batch file in silent mode, is
there a limitation of filetypes the data retreival to be exported to?
I think choosing between .txt, .csv, and .xls woud be fine.
(line numbering is for reference here only)
Here is my Batch File:
<begin code for TEST12.BAT>
1. @echo off
2. echo Running TEST12.SQL
3. echo SQLPlus running from a command line.....please wait....
4. SQLPLUS -s myLogOn/myPass@MyDBName@TEST12.SQL
5. Echo done running TEST12.BAT
<end code for TEST12.BAT>
TEST12.SQL: (runs ok in Toad)
<begin code for TEST12.SQL>
SELECT DISTINCT A.PROCESS_INSTANCE,
A.BUSINESS_UNIT,
A.VOUCHER_ID,
A.VOUCHER_LINE_NUM,
A.BUSINESS_UNIT_PO,
A.PO_ID, A.LINE_NBR,
A.SCHED_NBR,
A.BUSINESS_UNIT_RECV,
A.RECEIVER_ID,
A.RECV_LN_NBR,
A.RECV_SHIP_SEQ_NBR,
A.MATCH_RULE_ID,
A.SETID,
A.MATCH_CNTRL_ID,
A.BUYER_ID,
B.MERCHANDISE_AMT,
B.QTY_VCHR,
C.IMAGE_REF_ID
FROM PS_VCHR_MTCH_EXCPT A,
PS_VOUCHER_LINE B,
PS_VOUCHER C
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.VOUCHER_ID = B.VOUCHER_ID
AND A.VOUCHER_LINE_NUM = B.VOUCHER_LINE_NUM
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.VOUCHER_ID = C.VOUCHER_ID
And A.PO_ID in('0000877894')
<end code for TEST12.SQL>>
Thanks for your assistance.
Brian Peasland
> data, don't know if the query is finished, etc.
In your SQL script, code the following line:
EXIT
> 3. Is there a statement that I can use that will return whether SQLPlus
> successfully connected to the database? I think it connects, but am
> unsure actually.
See the next answer.
> 4. I need to save the data output. When the query is completed, how do
> I tell SQLPlus (via another line in the batch file) to save the
> contents to either a .csv file or .xls file, and exit, thus passing
> control back to the final line of the batch file to display the
> 'process completed' message?
The SPOOL command can save the results to a file. So the first line in
your SQL script should be something like:
SPOOL my_sql_script.txt
After you have run your SQL statements in your script, add the following
line:
SPOOL OFF
> I need to save this output file (be it .csv, .txt, or .xls) to the
> local drive so an Access program can pick it up and do more processing
> with it.
>
> When using the SQLPlus Command Line in a batch file in silent mode, is
> there a limitation of filetypes the data retreival to be exported to?
> I think choosing between .txt, .csv, and .xls woud be fine.
The SPOOL command will output straight text. However, you can also
choose to have SQL*Plus generate HTML output for you with the SET MARKUP
command. If you want XML output, then you'll need one of the Oracle XML
toolkits.
> (line numbering is for reference here only)
> Here is my Batch File:
> <begin code for TEST12.BAT>
> 1. @echo off
> 2. echo Running TEST12.SQL
> 3. echo SQLPlus running from a command line.....please wait....
> 4. SQLPLUS -s myLogOn/myPass@MyDBName@TEST12.SQL
There is no space after MyDBName. The format should be:
sqlplus -s user/pass@tns_alias @script_name
HTH,
Brian
--
===================================================================
Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
Thomas Kellerer
> My 5 line batch file runs, (listed below) but there are four problems I
> can't seem to resolve.
> 1. echo statement (line 5) when completed: "Echo done running
> TEST12.BAT"
What exactly is the question here?
> 2. After Line 4 executes SQLPlus the batch file hangs. I do not see
> data, don't know if the query is finished, etc.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1009789
> 3. Is there a statement that I can use that will return whether SQLPlus
> successfully connected to the database? I think it connects, but am
> unsure actually.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch6.htm#1006648
> 4. I need to save the data output. When the query is completed, how do
> I tell SQLPlus (via another line in the batch file) to save the
> contents to either a .csv file or .xls file, and exit, thus passing
> control back to the final line of the batch file to display the
> 'process completed' message?
> I need to save this output file (be it .csv, .txt, or .xls) to the
> local drive so an Access program can pick it up and do more processing
> with it.
.xls is not possible. For the rest see the spool command
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1013669
> When using the SQLPlus Command Line in a batch file in silent mode, is
> there a limitation of filetypes the data retreival to be exported to?
> I think choosing between .txt, .csv, and .xls woud be fine.
SQL*Plus does not "export" data. It prints out results which you can
redirect to files. Again see the SPOOL command and
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch7.htm#1005594
Thomas
RLN
Here are the 8 lines (simplified).
Right now, line 3 runs fine and connects.
Lines 4 thru 8 do not run at all unless I do these manually. I need
these to run as a batch process from start to finish. Lines 4 thru 8
are outside of the SQL Plus environment in this batch process. How do
I 'include' lines 4 thru 8 so that they run in succession automatically
with no manual intervention?
Startup and display SQL file that is running...
1. @echo off
2. echo Running TEST12.SQL
connect to Oracle...
3. SQLPLUS -s myUserID/myPass@myDBName
Get the SQL file into the buffer...
4. GET TEST12.SQL
Run the SQL file....
5 . RUN
Spool the output to .txt file...
6. SPOOL TEST12.txt
Turn off spooling...
7. SPOOL OFF
Exit SQL Plus....
8. EXIT
What am I missing here?
Brian Peasland
Things look to be a mess....
For starters, you start spooling *after* you run the SQL script. How do
you expect the output to show up? You need to turn on spooling, run your
SQL statements, and then turn spooling off.
Next, in Windows batch scripts, you won't be able to get interaction
with SQL*Plus as you have done, which is why you have to do these
manually. Instead, code your SQL script as follows:
-- Start of TEST12.SQL script
SPOOL c:\temp\test12.txt
<< Insert SQL statements here >>
SPOOL OFF
EXIT
-- End of TEST12.SQL script
Once all of the above is in your SQL script, envoke SQL*Plus as follows:
SQLPLUS -s myUserID/myPass@myDBName @TEST12.sql
RLN
ok....trudging along here....
After the batch file runs, Test12.txt contains no data and is zero
bytes in size.
What else am I missing in this process?
Here are my updated files:
- - - - - - -
<begin TEST12.BAT>
rem Connect to SQL...
SQLPLUS -s myLogon/myPass@myDBName @TEST12.SQL
Echo done running TEST12.BAT
<end TEST12.BAT>
- - - - - - -
<begin TEST12.SQL>
--Spool output to text file
SPOOL test12.txt
--turn off spooling...
SPOOL OFF
-- Exit SQLPlus...
EXIT
<end TEST12.SQL>
Thanks.
Brian Peasland
Things look good to me. Your batch file is constucted correctly and so
is your SQL script. The only suggestion I would have is to explicitly
denote the directory in the SPOOL command similar to the following:
SPOOL C:\my_directory\test12.txt
That way, you know exactly where the output will be placed.
I'm not sure why your file is empty. Are you sure that the query returns
a result set?
Cheers,
RLN
manual process via Toad. I suppose doing a "List" command just prior to
the 'exit in the sql file would confirm that for sure, or would there
be a more efficient way...maybe pipe the recordcount to an ascii text
file like, "# of rows returned for QueryX was: <recordcount>"
(or however SQL Plus would reference the recordcount of the result set
returned.)
>>SPOOL C:\my_directory\test12.txt <<
yes, full qualification would be wise here.
Thanks Brian. Your help here is very much appreciated.
Ed Prochak
Syntax error: no terminating semicolon ;
RLN
Thanks Ed, (after turning multi-shades of red in embarrassment) :-)
As for formatting: the SPOOL command states:
"Represents the name of the file to which you wish to spool. SPOOL
followed by file_name begins spooling displayed output to the named
file. If you do not specify an extension, SPOOL uses a default
extension (LST or LIS on most systems)"
Below is 1 record of that output.
Are there simple ways to pre-format the text before spooling aside from
what is delivered here or am I stuck with "whatcha see is whatcha get"?
<begin text output>
PROCESS_INSTANCE BUSIN VOUCHER_ VOUCHER_LINE_NUM BUSIN PO_ID
LINE_NBR
---------------- ----- -------- ---------------- ----- ----------
----------
SCHED_NBR BUSIN RECEIVER_I RECV_LN_NBR RECV_SHIP_SEQ_NBR MATCH_RULE
SETID
---------- ----- ---------- ----------- ----------------- ----------
-----
MATCH_CNTR BUYER_ID MERCHANDISE_AMT QTY_VCHR
---------- ------------------------------ --------------- ----------
IMAGE_REF_ID
------------
1617711 10500 01436603 1 10500 0000877894
1
1 0 1 RULE_E100
SHARE
STANDARD ch282836 7150 2600
9446694
<end text output>
Brian Peasland
The SPOOL command takes the output of SQL*Plus and writes it to a file.
As such, if you do not like the formatting, change in SQL*Plus.
To change the length of the line of output, use the following:
SET LIN xxx
Where xxx is the number of characters. The default is too short.
To change the formatting of a column (including the length of a column),
use the COLUMN command in SQL*Plus.
Please refer to the SQL*Plus documentation for these two commands to see
how they can help you format your report:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm#i1081008
You can add the SET and COLUMN commands in your script before you start
the SPOOL.
RLN
Sorry for the delayed response. Lots of projects going on at once
here.
The 'Set LineSize' and Spooling works fine. Thank you for that
assistance.
Using SQLPlus from the command line is there a specific command I can
include in my .SQL file that would place a comma in between each column
value? I searched several SQLPlus resources and came up nill. If
there is no way to do that, I thought of another possibiltiy and
wondered if this could work:
What about writing "fillers" to the text output that is spooled? Can
this be done? For example: a "PartNumber" column is 8 digits long.
If a particular row has nulls or spaces in the PartNumber column, is
there a way to write eight zeros to simply indicate for that row, no
part number existed?
Thanks.
Thomas Kellerer
> Using SQLPlus from the command line is there a specific command I can
> include in my .SQL file that would place a comma in between each column
> value?
SET COLSEP
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1012319
> What about writing "fillers" to the text output that is spooled? Can
> this be done? For example: a "PartNumber" column is 8 digits long.
> If a particular row has nulls or spaces in the PartNumber column, is
> there a way to write eight zeros to simply indicate for that row, no
> part number existed?
What about
SELECT nvl(trim(partnumber), '00000000') FROM theTable;
Thomas
--
It's not a RootKit - it's a Sony
RLN
I'm looking through the "download-west" link you have here, looking for
a way to reference the length and data type of the columns I need to
show from SQL.
For example, if 12 columns were extracted from 4 tables via SQL, it
would help my export process (and documentation of the system I am
writing) to know the initial lengths of the columns I extract and their
datatypes.
Once again, thanks for your assistance. You have made my Oracle
learning curve a little smoother.