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

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... (11 Replies)
Discussion started by: radhika
11 Replies

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... (6 Replies)
Discussion started by: stuck1
6 Replies

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 Replies)
Discussion started by: cola
4 Replies

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 ... (1 Reply)
Discussion started by: saps19
1 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. 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

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 ... (4 Replies)
Discussion started by: ken6503
4 Replies

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>... (1 Reply)
Discussion started by: sravanreddy
1 Replies

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 ... (10 Replies)
Discussion started by: digioleg54
10 Replies
DB2_NUM_ROWS(3) 							 1							   DB2_NUM_ROWS(3)

db2_num_rows - Returns the number of rows affected by an SQL statement

SYNOPSIS
int db2_num_rows (resource $stmt) DESCRIPTION
Returns the number of rows deleted, inserted, or updated by an SQL statement. To determine the number of rows that will be returned by a SELECT statement, issue SELECT COUNT(*) with the same predicates as your intended SELECT statement and retrieve the value. If your application logic checks the number of rows returned by a SELECT statement and branches if the number of rows is 0, consider modi- fying your application to attempt to return the first row with one of db2_fetch_assoc(3), db2_fetch_both(3), db2_fetch_array(3), or db2_fetch_row(3), and branch if the fetch function returns FALSE. Note If you issue a SELECT statement using a scrollable cursor, db2_num_rows(3) returns the number of rows returned by the SELECT state- ment. However, the overhead associated with scrollable cursors significantly degrades the performance of your application, so if this is the only reason you are considering using scrollable cursors, you should use a forward-only cursor and either call SELECT COUNT(*) or rely on the boolean return value of the fetch functions to achieve the equivalent functionality with much better perfor- mance. PARAMETERS
o $stmt - A valid stmt resource containing a result set. RETURN VALUES
Returns the number of rows affected by the last SQL statement issued by the specified statement handle. PHP Documentation Group DB2_NUM_ROWS(3)
All times are GMT -4. The time now is 01:54 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy