Passing cursors to PROCS

[LarrySteele]

I have a few dozen scripts that share 90% of their code. Primary difference is the cursor that's opened while running said script.

What I'd like to do is create a PROC and pass the SQL for the cursor I want the PROC to use. I've Googled and found references to REFCURSOR which I thought might be applicable, but have not had success. I've tried so many variations that I'm to the point where I need to clear the slate and start over.

Here's what I want to do...

create or replace procedure table_stuff ([b]in_cursor     varchar2[/b]) as

  cursor csr_data is [b]in_cursor[/b];

begin
  open csr_data;
    loop
      fetch csr_data
        {do stuff}
    end loop;
  close csr_data;
exception
  {get over it}
end;
/

You see what I have in bold. The idea is that I want the text from in_cursor to be used in the cursor definition.

One of the things I tried was creating a PROC that's supposed to generate a cursor. I pulled said script from here:

http://forums.oracle.com/forums/thread.jspa?threadID=694753.

create or replace procedure gen_cur(str in varchar2,rc in out sys_refcursor) is
  str1 varchar2(2000);
begin
  str1 := str;
  open rc for str1;
  exception
    when others then dbms_output.put_line(sqlerrm);
end;
/

In a PROC, I mimicked what I saw:

variable csr_data refcursor;
gen_cur(in_cursor,:csr_data);

Oracle's response:

PLS-00103: Encountered the symbol "REFCURSOR" when expecting one of the following:

   := . ( @ % ; not null range default character

The first line was highlighted.

Tried what I'm a bit more used to:

csr_data refcursor;
gen_cur(in_cursor,:csr_data);

This time Oracle complained about the second line:

PLS-00049: bad bind variable 'CSR_DATA'

Other variations met with the same level of success (read: failure).

How do I pass an SQL string to a PROC and have the PROC use that SQL string as a cursor? I'm sure the answer's easy, but it's eluded me so far...

Thanks in advance,
Larry

 

[SantaMufasa]

Larry,

I'll attempt to answer your question(s) with some examples. The examples will start out slow, then progress toward what, I believe, you are asking about.

First, here are some data from two entirely different tables (a Department table, S_DEPT, and an Employee table, S_EMP):

select * from s_dept;

ID NAME                       REGION_ID
-- ------------------------- ----------
10 Whatever New Finance               1
31 Sales                              1
32 Sales                              2
33 Sales                              3
34 Sales                              4
35 Sales                              5
41 Operations                         1
42 Operations                         2
43 Operations                         3
44 Operations                         4
45 Operations                         5
50 Administration                     1

select id, last_name, first_name from s_emp;

ID LAST_NAME    FIRST_NAME
-- ------------ ----------
 1 Jones        Carmen
 2 Ngao         LaDoris
 3 Ubu          Midori
 4 Quick-To-See Mark
 5 Ropeburn     Audry
 6 Urguhart     Molly
 7 Menchu       Roberta
 8 Biri         Ben

Next, here is a simple (contrived) PL/SQL procedure that illustrates a minimual usage of a generalized SYS_REFCURSOR. The procedure ("ShowRefCur") requires any two-column SELECT statement as an incoming argument, then the procedure displays those data:

create or replace procedure ShowRefCur(sql_select varchar2) is
    type any_rec is record (col_1 number, col_2 varchar2(50));
    r any_rec;
    rc sys_refcursor;
begin
 open rc for sql_select;
 loop
  fetch rc into r;
  exit when rc%notfound;
  dbms_output.put_line(r.col_1 ||' - '||r.col_2);
 end loop;
end;
/

Procedure created.

SQL> exec ShowRefCur ('select id,name from s_dept where rownum <= 2')

10 - Whatever New Finance
31 - Sales

PL/SQL procedure successfully completed.

SQL> exec ShowRefCur ('select id,last_name from s_emp where rownum <= 2')

1 - Jones
2 - Ngao

PL/SQL procedure successfully completed.

Now, here is a procedure ("GetRefCur") that is, perhaps, similar to what you were asking for. The procedure is a variation (and reduction) of the procedure, above, but it returns the contents of the SYS_REFCURSOR from the generic procedure to the calling environment:

create or replace procedure GetRefCur(sql_select varchar2, tabdata out sys_refcursor) is
    type any_rec is record (id number, col_2 varchar2(50));
    r any_rec;
    rc sys_refcursor;
begin
	open rc for sql_select;
	tabdata := rc;
end;
/

Procedure created.

declare
    type some_rec is record (col_one number, col_two varchar2(50));
    x some_rec;
    my_cursor sys_refcursor;
begin
    dbms_output.put_line('Departments');
    dbms_output.put_line('====================');
    GetRefCur ('select id,name from s_dept where rownum <= 2',my_cursor);
    loop
        fetch my_cursor into x;
        exit when my_cursor%notfound;
        dbms_output.put_line(x.col_one ||' - '||x.col_two);
    end loop;
    dbms_output.put_line(' ');
    dbms_output.put_line('Employees');
    dbms_output.put_line('====================');
    GetRefCur ('select id,last_name from s_emp where rownum <= 2',my_cursor);
    loop
        fetch my_cursor into x;
        exit when my_cursor%notfound;
        dbms_output.put_line(x.col_one ||' - '||x.col_two);
    end loop;    
end;
/

Departments
====================
10 - Whatever New Finance
31 - Sales

Employees
====================
1 - Jones
2 - Ngao

PL/SQL procedure successfully completed.

After you look over the above, and have a change to digest it, if additional questions remain, please post.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LarrySteele]

