Build Cursor, passing in table name as a variable

[bgreen]

How can I build a cusor in PL/SQL by passing in thae table name as a variable?

ex
l_vExtTable Varchar2(2000) := 'XX'||i_vCompanyName||'_GLCONV_ACT_EXT';

cursor conv_data is
SELECT Period_name, Set_Of_Books_ID, Segment1 Company, User_Je_Source_Name, Actual_Flag,
To_Char(Min(Accounting_Date),'MON-RR') From_Period,
To_Char(Max(Accounting_Date),'MON-RR') To_Period,
Sum(Abs(Entered_Dr)) Total_Dr, Sum(Abs(Entered_Cr)) Total_Cr,
NVl(Sum(Abs(Entered_Dr)),0)-nvl(Sum(Abs(Entered_Cr)),0) Difference, Currency_code,
Count(*) Line_Count, Null Budget_Name
FROM l_vExtTable
WHERE UPPER(i_vActualFlag) = 'A'
GROUP By Period_name,Set_Of_Books_ID, Segment1, User_Je_Source_Name, Actual_Flag ,
Currency_code;

 

[SantaMufasa]

BGreen,

Try this variation:

DECLARE
    [b]rc sys_refcursor;
    v <name of permanent table with "...GLCONV_ACT_EXT" format>%rowtype;[/b]
    l_vExtTable Varchar2(2000) := 'XX'||i_vCompanyName||'_GLCONV_ACT_EXT';
BEGIN
     [b]open rc for [/b]SELECT Period_name, Set_Of_Books_ID, Segment1 Company, User_Je_Source_Name, Actual_Flag,  
               To_Char(Min(Accounting_Date),'MON-RR') From_Period, 
               To_Char(Max(Accounting_Date),'MON-RR') To_Period, 
                  Sum(Abs(Entered_Dr)) Total_Dr, Sum(Abs(Entered_Cr)) Total_Cr, 
                  NVl(Sum(Abs(Entered_Dr)),0)-nvl(Sum(Abs(Entered_Cr)),0) Difference, Currency_code,
                  Count(*) Line_Count, Null Budget_Name 
        FROM   l_vExtTable
        WHERE UPPER(i_vActualFlag) = 'A'
        GROUP By Period_name,Set_Of_Books_ID, Segment1, User_Je_Source_Name, Actual_Flag , 
                   Currency_code;[b]
    loop
        fetch rc into v;
        exit when rc%notfound;
        dbms_output.put_line(v.Period_name...);
    end loop; -- illustrates how to access refcursor
END;[/b]
/

Let us know if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[bgreen]

I am not sure what you mean by this statement:

v <name of permanent table with "...GLCONV_ACT_EXT" format>%rowtype;

do you mean

rc sys_refcursor;
v XXCGIC_GLCONV_ACT_EXT%rowtype;

where XXCGIC_GLCONV_ACT_EXT is a real table with same types of columns.

 

[SantaMufasa]

Exactly !

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[bgreen]

I got an error

PL/SQL: ORA-00942: table or view does not exist

I am creating this in a Package Body is there different syntax?

 

[SantaMufasa]

Have you first successfully tested the relevant syntax in a simple, anonymous PL/SQL block (script)? Once you got the block working, did it give you the results you wanted?

There may be idiosyncracies about refcursor syntax when you include it in a user-defined object (i.e., package, procedure, function). If you post the relevant portion of your package-body code, I can troubleshoot it for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]