Writing out meaningful output

[paljnad]

Hello,

We currently have some SQL code that we call from another BAT file but the log file produced from this process does not have any meaningful information.

For example, we have a command file that has this line:

C:
c:\Oracle\10.2.0\bin\SQLPLUS.EXE UID/PWD@DB @e:\a.sql >e:\a.log

a.sql looks like this:

set heading off
set feedback off
set pagesize 0
set define on
set term off
set echo off

-- define variables
set verify off

define tbl_bstrndtl = 'bstrndtl'
define tbl_bstrnhdr = 'bstrnhdr'



/* start attributes requirements */
set linesize 80
spool e:\a.txt

select
rpad(nvl(a.ACCOUNT_number,' '),10)
|| rpad(to_char(b.effective_date,'mmddyyyy') || ' ',8)
from &tbl_bstrndtl a, &tbl_bstrnhdr b
where a.tran_id = b.tran_id
spool off

exit

The a.log file produced from this process just tells me that a connection was made to Oracle and then it was disconnected.

Could I give a more meaningful output like what was the result of the select and whether it ran successfully or failed with an error?

 

[Turkbear]

Hi,
The a.txt file should have that info,shouldn't it?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Palnjad,

In your original post you say:

...this process just tells me that a connection was made to Oracle and then it was disconnected.

Could I give a more meaningful output like what was the result of the select and whether it ran successfully or failed with an error?

Here is an analysis if some of your code:

set feedback    off <- suppress the outcome of the SQL statement
set pagesize    0 <- suppress column headings
...
set term    off <- suppress output to the screen
set echo    off <- suppress code listing from the script file

As you can see, one or more of you SQL*Plus commands, above, are responsible for your not seeing what you want to see.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”