Dave,

Thanks for the response & suggestion. It appears to be somewhat straightforward. I'll give it a go on Monday and let you know how it goes.

Have a great weekend,
Larry

 

[LarrySteele]

Dave,

I'm looking at GetRefCur() and having a problem identifying one item - how to define the any_rec type to handle any record type.

Here's the definition I'm using:

type data_tbl is table of csr_data%rowtype;

You can probably guess that this is after I've defined my cursor, and your guess would be correct.

For whatever reason, I can't figure out how to do something similar in your sample. I have scores of different cursor definitions and need to adjust the proc to handle the different definitions itself.

I probably should be able to figure this out myself, but meetings went long today and my brain's turned to goo.

 

[LarrySteele]

Dave,

Shoulda, woulda, coulda.

Looking a the Google%ROWTYPE attribute definition, it confirms what I suspected - need to have the cursor defined prior to using it to declare a new type based on cursor's %ROWTYPE. Seems logical.

Then I looked at your GetRefCur() proc again. The any_rec type is only used in declaring r. However, it looks like r is not used in GetRefCur(). If I'm reading correctly, seems like I don't need to declare the new type and don't need to declare r.

Am I missing something?

If I'm correct, then I can handle the type declaration in the consuming script, and just move the type declaration thusly:

my_cursor sys_refcursor;
type some_rec is my_cursor%rowtype;
x some_rec;

What do you think? Am I heading off the good and right track on this?

Thanks again,
Larry

 

[SantaMufasa]

Larry,

When you say, "I have a few dozen scripts that share 90% of their code," how are you causing the shared code to refer to table column names that are specific to the different tables?

If I had shared functionality that I wanted to use for a variety of tables/columns, I would create common procedures. Those procedures would use incoming/outgoing arguments that allow me to pass in, and receive out, values, regardless of their names in the various tables.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sorry, Larry...I cross posted my entry, above, without first seeing your most recent posting.

If Oracle were "smart enough" to handle your construct, then, yes, yours would be the way to go. Unfortunately, the "...%rowtype" construct (as far as I know) must refer to a table definition that exists in the data dictionary at procedure compile time.

Sorry to bear bad news.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LarrySteele]

Thanks again Dave.

As far as %rowtype, in addition to physical tables, you can create a %rowtype on a cursor. Here's an example:

  cursor csr_data is
    select p.persid,
           case when el.mail_site is null then eh.home_country
                when el.mail_site = 'FS' then eh.home_country
                when el.mail_site = '99' then substr(el.loc_code,3,2)
                else 'US' end as country_code
    from pa0001 p,
         ee_loc_code el,
         ee_home eh
    where el.persid=p.persid
      and eh.persid=p.persid;

type typ_tbl is table of csr_data%rowtype;

But as best I can tell, you're correct about not being able to use the %rowtype declaration as I wanted.

Good news! I have the solution I was looking for.

At the beginning of this batch of scripts I have the following which is specific to the table being loaded:

def tbl     = '{table name}';
def scrpt   = '{name of this script - for error reporting};
def dep_tbl = '{list of dependent tables}';
def key1    = '{column we would use to identify rows with errors}';

