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.

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

You have to use a LOOP to fetch and print all of them:-

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

Thank you for your help, This has Worked.

Moderator comments were removed during original forum migration.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.