Visit Our UNIX and Linux User Community


Help in getting pl/sql output messages in script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help in getting pl/sql output messages in script
# 1  
Old 10-23-2009
Question Help in getting pl/sql output messages in script

Hi ,

I have a pl/sql procedure which takes its input from a shell script, and deletes rows from db table based on the input.
I've dbms_output.put_line statments in procedure which i want to capture and display in the script and change the course of the execution of the script.

for example, if no rows have been deleted, i have to stop executing the rest of the script.

the issue is that the dbms_output statements are not displayed on the screen even when the serveroutput is on

Can anyone give me ideas on how to implement this.

Last edited by justchill; 10-23-2009 at 06:11 AM..
# 2  
Old 10-28-2009
Code:
set serveroutput on

declare
begin
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('This is a very very bad habit to use DBMS_OUTPUT - I should never ever use it as it is a bad thing');
DBMS_OUTPUT.PUT_LINE('I can insert into some table instead using PRAGMA for autonomous transaction');
end;

Note the DBMS_OUTPUT.ENABLE. You might be interested in Perl and DBI module as well... I would suggest that (and I suggest python rather than Perl).

Last edited by pludi; 10-28-2009 at 07:09 PM.. Reason: code tags, please...

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script appending output of sql query

I am writing the following script to create the file v_out.txt. sqlplus -s /nolog << EOF CONNECT scott/tiger@orcl; whenever sqlerror exit sql.sqlcode; set newpage 0; SET PAGESIZE 0; SET ECHO OFF; SET FEEDBACK OFF; SET HEADING OFF; SET VERIFY OFF; SET LINESIZE 100; set tab off; set... (7 Replies)
Discussion started by: itzkashi
7 Replies

2. Shell Programming and Scripting

SQL Query in Shell Script output formatting

Hi All, #!/bin/ksh call_sql () { sql=$1 sqlplus -s $sqlparam_sieb <<EOF SET ECHO OFF; SET NEWPAGE NONE; SET SQLBL OFF; SET VERIFY OFF; SET LINESIZE 2000; SET... (2 Replies)
Discussion started by: Kevin Tivoli
2 Replies

3. Shell Programming and Scripting

SQL query in UNIX script - output in flat file

Hi, I never did this before... what I want to do is execute a SQL query from a unix script and redirect sql query's output to a flat file (comma separated one) without the header info (no column names). I would also want not to print the query's output to the screen. snapshot of my script:... (13 Replies)
Discussion started by: juzz4fun
13 Replies

4. Shell Programming and Scripting

Awk script to run a sql and print the output to an output file

Hi All, I have around 900 Select Sql's which I would like to run in an awk script and print the output of those sql's in an txt file. Can you anyone pls let me know how do I do it and execute the awk script? Thanks. (4 Replies)
Discussion started by: adept
4 Replies

5. Shell Programming and Scripting

How to Pass the Output Values from the PL/SQL Procedure to Shell Script?

hi, Could anyone tell me how to pass the output values of the PL/SQL procedure to Shell script and how to store that values in a shell script variable... Thanks in advance... (5 Replies)
Discussion started by: funonnet
5 Replies

6. Shell Programming and Scripting

SQL Script's output to a CSV file

I need to call and execute an SQL script within a KSH script and get the output/extracted data into a CSV file. Is there any way to get the out put in a CSV file other than spooling ? I tried spooling. Problem is if there is any wrning/comment/Error they all will be spooled into the csv file. I... (4 Replies)
Discussion started by: Sriranga
4 Replies

7. UNIX for Dummies Questions & Answers

Cut an sql script output

Hi i want to cut the first field of this output obtained form sql script more dxlocks_test.log.1 SID SERIAL# SPID ---------- ---------- --------- 25 18356 1029 78 39370 1025 136 14361 1027 ================================ cut -f1... (2 Replies)
Discussion started by: aishwaryakala
2 Replies

8. Shell Programming and Scripting

have a shell script done in pl/sql and want output in html

I have this shell script where I have both pl/sql and sql. But want to have a snigle output file where the result of each cursors are in HTML tables. I was able to do that on my old script but it was only sql scripts (no pl/sql). Can I do have such outputs now with my new script where I... (2 Replies)
Discussion started by: arobert
2 Replies

9. Shell Programming and Scripting

need help in reading a output of a sql query in unix script

i'm used a sql query in a unix script to get the information from table. but unable to extract the output which i need. Any help with logic will be greatly appreciated. my sql query provide output some thing like this - col1 col2 count ---- ---- ------ A B 10 c D 6 e... (8 Replies)
Discussion started by: pharos467
8 Replies

10. Shell Programming and Scripting

redirecting SQL output from within a shell script

Hi all I would like to open a SQL session from within a shell script and then redirect the output of the SQL command to a file. e.g. #!/bin/bash sqlplus "/ as sysdba" <<EOF @$HOME/recovery_space.sql EOF I want to redirect the output of the SQL command to a temp file, because... (2 Replies)
Discussion started by: soliberus
2 Replies

Featured Tech Videos