declare
  cursor csr_data is
    {select sql statement;

The remainder of the script is the same, no matter which table I'm loading. First part checks to make sure dependent tables were successfully loaded on the current date (as an example - if the employees table needs to have the employee_names table loaded, then employee_names would be a dependent of the employees table). If there's a load failure in any of a table's dependent tables, then we raise a user defined error and record this tables load as a bad load. If all dependent tables load okay, then we bulk load the cursor, 25k records at a time and insert into our destination table, saving exceptions as we go. When inserts are completed, we'll save up to 25 DML exceptions in our load errors table for troubleshooting. At the end, we'll record the script that ran, the table that was loaded (or not), the number of rows inserted, the number of errors, whether it was a bad load (10%+ or 0 rows inserted), and date/time.

In the above, I use the temporary substitution variables from the top to fill in the blanks (table names, script names, etc).

Works effectively, but from a programming standpoint, not most efficient. When 80% of the code in scores of scripts is identical, I want to put that 80% in a common location - function, proc, file, whatever.

I tried searching for Oracle includes file (thinking along the line of using includes files in HTML & Cold Fusion). From what I read, no can do in Oracle. Tried a few other ideas that lead me off the good and right path. Tried this method and once again no joy.

Last night revisited the includes file idea. While I was still unsuccessful finding the solution online, I took a shot in the dark and guessed the solution.

Here's the solution to my quest. I placed all the common lines in a file cleverly named common.sql and added the following lines to my script above:

@common.sql;
begin
  null;
end;
/

I'm not experienced enough with Oracle to speak to what it is doing, but I will tell you the symptoms. It works as if I copied the text from common.sql and placed it right in my script. That's exactly what I wanted it to do.

Now, about that begin...null...end stuff. I didn't have that initially and was getting some goofy responses. If I remember, I was getting double notifications from common.sql (or it may have been one of the other output curiosities). Anyway, when I added that block, everything fell right into place.

It's nice, because if I ever want to change the error or load stats table, I only have to change one script instead of scores of scripts.

how are you causing the shared code to refer to table column names that are specific to the different tables?

Good question...

Here's common.sql:

  -- common variables 
  dep_bad_load    number :=1;
  dep_loads       number :=0;
  error_cnt       number :=0;
  error_cnt1      number :=0;
  error_idx       number;
  error_code      number;
  error_desc      varchar2(600 char);
  error_stats     number :=0;
  end_loop        number :=0;
  row_cnt         number :=0;
  bad_load        varchar2(1 char) := 'N';
  dml_errors      exception;
  pragma          exception_init(dml_errors, -24381);
  time_start      number := dbms_utility.get_time;
  time_stop       number;
  time_total      varchar2(20 char);
  script_name     varchar2(50 char) := '&&scrpt';
  table_name      varchar2(50 char) := '&&tbl';
  
  -- table variables defined from our source.
  type sap_tab is table of csr_data%rowtype;
  sap_tbl  sap_tab := sap_tab();

begin
  
-- check the tables we depend on...
  select sum(bad_load), 
         sum(loads)
  into dep_bad_load,
       dep_loads
  from (
        select distinct   -- tables loaded more than once per day will only count once
               case bad_load when 'N' then 0 else 1 end bad_load,
               table_name,
               1 loads 
        from hrdm_load_stats 
        where load_date = to_date(sysdate) 
          and table_name in ('&&dep_tbl'));

  if  nvl(dep_bad_load,0) != 0 or nvl(dep_loads,0) != &&dep_cnt then
    
    -- record bad load stat and error for this table
    insert into hrdm_load_stats
      (
        script_name,
        table_name,
        insert_count,
        error_count,
        bad_load,
        load_date,
        load_time
      )
    values
      (
        script_name,
        table_name,
        0,
        0,
        'Y',
        to_date(sysdate),
        to_char(sysdate, 'hh24:mi:ss')
      );

    insert into hrdm_load_errors
      (
        script_name,
        table_name,
        error_code,
        error_desc,
        key_data,
        load_date
      )
    values
      (
        script_name,
        table_name,
        '-20002',
        'ORA-20002: TABLE LOAD HOLD FOR ' || table_name, 
        '',
        to_date(sysdate)
      );

    -- raise user defined error
    raise_application_error (-20002, 'TABLE LOAD HOLD FOR ' || table_name);
  end if;
    
  begin
    -- if we make it here, our source tables loaded okay

    execute immediate 'truncate table &&tbl';

-- bulk collect (loads into memory) from cursor
    open csr_data;
      loop
        fetch csr_data
        bulk collect into sap_tbl limit 25000;
        exit when sap_tbl.count = 0;

          begin
            -- forall (bulk save from memory into our table) recording exceptions if they occur
            forall i in sap_tbl.first..sap_tbl.last save exceptions
              insert into &&tbl values sap_tbl(i);

            -- keep track of rows inserted
            row_cnt := row_cnt + sql%rowcount;   -- just in case we didn't have an error

          exception
            -- record up to 25 individual record errors in the load_errors table
            -- that will be used to aid troubleshooting
            when dml_errors then
              row_cnt := sql%rowcount;    -- pick up the rowcount from last process sql (forall...)
              error_cnt := sql%bulk_exceptions.count;
              error_cnt1 := error_cnt1 + error_cnt;

              -- record the error records, but no more than 25, if we have that many
              if error_cnt > 25 then
                end_loop := 25;
              else
                end_loop := error_cnt;
              end if;

              for e in 1..end_loop loop  --error_cnt loop
                error_idx := sql%bulk_exceptions(e).error_index;
                error_code := sql%bulk_exceptions(e).error_code;
                error_desc := sqlerrm(-error_code);

                insert into hrdm_load_errors
                  (
                    script_name,
                    table_name,
                    error_code,
                    error_desc,
                    key_data,
                    load_date
                  )
                values
                  (
                    script_name,
                    table_name,
                    to_char(-error_code),
                    error_desc, 
                    '&&key1: "' || sap_tbl(error_idx).&&key1 || '"',
                    to_date(sysdate)
                  );
              end loop;   -- dml error writing
            -- if others, propogate the error up
          end;    -- exception
      end loop;   -- block collecting from our cursor
    close csr_data;

  exception
    when others then
      bad_load := 'Y';  -- unexpected/unhandled error: bad load to investigate
  end;

-- process load stats
-- record when tables are loaded (or not loaded) and their success (or failure)
  select count(1)
  into row_cnt
  from &&tbl.;

  if row_cnt > 0 then
    error_stats := (error_cnt / row_cnt) * 100;
  else
    error_stats := 100;   -- if no rows inserted, then 100% load failure
  end if;

  if error_stats > 10 then    -- more than 10% failed to load
    bad_load := 'Y';
  end if;

  insert into hrdm_load_stats
    (
      script_name,
      table_name,
      insert_count,
      error_count,
      bad_load,
      load_date,
      load_time
    )
  values
    (
      script_name,
      table_name,
      row_cnt,
      error_cnt,
      bad_load,
      to_date(sysdate),
      to_char(sysdate, 'hh24:mi:ss')
    );
  commit;

  -- script time can be used during development, but not recorded/reported in production
  time_stop := dbms_utility.get_time;
  time_total := to_char((time_stop - time_start)/100);
  
-- post user defined error (if it occurs) to the screen for review.  
-- otherwise post results to the screen for review 
  begin
    if bad_load = 'Y' then
      raise_application_error (-20001, 'BAD LOAD ERROR FOR ' || table_name);
    else
      dbms_output.put_line(table_name || ' loaded.  ' || 
                           row_cnt || ' rows inserted - ' || 
                           error_cnt1 || ' errors.');    
    end if;
  exception
    when others then
      if to_char(sqlcode) = -20001 then
        -- our error code
        dbms_output.put_line(sqlerrm);
      else
        -- some other error occured...
        dbms_output.put_line(script_name || ' LOAD ERROR: ' || to_char(sqlcode) || '-' || sqlerrm);
      end if;
  end;
  
exception
  when others then
      if to_char(sqlcode) = -20002 then
        -- our error code
        dbms_output.put_line(sqlerrm);
      else
        -- some other error occured...
        dbms_output.put_line(script_name || ' LOAD ERROR: ' || to_char(sqlcode) || '-' || sqlerrm);
      end if;  
  
end;
/

Confession... I could probably tidy this up a bit. It's been evolving over time until I got it to do *what* I need it to do. For now what's important for us is that the code works and works well. I'll probably review the logic some more at a later date but right now I need to get-r-done as I have a few projects in the wing .