CODEWITHSUNDEEP
oracle-databaseplsqlcursorref-cursor
user5299399
user5299399

Reputation:

Oracle REFCURSOR error with declare and setting up variables

I am rather new to Oracle, I use sql server for years. I been trying to get a cursor loop to work. I had found an example that is in a Function, but for testing, i'm not wanting to create the function just yet.

I was hoping this type of syntax would work, what am i doing wrong? I'm using PL/SQL Developer 

declare 
c_actual REFCURSOR;  
v_crkt_nb log_step_circuit.crkt_nb%TYPE;
v_string  VARCHAR2(50);

v_string := '';

open c_actual for 

SELECT 
  u.LGN_ID_NM 
  FROM work_folder f,
   trouble_call    t,
   doc_log_step    d,
   impacted_device i,
   user_assignment a,
   user_role       r,
   oms_user        u,
   service_location s
 WHERE t.impct_dev_k = i.impct_dev_k
    and i.doc_log_stp_k = d.doc_log_stp_k
    and d.work_fldr_k = f.work_fldr_k
    and s.locatn_k = t.locatn_k
    and f.work_fldr_k = a.work_fldr_k
    and a.user_role_k = r.user_role_k
    and FOLDER_PKG.TroubleCallCount(f.work_fldr_k) = 1
    and r.lgn_id_nm = u.lgn_id_nm
    AND t.oms_tcall_k = 563815
    and f.work_fldr_k in
    (select work_fldr_k
      from work_folder
     WHERE work_folder.fldr_stts_c in ('NEW', 'IN PROGRESS'))
     AND ROLE_C = 'TRBLSHTR' and a.unasn_d is null;

     LOOP
       FETCH c_actual INTO v_crkt_nb;
     EXIT WHEN c_actual%NOTFOUND;  

     v_string := substr(v_string || v_crkt_nb || '  ',1, 50);
     END LOOP;

     v_string := trim(v_string);

     CLOSE c_actual;

Upvotes: 0

Views: 93

Answers (1)

Md. Shamim Al Mamun
Md. Shamim Al Mamun

Reputation: 386

Have a look below.

declare 
v_crkt_nb log_step_circuit.crkt_nb%TYPE;
v_string  VARCHAR2(500);
cursor c1 is
  SELECT u.LGN_ID_NM 
  FROM work_folder f,
   trouble_call    t,
   doc_log_step    d,
   impacted_device i,
   user_assignment a,
   user_role       r,
   oms_user        u,
   service_location s
 WHERE t.impct_dev_k = i.impct_dev_k
    and i.doc_log_stp_k = d.doc_log_stp_k
    and d.work_fldr_k = f.work_fldr_k
    and s.locatn_k = t.locatn_k
    and f.work_fldr_k = a.work_fldr_k
    and a.user_role_k = r.user_role_k
    and FOLDER_PKG.TroubleCallCount(f.work_fldr_k) = 1
    and r.lgn_id_nm = u.lgn_id_nm
    AND t.oms_tcall_k = 563815
    and f.work_fldr_k in
    (select work_fldr_k
      from work_folder
     WHERE work_folder.fldr_stts_c in ('NEW', 'IN PROGRESS'))
     AND ROLE_C = 'TRBLSHTR' and a.unasn_d is null;
Begin
v_string := '';

FOR i in c1

     LOOP

       EXIT WHEN c1%NOTFOUND;  

       v_string := substr(v_string || v_crkt_nb || '  ',1, 50);
     END LOOP;

     v_string := trim(v_string);

end;
/

Hope it will help you.

Upvotes: 2

Related Questions