DBMS_OUTPUT.PUT_LINE doesn't print values in shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting DBMS_OUTPUT.PUT_LINE doesn't print values in shell script
# 1  
Old 09-18-2019
DBMS_OUTPUT.PUT_LINE doesn't print values in shell script

Hello,
I'm trying to print the value of my cursor in the dbms_output.put_line in shell script, but it only shows "PL/SQL procedure successfully completed." nothing else. I have set serveroutput on,
Below is my script : Any advise would be really helpful.
Code:
sqlplus -s $ORACLE_LOGON <<EOF >> $alert_file
set serveroutput on size 30000;
set lines 1000
set pagesize 50000
set trimspool on
set heading off
set echo off
set newpage none
set heading OFF;
DECLARE
CURSOR C1
IS
SELECT DISTINCT A.KNOWLEDGE_SESSION_UID AS KSUID
FROM KNOWLEDGE_SESSION A ,
  PROV_KNOWLEDGE_SESSION_USE B ,
  WORK_ORDER_STEPS C
WHERE A.SERIALISED_STATE LIKE '%<STRING>US</STRING>%'
AND A.SERIALISED_STATE NOT LIKE '%<STRING>FA</STRING>%'
AND A.SERIALISED_STATE NOT LIKE '%<STRING>CG</STRING>%'
AND ROUND((SYSDATE -A.LAST_UPDATED_DT )*24*60) > 5
AND A.KNOWLEDGE_SESSION_UID=B.KNOWLEDGE_SESSION_UID
AND B.WO_NO IS NOT NULL
AND A.REVIVE_AFTER_DT IS NULL
AND A.LAST_UPDATED_DT >= SYSDATE -5
AND C.WO_NO=B.WO_NO
AND C.STATUS_CD='LP'
AND C.STEP_DEF_CODE='PRO'
fetch first 20 rows only ;
KS_UID KNOWLEDGE_SESSION.KNOWLEDGE_SESSION_UID%TYPE;
BEGIN
OPEN C1;
FETCH C1 into KS_UID;
DBMS_OUTPUT.PUT_LINE(KS_UID);
END;
/

Regards,
Chiranjit

--- Post updated at 12:34 PM ---

I have now able to print the values, however it is only printing the first value where i am selecting first 20 rows in the cursor.
Is there a way to print all the values ?

Thanks,
Chiranjit

Last edited by mail.chiranjit; 09-18-2019 at 02:39 AM..
# 2  
Old 09-18-2019
You have to use a LOOP to fetch and print all of them:-

Code:
BEGIN
OPEN C1;
LOOP
 FETCH C1 into KS_UID;
 EXIT WHEN C1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(KS_UID);
END LOOP;
CLOSE C1;
END;
/

These 2 Users Gave Thanks to Yoda For This Post:
# 3  
Old 09-20-2019
Thank you for your help, This has Worked.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

The shell script doesn't get arguments, but parse them

Hello, I have a simple shell script, which starts from crontab like this: 00 03 * * 2-6 /export/applications/dte/sh/fwmarg.sh > /export/applications/dte/data/cron_log/fwmarg.cronlog.`date +\%m.\%d` 2>&1 The script doesn't get any argument. But inside it I see the line ... (10 Replies)
Discussion started by: digioleg54
10 Replies

2. UNIX for Dummies Questions & Answers

Reading XML file and print the values in the text file using Linux shell script

hi guys, i want help... Reding XML file and print the values into the text file using linux shell script file as per below xml file <sequence> <Filename>aldorzum.doc</Filename> <DivisionCode>US</DivisionCode> <ContentType>Template</ContentType> <ProductCode>VIMZIM</ProductCode> </sequence>... (1 Reply)
Discussion started by: sravanreddy
1 Replies

3. Shell Programming and Scripting

Script doesn't print error.

Hi Gurus, I have below sample script. I expect it print error when running script without input parameter. but the it doesn't. would you please help me about this issue. thanks in advance. /script$cat test.ksh #!/bin/ksh while getopts :f: arg do case $arg in ... (4 Replies)
Discussion started by: ken6503
4 Replies

4. Shell Programming and Scripting

Query the table and return values to shell script and search result values from another files.

Hi, I need a shell script, which would search the result values from another files. 1)execute " select column1 from table_name" query on the table. 2)Based on the result, need to be grep from .wft files. could please explain about this.Below is the way i am using. #!/bin/sh... (4 Replies)
Discussion started by: Rami Reddy
4 Replies

5. Shell Programming and Scripting

[Solved] How to display only output of DBMS_OUTPUT.PUT_LINE , rest should be neglected

Hi All, I Have written a script through that i am calling sql file Sqlfile.sql set time on set timing on set echo on set head off set scan on set feedback on set serveroutput on set linesize 1000 DECLARE v_acc_no NUMBER(10); v_product_no NUMBER(10); BEGIN... (3 Replies)
Discussion started by: sujit_kashyap
3 Replies

6. Programming

How to Format database output (DBMS_OUTPUT.PUT_LINE) in unix?

Dear All, As I'm new here, please forgive me if any rule violation occurred. I have a script like this: #! /bin/ksh # Author : Saptarshi # Date : 18-Mar-2011 # Version : 1.0 Return_op=`sqlplus -s <<ENDOFSQL db_user/db_pass@db_nm WHENEVER SQLERROR EXIT 1 set ... (1 Reply)
Discussion started by: saps19
1 Replies

7. Shell Programming and Scripting

shell script, echo doesn't work

#!/bin/sh something(){ echo "Inside something" echo $1 $2 } val=$(something "Hello " "world") Output expected: Inside somethingHello world But it's not echoing. (4 Replies)
Discussion started by: cola
4 Replies

8. Shell Programming and Scripting

sqlplus and dbms_output.put_line in shell script

Hi, I have created a pl/sql block utilises dbms_output.put_line. This script works fine when I call from sqlplus ie sqlplus @./scriptname but when I embed it within my shell script I get no output to screen so I end up with an empty spool file. I know it's executing successfully when called from... (6 Replies)
Discussion started by: stuck1
6 Replies

9. Shell Programming and Scripting

Shell script doesn't get executed using crontab

I have the following crontab entry to run a shell script for every 30 minutes of every day: 30 * * * * $HOME/main.sh > $HOME/main.log 2>$HOME/error.log after I created the crontab file I have also done: $crontab my_crontab I also check to make sure it exists, by using the following... (11 Replies)
Discussion started by: radhika
11 Replies
Login or Register to Ask a Question

Featured Tech Videos