Trying to emulate the split() function in Oracle 1

[LarrySteele]

This isn't rocket science, but I'm hitting a mental block that I can't seem to get past. Google works great if you know how to frame your question in a few words, but haven't figured the magic words to ask.

Given a string of table names like this: [tt]'HIS_TABLE,HER_TABLE,MY_TABLE,YOUR_TABLE'[/tt], how do I walk through each and send it as part of a SQL select?

I have a function setup that accepts a single table name and gives me the required return. What I need is one that allows me to walk through a comma-separate list of table name(s) and gives me the status for all.

Here's the function I have that gives me a return based on a single table:

CREATE OR REPLACE function dep_tbl_load(tbl_name varchar2)
  return varchar2
  is
  -- given a table name, see if it was successfully loaded today
  -- return 'Y' if yes, otherwise 'N'
  v_recs            number;

begin

    select count(1)
    into v_recs
    from hrdm_load_stats
    where bad_load = 'N'
      and load_date = to_date(sysdate)
      and table_name = tbl_name;
    
    if v_recs = 1 then
      return 'Y';
    else
      return 'N';
    end if;

exception
  when others then
    dbms_output.put_line('Error in dep_tbl_load function: ' || to_char(sqlcode) || '-' || sqlerrm);        
end;
/

Function works great. I'm trying to figure out is how to go through a comma-separated list of table name(s) and run each through this function.

In other languages I'd go to the split() function, but not so here.

TIA,
Larry

 

[SantaMufasa]

Larry,

There seems to be a minor ambiguity in your request:

What I need is one that allows me to walk through a comma-separate list of table name(s) and gives me the status for all.

I'm trying to figure out is how to go through a comma-separated list of table name(s) and run each through this function

So, do you want a function that returns a string that contains all statuses for the tables in a list, or do you want the function to return separate statuses from separate invocations of the function?



Regardless, it seems to me that you need to build yourself your own SPLIT() function, which you can then use to drive invocations of dep_tbl_load(tbl_name varchar2), for example:

