Tag Archives: plsql

Oracle: Iterate over regexp matches with hierarchical query trick

begin
  for matches in ( 
    with in_data as (
      select 'v113*(v43|v42|v900)/v54' haystack
        , 'v\d+' needle
      from dual
    )
    , matches ( a_match, occ ) as ( 
      select regexp_substr( haystack, needle, 1, 1 ) a_match
        , 1 occ
      from in_data
      union all
      select regexp_substr( haystack, needle, 1, p.occ+1 ) a_match
        , p.occ + 1 as occ
      from matches p
      cross join in_data
      where p.a_match is not null
    )
    cycle a_match set cycle to 1 default 0
    select a_match
    from matches
    where a_match is not null
  ) loop
    dbms_output.put_line( matches.a_match );
  end loop;
end;

PL/SQL: effectively reusing table of object type in PL/SQL

Just follow the example code to get the feature.

declare
  v_tbl ty_varchar2_tbl; -- table of varchar2
  v_stmt clob;
  g_te_merge_te ty_te;
begin
  -- here we fill our table one time
  select column_value
  bulk collect into v_tbl 
  from table( pk_utils.vchar2_to_vchar2_lines( v_sd.composite_key, ',' ) )
  ;
  -- some code follows
  -- ...
  -- don't try to get intricacies of calling pk_te
  -- just mention the usage of v_tbl two times with casting it to SQL-level type
  select pk_te.substitute( 
      g_te_merge_det
      , ty_m(
        ty_p( 'dest_tbl', v_sd.dest_table )
        , ty_p( 'tmp_tbl', v_sd.tmp_table )
      ) 
      , cursor ( 
        select ty_m( ty_p( 'column_name', uc1.column_name ) )
        from user_tab_columns uc1
        where uc1.table_name like v_sd.dest_table
          and uc1.column_name not in ( select t.column_value from table( cast ( v_tbl as TY_VARCHAR2_TBL ) ) t )
      )
      , cursor ( 
        select ty_m( ty_p( 'comp_key', t.column_value ) )
        from table( cast ( v_tbl as TY_VARCHAR2_TBL ) ) t
      )
    )
  into v_stmt 
  from dual
  ;

end;

PL/SQL: What exactly to log when exception is raised

To catch the exact error message and the line that raised it insert the following into your log table.

trim( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ) 
    || chr(13) || chr(10) || trim( DBMS_UTILITY.FORMAT_CALL_STACK )  
    || chr(13) || chr(10) || trim( DBMS_UTILITY.FORMAT_ERROR_STACK )

It will help you to determine the culprit even if an exception was thrown from anonymous block inside dynamic call.

PL/SQL: Procedure to copy package with another name

Here’s simple procedure build upon custom listagg implementation (see Custom listagg function using cursor to bypass ORA-01489) that creates a copy of existing package with a new name.
It comes very handy when you have several versions of package (say production and development) and constantly switch between them.
It simply obtains package definition and body and then recreates them with a new name.

procedure cp_package( a_owner in varchar2, a_name in varchar2, a_new_name in varchar2 ) as 
  v_head clob;
  v_body clob;
begin
  select regexp_replace( 
    pk_utils.listagg_clob( 
        cursor (
          select text
          from all_source
          where name like upper( a_name ) 
            and owner like upper( a_owner )
            and type like 'PACKAGE'
          order by line
        )
        , ''
      )
      , a_name
      , a_new_name
      , 1
      , 0
      , 'imn'
    )
    , regexp_replace( 
    pk_utils.listagg_clob( 
        cursor (
          select text
          from all_source
          where name like upper( a_name ) 
            and owner like upper( a_owner )
            and type like 'PACKAGE BODY'
          order by line
        )
        , ''
      )
      , a_name
      , a_new_name
      , 1
      , 0
      , 'imn'
    )
  into v_head
    , v_body
  from dual
  ;

  execute immediate 'create or replace ' || v_head;  
  execute immediate 'create or replace ' || v_body;  
  
  dbms_output.put_line( 'Successfully copied package ' || upper( a_name ) || ' to ' || upper( a_new_name ) );
end;

SQLDeveloper: Handy user report to generate column names and data types for arbitrary query

I’ve already covered the tough question on converting arbitrary query to clob delimited text (Take 1 and Take 2). The resulting function used a code snippet that produced a list of column names and their datatypes in it’s intestines. Based on that snippet here’s a SQLDeveloper user reports that produces a list of column names and their datatypes for arbitrary query. A result of a report is ready to be used in create (temporary) table statement or insert statement (for target columns) and in many other ways.
Here’s the text of a report:

