creating a log file in oracle using plsql (to out put the success/failure status of a query)

First of all, that isn't pl/sql, it's SQL and SQLPLUS.  Next, I think you'll have a little more than just results in your file, so it won't REALLY be a csv.  If your goal is to keep this in sqlplus script (rather than plsql, as you stated), then you can use the SQLCODE variable to tell if there was an error.  Once that has been done, you can use that file in generating your log.  Just append this to your current SQLPLUS script:

set head off
set show off
set verify off
set pages 0
set prompt off
set echo off
spool status.sql
show sqlcode
spool off
select sysdate DateRun,decode(

get status
c/sql/'sql/
a '
i
'sqlcode 0','plsql complete','plsql incomplete - ' ||

get status
c/sql/'sql/
a '
i
) status from dual

spool /gtxappl/Stock_Mirroring/Log/pyfs_unallocablestock_supply.csv
/
spool off


Good luck!

Hi,
dont really see where to fit this in in the code bellow, sorry.

set linesize 4000 pagesize 0 verify off
set heading off 
set feedback off 
set trimout on trimspool on termout off
set echo off
set prompt off
 
column curr_date new_value file_date noprint
select to_char(sysdate,'DDMMYY') curr_date
from sys.dual;
 
spool /gtxappl/Stock_Mirroring/Results/pyfs_allocablestock_supply&file_date..csv
select upper('item_id,shipnode_key,onhand')  
from dual;
select a.item_id || ',' || b.shipnode_key || ',' || b.quantity  
from yfs_inventory_item a, yfs_inventory_supply b 
where b.supply_type = 'ONHAND'   
and b.inventory_item_key = a.inventory_item_key;
spool off

Select allOpen in new window

As I said above, it goes at the end.  Just append it after what you have.  Now that you've added the other SET commands, you don't need to repeat them in what I gave you.

Good luck!

set linesize 4000 pagesize 0 verify off
set heading off 
set feedback off 
set trimout on trimspool on termout off
set echo off
set prompt off
 
column curr_date new_value file_date noprint
select to_char(sysdate,'DDMMYY') curr_date
from sys.dual;
 
spool /gtxappl/Stock_Mirroring/Results/pyfs_allocablestock_supply&file_date..csv
select upper('item_id,shipnode_key,onhand')  
from dual;
select a.item_id || ',' || b.shipnode_key || ',' || b.quantity  
from yfs_inventory_item a, yfs_inventory_supply b 
where b.supply_type = 'ONHAND'   
and b.inventory_item_key = a.inventory_item_key;
spool off
spool status.sql
show sqlcode
spool off
select sysdate DateRun,decode(
 
get status
c/sql/'sql/
a '
i
'sqlcode 0','plsql complete','plsql incomplete - ' ||
 
get status
c/sql/'sql/
a '
i
) status from dual
 
spool /gtxappl/Stock_Mirroring/Log/pyfs_unallocablestock_supply&file_date..csv
/
spool off

Select allOpen in new window

Hi, thanks for that.
We have had a re think and are now looking for a shell script to exit with success or failure?
This meaning the shell will execute the plsql and return a value of 0 or > to TSW to indicate success or failure.
Am stll waiting to find out more about TWS, but is there some way that you can tell me without you knowing what it is?
eg.If there are no oracle errors
exit 0
Else
exit 1
Sorry i knmow this is off a bit of a tangent.

That's ok.  It's a little different.  You can actually get it to exit with the sqlcode.

WHENEVER SQLERROR EXIT SQL.SQLCODE

It would change your script like below:

set linesize 4000 pagesize 0 verify off
set heading off 
set feedback off 
set trimout on trimspool on termout off
set echo off
set prompt off
 
column curr_date new_value file_date noprint
select to_char(sysdate,'DDMMYY') curr_date
from sys.dual;
WHENEVER SQLERROR EXIT SQL.SQLCODE 
spool /gtxappl/Stock_Mirroring/Results/pyfs_allocablestock_supply&file_date..csv
select upper('item_id,shipnode_key,onhand')  
from dual;
select a.item_id || ',' || b.shipnode_key || ',' || b.quantity  
from yfs_inventory_item a, yfs_inventory_supply b 
where b.supply_type = 'ONHAND'   
and b.inventory_item_key = a.inventory_item_key;
spool off
EXIT 0

Select allOpen in new window

Hi,
thats usful for another query im building! but unfortunatly my betters have decided that they would like a piece of shell script to run the sql, then return a 0 or 1 to a managment system called TWS depending on the success of failure of the query(whether it ran or fell over basically).

Can you help with the shell, this is an utterly alien language, where sql is just nerveracking!

Thanks so much

Well, if 0 or "something else" isn't good enough and it HAS to be a 1 or 0, you can use a variation on what I did before with sqlcode.  We'll create a script file that either has "exit 0" or "exit 1" in it.

set linesize 4000 pagesize 0 verify off
set heading off 
set feedback off 
set trimout on trimspool on termout off
set echo off
set prompt off
 
column curr_date new_value file_date noprint
select to_char(sysdate,'DDMMYY') curr_date
from sys.dual;
 
spool /gtxappl/Stock_Mirroring/Results/pyfs_allocablestock_supply&file_date..csv
select upper('item_id,shipnode_key,onhand')  
from dual;
select a.item_id || ',' || b.shipnode_key || ',' || b.quantity  
from yfs_inventory_item a, yfs_inventory_supply b 
where b.supply_type = 'ONHAND'   
and b.inventory_item_key = a.inventory_item_key;
spool off
spool status.sql
show sqlcode
spool off
select decode(
 
get status
c/sql/'sql/
a '
i
'sqlcode 0','exit 0','exit 1') status from dual
 
spool exitwithstatus.sql
/
spool off 
start exitwithstatus
 
Good luck!

Select allOpen in new window

Hi sorry it can 0 or "something else". It just needs to be a shell script.
sqlplus /@ @UnAllocableStock1.sql
This is ok, but it does still need to return the 0 or somthing else for success or failure.
Sorry about the mess around. I just need the shell script to run, and then tell somthing else whether it did or not with a 0 or somthing else.

Unix admin is unreachable. Cant get hold of them.
Is there no way at all of putting the ,
If there are no oracle errors
exit 0
Else
 exit 1
into the /@ @UnAllocableStock1.sql,  shell you were so kind to provide before hand.
The upper echelon are determined that this command be present in the shell rather than the sql.
Im sorry, but you really are going to have to spoon feed this to me, i am an absolute novice. Again, sorry.

Sorry for messing you around.
I will leave you in peace.

You're not messing me around.  I just can't answer without know the shell you are working in because your script should be written for that shell.  Basically, you would have something like this:

!
sqlplus /@ @UnAllocableStock1.sql
exit $?

Although, the sqlplus command SHOULD cause the shell script to exit with its status anyway.  The "$?" is a shell variable that contains the exit status of the last command executed.  You would put the commands I have here into a file with a ".sh" extension.  Then you would run this command on it:

chmod a+x

Good luck!

Thats great, thanks you so much.
Appologies for the latness of response, decided enough was enbough for one day.
You have been extreemly helpful.