SELECT dep_tbl_load(SPLIT('HIS_TABLE,HER_TABLE,MY_TABLE,YOUR_TABLE',ROWNUM)
  FROM USER_TAB_COLUMNS
 WHERE ROWNUM <= 4
/

In the above example, I'm using "user_tab_columns" simply as a "driver table" that should always have enough rows to exceed your needs for "ROWNUM <= <whatever>".

If you use the above technique, you do not need to modify your "dep_tbl_load" function, plus you will have a nice, stand-alone SPLIT function.

Let us know your thoughts.



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,

Understand the confusion. And yes, I started writing a split function, but having a hard time with the logic flow - in fact was working on the flow chart as you were posting.

Anyway, the idea is that I'd send the string to one function dep_tbls that would serve as the splitter and reporter.

This dep_tbls function would walk through each table in the string and pass to the existing dep_tbl_load function. As it walked through each table if dep_tbl_load returned a bad load, it would stop and return a bad load indicator (be it char or number). After walking through all tables named in the string, it would then return a good load indicator.

So, the dep_tbls function would give me the status for all by running each table listed through the dep_tbl_load function.

Your thoughts?

Thanks,
Larry

 

[LarrySteele]

I found this page:

http://newsgroups.derkeiler.com/Archive/Comp/comp.databases.oracle.misc/2007-09/msg00315.html

That included this script:

FUNCTION split_text(text_ids IN VARCHAR2,
                    delim VARCHAR2,
                    return_nulls BOOLEAN) 
  RETURN varchar_tab
  PIPELINED IS

  vn_idx PLS_INTEGER;
  vc_text_ids VARCHAR2(32767) := text_ids;
  vc_next_string VARCHAR2(32767);

BEGIN
  LOOP
    vn_idx := instr(vc_text_ids, delim);
    IF vn_idx > 0 THEN
      vc_next_string := substr(vc_text_ids, 1, vn_idx - 1);

      IF vc_next_string IS NOT NULL OR return_nulls THEN
        PIPE ROW(vc_next_string);
      END IF;

      vc_text_ids := ltrim(substr(vc_text_ids, vn_idx + length(delim)));

    ELSE
      IF vc_text_ids IS NOT NULL OR return_nulls THEN
        PIPE ROW(vc_text_ids);
      END IF;
      EXIT;
    END IF;
  END LOOP;
RETURN;
END split_text;

This is VERY close to a method I had started working with, but was kinda writing off the cuff and while syntax was fine, my logic errors were causing a lot of grief.

I think this should give me enough of a jump off point to get the function I'm looking for. I'll let you know how it goes.

Thanks,
Larry

 

[LarrySteele]

Got it...

First function accepts a delimited string of table names and returns 1 if any dependent table fails to load, otherwise 0:

CREATE OR REPLACE function dep_tbl_loads(text_in in varchar2, delim varchar2 := ',') 
  return number
  is
  -- given a separated string list of tables, check their load status.  
  -- if *any* dependent table failed to load return 1, otherwise, return 0
  idx          number;
  cur_text      varchar2(32767) := text_in;
  next_string  varchar2(32767); 

begin
  loop
    idx := instr(cur_text, delim);

    if idx > 0 then
      -- separator exists, parse
      next_string := substr(cur_text, 1, idx - 1);

      if next_string is not null then
        -- check table load stat        
        if tbl_load_check(next_string) = 'N' then
          return 1;
        end if; 
      end if;

      -- get remaining tables from string
      cur_text := ltrim(substr(cur_text, idx + length(delim)));

    else
      if cur_text is not null then
        -- no separator, just table name remains, check load stat 
        if tbl_load_check(cur_text) = 'N' then
          return 1;
        end if; 
      end if;
      exit;

    end if;
  end loop;
  
  -- if we make it here, tables loaded okay
  return '0';
end dep_tbl_loads;
/

Second function, accepts a table name and returns its load stat:

CREATE OR REPLACE function tbl_load_check(tbl_name varchar2)
  return varchar2
  is
  -- given a table name, see if it was successfully loaded today
  -- return 'Y' if yes, otherwise 'N'
  v_recs            number;

begin

    select count(1)
    into v_recs
    from hrdm_load_stats
    where bad_load = 'N'
      and load_date = to_date(sysdate)
      and table_name = tbl_name;
    
    if v_recs = 1 then
      return 'Y';
    else
      return 'N';
    end if;

exception
  when others then
    dbms_output.put_line('Error in tbl_load_check function: ' || to_char(sqlcode) || '-' || sqlerrm);        
end;
/

I use it like this:

if dep_tbl_loads('HIS_TABLE,HER_TABLE,MY_TABLE,YOUR_TABLE') != 0 then
  -- one or more of the dependent tables failed to load
  -- error handling stuff goes here
  return;
end if;

Much happier with this method!

Larry

 

[carp]

For future reference, you might also want to look at the dbms_utility.comma_to_table procedure. It takes in a comma-separated list and converts it into a PL/SQL table. Very useful for this sort of thing.

 

[LarrySteele]

Carp,

Thanks for the idea. While it wouldn't serve my purpose, it's a great tip that might be good for a future project.

Larry

 

[taupirho]

The TABLE operator can be helpful in this situation also. To recap, TABLE takes its parameter e.g a comma separated list of values and makes it "look" like a table. See example below which shows general idea rather than anything really useful in itself.


1* select count(*) from tom01
SQL> /

COUNT(*)
----------
9
1* select count(*) from tom02
SQL> /

COUNT(*)
----------
19

1* select count(*) from tom03
SQL> /

COUNT(*)
----------
29

1 declare
2 tablist instrtab := instrtab('tom01','tom02','tom03');
3 nr_rows number;
4 begin
5 for i in (select column_value x from TABLE(tablist))
6 loop
7 execute immediate 'select count(*) from ' || i.x into nr_rows;
8 dbms_output.put_line (i.x || ' ' || nr_rows);
9 end loop;
10* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
tom01 9
tom02 19
tom03 29

PL/SQL procedure successfully completed.



In order to understand recursion, you must first understand recursion.

 

[taupirho]

In my previous answer the following has been omitted in error and needs to be run in sqlplus before everything else.

CREATE TYPE instrtab AS TABLE OF VARCHAR2(30)
/


In order to understand recursion, you must first understand recursion.