/* http://stackoverflow.com/questions/6544922/column-names-in-an-empty-oracle-ref-cursor */
DECLARE
  v_ref_cur SYS_REFCURSOR;
  v_cur_handle NUMBER;
  v_count NUMBER;
  v_desc_tab dbms_sql.desc_tab;
  v_statement clob := :a_statement;
  v_print_data_type pls_integer := :a_print_data_type;
  DELIMITER constant varchar2( 5 char ) := chr( 13 ) || chr( 10 ) || chr( 9 ) || ', ';
  PROCEDURE print_desc_tab( a_desc_tab IN sys.dbms_sql.desc_tab, a_print_data_type in pls_integer ) as
    v_data_type VARCHAR2(30);
    v_delimiter varchar2( 30 char ) := '';  
  BEGIN
    dbms_output.put_line( '<pre>' );
    FOR i IN 1 .. a_desc_tab.count LOOP
      SELECT DECODE( to_char( a_desc_tab( i ).col_type ), 1, 'VARCHAR2', 2, 'NUMBER', 12, 'DATE' )
      INTO v_data_type
      FROM dual
      ;
      dbms_output.put( v_delimiter || a_desc_tab( i ).col_name );
      if ( 1 = a_print_data_type ) then
        dbms_output.put( ' ' || v_data_type);  
        case a_desc_tab( i ).col_type
          when 1 then
            dbms_output.put( '(' || to_char( a_desc_tab( i ).col_max_len ) || ' char)' );  
          when 2 then
            if ( 0 != a_desc_tab( i ).col_precision ) then
              dbms_output.put( 
                '(' || to_char( a_desc_tab( i ).col_precision ) || ', ' || to_char( a_desc_tab( i ).col_scale ) || ')'  
              );  
            end if;
          else
          
            null;
        end case;
      end if;
      v_delimiter := DELIMITER;
    END LOOP;
    dbms_output.new_line;
    dbms_output.put_line( '</pre>' );
  END print_desc_tab;
  
BEGIN
  OPEN v_ref_cur FOR v_statement;

  v_cur_handle := dbms_sql.to_cursor_number( v_ref_cur );
  dbms_sql.describe_columns( v_cur_handle, v_count, v_desc_tab );
  
  print_desc_tab( v_desc_tab, v_print_data_type );
  dbms_sql.close_cursor( v_cur_handle );
END;

And here’s the link for user report (hosted on Google drive). Once downloaded it can be imported into SQLDeveloper.

PL/SQL: Generic function for converting arbitrary cursor to clob (delimited text). Take two

Addressing large dataset issue that was mentioned for previously developed cursor to clob conversion functions (see previous post) a new version of a function that converts any query into delimited text was born.
Not so elegant nevertheless it handles nulls (thanks to this StackOverflow topic) and large datasets (that still needs approval).
If you fill able, you can try to convert this procedure into highly optimized pipelined function making use of bulk operations, though I failed due to some bug producing ORA-24374 while working with xml and multisets (maybe I’ll cover this bug in the following posts).
So by now the following procedure just inserts clob values into given table

Here are prerequisites:


/* ty_int_to_VCHAR2_row ty_int_to_VCHAR2_tbl type declarations ...*/
create or replace
type TY_INT_TO_VCHAR2_ROW force as object (
int ingeger
, vchar varchar2( 4000 char )
, CONSTRUCTOR FUNCTION TY_INT_TO_VCHAR2_ROW( SELF IN OUT NOCOPY TY_INT_TO_VCHAR2_ROW, a_int in pls_integer, a_vchar in varchar2 )
RETURN SELF AS RESULT
);
/

create or replace
type body TY_INT_TO_VCHAR2_ROW as

constructor function TY_INT_TO_VCHAR2_ROW ( SELF IN OUT NOCOPY TY_INT_TO_VCHAR2_ROW , a_int in pls_integer, a_vchar in varchar2 )
return self as result
as
begin
int := a_int;
vchar := a_vchar;
return;
end TY_INT_TO_VCHAR2_ROW ;

end;
/

create or replace type TY_INT_TO_VCHAR2_TBL force as table of TY_INT_TO_VCHAR2_ROW;
/

create global temporary table tmp_pk_utils_xml_result_set(
row_num integer, name_ varchar2( 30 char ), value_ varchar2( 4000 char )
) on commit preserve rows;
/

And here’s the code:


procedure query_to_flat(
a_query in clob, a_delimiter in varchar2, a_line_break in varchar2, a_target_table in varchar2
)
as
v_prev_rn pls_integer := 0;
v_clob clob;
v_cur_desc sys_refcursor;
v_desc_tab dbms_sql.desc_tab;
v_count number;
v_cur_handle NUMBER;

v_cur sys_refcursor;

