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