ORACLE - can i pass cursor in a parameter and how to use it?-CodePudding

Here is one of oracle procedure. There is a cursor called r_ba.

DECLARE
   r_ba SYS_REFCURSOR;
BEGIN
   OPEN r_ba FOR
      SELECT head.*,
             line.jenis_pekerjaan,
             line.deskripsi_pekerjaan,
             line.akun,
             line.rate_ppn,
             line.dpp,
             line.ppn,
             line.total_realisasi,
             line.major,
             line.minor,
             line.sla
        FROM idm_ap_mtc_acl_header_tmp head, idm_ap_mtc_acl_lines_tmp line
       WHERE head.no_ba = line.no_ba AND head.req_id = line.req_id AND head.req_id = n_req_id;

   create_invoice_ap2 (
      p_org_id      => p_org_id,
      p_user_id     => p_user_id,
      p_branch_code => v_branch_code,
      r_ba          => r_ba);
END;

and how call that cursor in this procedure ?

PROCEDURE create_invoice_ap2 (p_org_id      IN NUMBER,
                              p_user_id     IN NUMBER,
                              p_branch_code IN VARCHAR2,
                              r_ba          IN SYS_REFCURSOR)
IS
   r_tax_info       rt_tax_info;
   rt_ba            SYS_REFCURSOR;
BEGIN
   ------------ setup ------------
   r_tax_info      := get_tax_code (
                         p_date           => rt_ba.tgl_ba,
                         p_group_tax_name => rt_ba.tax_name,
                         p_ppn_rate       => rt_ba.ppn_rate,
                         p_dc_code        => rt_ba.dc_code);
END;

oracle give me error

[Error] PLS-00487 (707: 64): PLS-00487: Invalid reference to variable 'RT_BA'

Anyone could suggest me a way to accomplish this?

Thanks

CodePudding user response：

If you're passing refcursor into a parameter named r_ba, then - I believe - you should use it, not declare another (rt_ba):

PROCEDURE create_invoice_ap2 (p_org_id      IN NUMBER,
                              p_user_id     IN NUMBER,
                              p_branch_code IN VARCHAR2,
                              r_ba          IN SYS_REFCURSOR)
IS
   r_tax_info       rt_tax_info;
   -- rt_ba            SYS_REFCURSOR;        --> you don't need it
BEGIN
   ------------ setup ------------
   r_tax_info      := get_tax_code (
                         p_date           => r_ba.tgl_ba,
                         p_group_tax_name => r_ba.tax_name,
                         p_ppn_rate       => r_ba.ppn_rate,
                         p_dc_code        => r_ba.dc_code);
END;                                         ----
                                              ^
                                              |
                                       r_ba, not rt_ba

Also, note that you should generally avoid select * because it can lead to ambiguity if there are columns with the same name as columns in other tables, so Oracle doesn't know which one you want to use.

The same goes for typos; for example, in anonymous PL/SQL block you're selecting line.rate_ppn, while procedure uses p_ppn_rate => r_ba.ppn_rate (rate_ppn vs. ppn_rate). Maybe it is OK because there's ppn_rate column in head table (which is in select head.*) but - once again - that's difficult to debug.

Furthermore, you can't use refcursor that way - you have to fetch from it. Have a look at example based on Scott's sample schema. Here's a procedure that accepts refcursor as a parameter and does something with it:

SQL> create or replace procedure p_test (r_ba in sys_refcursor)
  2  is
  3    l_dname varchar2(20);
  4  begin
  5    l_dname := r_ba.dname;
  6  end;
  7  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE P_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: Statement ignored
5/19     PLS-00487: Invalid reference to variable 'R_BA'
SQL>

See? The same error you got. Therefore, fetch:

SQL> create or replace procedure p_test (r_ba in sys_refcursor)
  2  is
  3    l_row dept%rowtype;
  4  begin
  5    loop
  6      fetch r_ba into l_row;
  7      exit when r_ba%notfound;
  8      dbms_output.put_line(l_row.deptno ||', '|| l_row.dname ||', '|| l_row.loc);
  9    end loop;
 10  end;
 11  /

Procedure created.

SQL> declare
  2    r_ba sys_refcursor;
  3  begin
  4    open r_ba for select deptno, dname, loc from dept;
  5    p_test (r_ba);
  6  end;
  7  /
10, ACCOUNTING, NEW YORK
20, RESEARCH, DALLAS
30, SALES, CHICAGO
40, OPERATIONS, BOSTON

PL/SQL procedure successfully completed.

SQL>

Now it is OK.

I don't know what you're doing with r_tax_info, how many rows refcursor contains (whether you need to use a loop or not), but - that's the general idea. Fetch first, use it next.