v_column_names_tbl TY_INT_TO_VCHAR2_TBL := TY_INT_TO_VCHAR2_TBL();
v_truncate_result pls_integer;
v_truncate_message varchar2( 32767 char );
begin
$if ( pk_utils_cc.dbg = 1 ) $then
dbms_output.enable( null );
$end
-- read column description into v_desc_tab
open v_cur_desc for 'select * from ( ' || a_query || ' ) where 1=0';

v_cur_handle := dbms_sql.to_cursor_number( v_cur_desc );
-- we'll get all columns and their names
dbms_sql.describe_columns( v_cur_handle, v_count, v_desc_tab );

if v_cur_desc%ISOPEN then
close v_cur_desc;
end if;
-- move column descripition data into sql-level table type, so we can use it in queries
v_column_names_tbl.extend( v_desc_tab.count );
for v_column_idx in 1 .. v_desc_tab.count loop
v_column_names_tbl( v_column_idx ) :=
ty_int_to_vchar2_row( v_column_idx, v_desc_tab( v_column_idx ).col_name )
;
end loop;

open v_cur for a_query;

-- just a truncate that retries if resource is busy, not necessary here, but it's an attitude
pk_safe_ddl.safe_truncate( 'MY_SCHEMA', 'TMP_PK_UTILS_XML_RESULT_SET' );

-- insert resultset into temporary table, here we'll got only not null columns
insert into tmp_pk_utils_xml_result_set( row_num, name_, value_ )
SELECT t.rn /*+ NO_XML_QUERY_REWRITE */
, t2.COLUMN_VALUE.getrootelement() NAME
, EXTRACTVALUE ( t2.COLUMN_VALUE, 'node()' ) VALUE
FROM ( select /*+ NO_XML_QUERY_REWRITE */ rownum rn, column_value from TABLE ( XMLSEQUENCE ( v_cur ) ) ) t
, TABLE ( XMLSEQUENCE ( EXTRACT ( t.COLUMN_VALUE, '/ROW/node()' ) ) ) t2
;
-- first cross join to get all column names for every row in resulting dataset
-- then join with temporary table that stores actual values
for cur_val in (
select a.row_num
, a.name_
, q.value_
from (
select r.row_num
, c.int as col_idx
, c.vchar as name_
from (
select row_num
from tmp_pk_utils_xml_result_set
group by row_num
) r
cross join table( cast( v_column_names_tbl as TY_INT_TO_VCHAR2_TBL ) ) c
) a
left outer join (
select row_num
, name_
, value_
from tmp_pk_utils_xml_result_set
where name_ is not null
) q
on a.row_num = q.row_num
and a.name_ = q.name_
order by a.row_num
, a.col_idx
) loop
-- some kind of listagg functionality but for potentially long lines (clobs)
if ( 0 = v_prev_rn ) then
v_clob := cur_val.value_;
else
if ( v_prev_rn != cur_val.row_num ) then
v_clob := v_clob || a_line_break;
execute immediate 'insert into ' || a_target_table || ' values( :v_clob )'
using v_clob
;
$if ( pk_utils_cc.dbg = 1 ) $then
dbms_output.put_line( v_clob );
$end
v_clob := cur_val.value_;
else
v_clob := v_clob || a_delimiter || cur_val.value_;
end if;
end if;
v_prev_rn := cur_val.row_num;
END LOOP;

execute immediate 'insert into ' || a_target_table || ' values( :v_clob )'
using v_clob
;
$if ( pk_utils_cc.dbg = 1 ) $then
dbms_output.put_line( v_clob );
$end
if v_cur%ISOPEN then
close v_cur;
end if;
commit;
end;

PL/SQL: Generic function for converting arbitrary cursor to clob (delimited text)

