DBMS_OUTPUT.PUT_LINE doesn't print values in shell script Post: 303038942

Sponsored Content

Top Forums Shell Programming and Scripting DBMS_OUTPUT.PUT_LINE doesn't print values in shell script Post 303038942 by mail.chiranjit on Wednesday 18th of September 2019 02:04:05 AM

09-18-2019

Registered User

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..

mail.chiranjit

View Public Profile for mail.chiranjit

Find all posts by mail.chiranjit

9 More Discussions You Might Find Interesting

1. 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...

2. 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...

3. 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. 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 ...

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...

6. 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...

7. 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 ...

8. 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>...

9. 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 ...

LEARN ABOUT OPENDARWIN

script

SCRIPT(1)						    BSD General Commands Manual 						 SCRIPT(1)

NAME

     script -- make typescript of terminal session

SYNOPSIS

     script [-a] [-k] [-q] [-t time] [file [command ...]]

DESCRIPTION

     The script utility makes a typescript of everything printed on your terminal.  It is useful for students who need a hardcopy record of an
     interactive session as proof of an assignment, as the typescript file can be printed out later with lpr(1).

     If the argument file is given, script saves all dialogue in file.	If no file name is given, the typescript is saved in the file typescript.

     If the argument command ... is given, script will run the specified command with an optional argument vector instead of an interactive shell.

     Options:

     -a       Append the output to file or typescript, retaining the prior contents.

     -k       Log keys sent to program as well as output.

     -q       Run in quiet mode, omit the start and stop status messages.

     -t time  Specify time interval between flushing script output file.  A value of 0 causes script to flush for every character I/O event.  The
	      default interval is 30 seconds.

     The script ends when the forked shell (or command) exits (a control-D to exit the Bourne shell (sh(1)), and exit, logout or control-d (if
     ignoreeof is not set) for the C-shell, csh(1)).

     Certain interactive commands, such as vi(1), create garbage in the typescript file.  The script utility works best with commands that do not
     manipulate the screen.  The results are meant to emulate a hardcopy terminal, not an addressable one.

ENVIRONMENT

     The following environment variable is utilized by script:

     SHELL  If the variable SHELL exists, the shell forked by script will be that shell.  If SHELL is not set, the Bourne shell is assumed.  (Most
	    shells set this variable automatically).

SEE ALSO

     csh(1) (for the history mechanism).

HISTORY

     The script command appeared in 3.0BSD.

BUGS

     The script utility places everything in the log file, including linefeeds and backspaces.	This is not what the naive user expects.

     It is not possible to specify a command without also naming the script file because of argument parsing compatibility issues.

     When running in -k mode, echo cancelling is far from ideal.  The slave terminal mode is checked for ECHO mode to check when to avoid manual
     echo logging.  This does not work when in a raw mode where the program being run is doing manual echo.

BSD
								   June 6, 1993 							       BSD