Thanks to a couple of posts (namely TO_CHAR of an Oracle PL/SQL TABLE type and SQL*Plus tips. #2) I was able to develop very convenient and what’s more important generic function for converting arbitrary cursor to clob lines or say any result set to clob lines.
Such function will be very useful for unloading results of an arbitrary query to delimited flat files.


function cursor_to_flat( a_cur in sys_refcursor, a_delimiter in varchar2, a_line_break in varchar2 )
return ty_clob_tbl pipelined
as
v_prev_rn pls_integer := 0;
v_clob clob;
begin
FOR cur_val IN (
SELECT t.rn
, EXTRACTVALUE ( t2.COLUMN_VALUE, 'node()' ) VALUE
FROM ( select rownum rn, column_value from TABLE ( XMLSEQUENCE ( a_cur ) ) ) t
, TABLE (XMLSEQUENCE ( EXTRACT ( t.COLUMN_VALUE, '/ROW/node()' ) ) ) t2
order by 1
) LOOP
if ( 0 = v_prev_rn ) then
v_clob := cur_val.value;
else
if ( v_prev_rn != cur_val.rn ) then
v_clob := v_clob || a_line_break;
pipe row ( v_clob );
v_clob := cur_val.value;
else
v_clob := v_clob || a_delimiter || cur_val.value;
end if;
end if;
v_prev_rn := cur_val.rn;
END LOOP;
pipe row ( v_clob );
end;

Unit test code:


select *
from table(
pk_utils.cursor_to_flat(
cursor(
select 1, 2, 3 from dual
union all
select 4, 5, 6 from dual
)
)
);

NB! One problem with this approach is that if you have NULLs in your result set, then they will be completely skipped (not wrapped with delimiters). Now I’m trying to solve it.

Edit
The author of SQL*Plus tips. #2 was totally awesome pointing out how to handle NULLs. Resulting function takes query as clob, but one can easily rollout version with sys_refcursor, as dbms_xmlgen.newcontext will accept it.


function query_to_flat( a_query in clob, a_delimiter in varchar2, a_line_break in varchar2 )
return ty_clob_tbl pipelined
as
v_prev_rn pls_integer := 0;
v_clob clob;
v_cur sys_refcursor;
v_xml clob;
v_context dbms_xmlgen.ctxtype;
begin
v_context := dbms_xmlgen.newcontext( a_query );
dbms_xmlgen.setnullhandling( v_context, dbms_xmlgen.empty_tag );
v_xml := dbms_xmlgen.getxml( v_context );
dbms_xmlgen.closecontext( v_context );

for cur_val in (
select row_num
, col_value
from xmltable( --<<<ML
'(#ora:view_on_null empty #){
for $a at $i in /ROWSET/ROW
, $r in $a/*
return element ROW{
element ROW_NUM{$i}
, element COL_VALUE{$r/text()}
}
}'
--ML;
passing xmltype(v_xml)
columns
row_num int
, col_value varchar2(100)
)
) loop
if ( 0 = v_prev_rn ) then
v_clob := cur_val.col_value;
else
if ( v_prev_rn != cur_val.row_num ) then
v_clob := v_clob || a_line_break;
pipe row ( v_clob );
v_clob := cur_val.col_value;
else
v_clob := v_clob || a_delimiter || cur_val.col_value;
end if;
end if;
v_prev_rn := cur_val.row_num;
END LOOP;
pipe row ( v_clob );
end;

NB!This solution is still has some drawbacks for large dataset as all data is placed into variable, consuming PGA.
I’ll try to address this issue in the following post.
Edit
And here’s the followup post!

Custom listagg function using cursor to bypass ORA-01489

When using build-in listagg function one should always consider that resulting string must have a length less or equal to 4000 characters. If does exceed then an error ‘SQL Error: ORA-01489: result of string concatenation is too long’ is thrown.
Here’s a package implementing a custom aggregate function that bypasses this restriction.


create or replace PACKAGE "PK_UTILS" AS

type ref_cur is ref cursor;

function LISTAGG_CLOB( a_cur in pk_utils.ref_cur, a_delimiter in varchar2 ) return clob;

END PK_UTILS;
/
create or replace PACKAGE BODY "PK_UTILS" AS

function listagg_clob( a_cur in pk_utils.ref_cur, a_delimiter in varchar2 ) return clob
as
v_single_value clob;
v_result clob;
begin
fetch a_cur into v_single_value;
if ( a_cur%NOTFOUND ) then
goto FIN;
end if;
v_result := v_single_value;
loop
fetch a_cur into v_single_value;
exit when a_cur%NOTFOUND;
v_result := v_result || a_delimiter || v_single_value;
end loop;
<<FIN>>
return v_result;
end;

END PK_UTILS;
/

And here is a small user test emulating ‘within group (order by …)’ functionality.


with prepared as (
select 1 a, 1 b from dual
union all
select 2 a, 2 b from dual
union all
select 3 a, 1 b from dual
union all
select 4 a, 2 b from dual
)
select q.b
, pk_utils.listagg_clob( cursor( select a from prepared where b = q.b order by a desc ) , ',' )
from (
select b
from prepared
group by b
) q
;/

B LISTAGG_
---------- ------------------
1 3,1
2 4,2

Useful onliner to make testing in Oracle SQL*Developer a little bit easier

This little script helps to test subroutines using SQL*Developer Run or Debug command.  Just copy code from “Run PL/SQL” window and run the following onliner in a terminal. It uncomments legacy dbms_ouput code and extends capacity of varchar2 variables from 200 to 32767 chars. Now paste clipboard contents back into SQL Worksheet and run.

cat /dev/clipboard | perl -p -e 's//* Legacy output://g; s/*///g; s/VARCHAR2(200)/VARCHAR2( 32767 char )/g;s/^s+:.*$//g' > /dev/clipboard