Tag Archives: oracle

Oracle: on Overloading and Hiding Constructors

From Oracle 12c documentation:

You can overload user-defined constructors, like other type methods.

User-defined constructors are not inherited, so a user-defined constructor defined in a supertype cannot be hidden in a subtype. However, a user-defined constructor does hide, and thus supersede, the attribute-value constructor for its type if the signature of the user-defined constructor exactly matches the signature of the attribute-value constructor. For the signatures to match, the names and types of the parameters (after the implicit SELF parameter) of the user-defined constructor must be the same as the names and types of the attributes of the type. The mode of the parameters (after the implicit SELF parameter) of the user-defined constructor must be IN.

If an attribute-value constructor is not hidden by a user-defined constructor that has the same name and signature, the attribute-value constructor can still be called.

Note that, if you evolve a type—for example, by adding an attribute—the signature of the attribute-value constructor of the type changes accordingly. This can cause a formerly hidden attribute-value constructor to become usable again.

ora_te: On further development

Hi, we are talking about ora_te again.
I’ve finally decided to implement IF construct, but I want to do it in a slightly different way I wanted earlier. Next release of ora_te will feature new syntax for LOOP construct and a an implementation of IF construct in a form very similar to twig template engine.
It will greatly improve readability of templates.
Plus such things as concatenation by new line and carriage return could be directly archived by writing actual new line in the template.
Compare

{% for cur:1 |  , %}t1.{{column_name}} = t2.{{column_name}}
{% endfor %}

to

{% for cur:1 |, %}t1.{{column_name}} = t2.{{column_name}}{% endfor %}

First of given templates will naturally insert new line after each loop substitution, while second one will produce one line.
If construct will also be implemented the twig way.

{% if ('{{column_name}}' = 'dummy' ) %}
  gotcha
{% else %}
  nope
{% endif %}

Sure old syntax will not be deprecated.
Rewriting a merge example with a new syntax will result in something like

'merge into {{dest_table}} t1
using {{tmp_table}} t2
on (t1.{{join_by}} = t2.{{join_by}} ) 
when matched then 
  update set {% for cur:1 |  , |n|?|%}t1.?1 = t2.?1 
{% endfor %}
delete where t2.status_code = ''D''
when not matched then insert( 
  {{dwh_pk}}
  , {% for cur:2 |,%} t1.{{column_name}} = t2.{{column_name}} 
{% endfor %} 
) values ( 
  {{sequence_name}}.nextval 
  , {% for cur:2 |,%}t2.{{column_name}}
{% endfor %} 
) 
where t2.status_code <> ''D''';

Oracle: Regexp_Replace respecting word boundaries

Sometimes you just want to preserve word boundaries while using Oracle regexp_replace, or to replace the whole word only, not substrings within other words. Pity, but Oracle regexp syntax does not understand perl’s ‘\b’ (word boundary). So the workaround is to use ‘\W’ (not word). Here how I do it:

regexp_replace( 
    haystack
    , '(\W|^)(' || what_to_replace || ')(\W|$)'
    , '\1' || with_what || '\3'
    , 1
    , 0
    , 'imn'
)

Credits go to SQL.ru forum

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)

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!

VBA: Run-time error 3001 Arguments Are Of The Wrong Type… when setting ADODB.Command object members

This forum post saved my day.
I was trying to run Oracle stored procedure with output parameters from Excel VBA with the following piece of code:


Dim cmd As Object
Dim resultSet As Object
Set cmd = CreateObject("ADODB.Command")
With cmd
.CommandText = "PK_AUTH.LOGON"
.NamedParameters = True
.Parameters.Append .CreateParameter("login", adVarChar, adParamInput, 50, login_)
.Parameters.Append .CreateParameter("pass", adVarChar, adParamInput, 50, pass_)
.Parameters.Append .CreateParameter("ldb", adVarChar, adParamOutput, 50)
.Parameters.Append .CreateParameter("pdb", adVarChar, adParamOutput, 50)
.CommandType = adCmdStoredProc
.ActiveConnection = GetConn_()
Set resultSet = .Execute
ldb_ = .Parameters.Item("ldb")
End With

And I always got Run-time error 3001 ‘Arguments Are Of The Wrong Type, Are Out Of The Acceptable Range, or are in conflict with one another’ upon invocation of

.CommandType = adCmdStoredProc

or

.Parameters.Append .CreateParameter(...)

no matter which statement I placed first.
After fighting for a while I found this post that stated that error is fired because of late binding of library references, so VB simply did not know of adCmdStoredProc and other constants
That meant that this error has nothing to do with ADODB or Ole or, I just said that it does not know the value of constant. Not very informative in fact…
So, I simply added


Const adVarChar As Long = 200
Const adParamInput As Long = &H1
Const adParamOutput As Long = &H2
Const adCmdStoredProc As Long = &H4

to the Sub header and everything worked fine. Constants are defined in c:Program FilesCommon FilesSystemadoadovbs.inc

VBA: Prototype Class than connects to oracle and checks if connection is up. Plus VBA singleton pattern

Here some prototyping code of a class that is able to connect to Oracle through OleDB and to check if the connection is up before doing some application logic. Class name is TUploadHelper.


Private m_conn As Object

Private Function GetConn_() As Object
If m_conn Is Nothing Then
Set m_conn = CreateObject("ADODB.Connection")
End If
Set GetConn_ = m_conn
End Function

Private Function Connected_() As Boolean
Dim recordSet As Object
Dim value As Long
Dim errCode As Variant
Dim errMsg As Variant

If Not PopErrors() Then On Error GoTo L_ERR_HANDLER

value = 0
Connected_ = False

Set recordSet = CreateObject("ADODB.Recordset")

Set recordSet.ActiveConnection = GetConn_()

recordSet.Open "select 1 as value_ from dual"
While Not recordSet.EOF
value = recordSet.Fields(0)
If 1 = value Then
Connected_ = True
GoTo L_CLEANUP
End If
Wend
' не вернулось ни одной записи
L_ERR_HANDLER:
errCode = Err.Number
errMsg = Err.Description
L_CLEANUP:
On Error GoTo 0
If 1 = recordSet.State Then
recordSet.Close
End If
Set recordSet = Nothing
End Function


Private Sub Class_Deinitialize()
If Not m_conn Is Nothing Then
If 1 = m_conn.State Then
m_conn.Close
End If
Set m_conn = Nothing
End If
End Sub

Private Function Authorized_() As Boolean
Authorized_ = False
End Function

Private Sub Connect_()
' Dim state_ As Variant
m_conn.Open "Provider=OraOLEDB.Oracle;Data Source=your_server.world;User ID=your_user;Password=your_pass;PLSQLRSet=1;"
' state_ = m_conn.State
End Sub

Public Sub Ut()
If Not Connected_ Then
Connect_
If Not Connected_ Then
End If
End If
End Sub

And here is module code that implements singleton pattern for TUploadHelper object.


'Singleton pattern
Private g_uploadHelper As TUploadHelper
Public Property Get GetUploadHelper() As TUploadHelper
If g_uploadHelper Is Nothing Then
Set g_uploadHelper = New TUploadHelper
End If
Set GetUploadHelper = g_uploadHelper
End Property

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

On database link naming

Name dblinks as a site it refers.
For example name it ‘subdomain.somesite.com’ and then create synonyms to objects on the site:


create synonym some_table for some_table@subdomain.somesite.com

Doing so you help other developers to decipher the site particular database link refers to without consulting all_db_links system view.

Oracle SQL selects to get familiar with table naming standard and to find junk tables in scheme

Run following selects to discover prefix and suffix hierarchies and to get count of particular prefix/suffix occurrences. That will help you become familiar with naming standard and to find suspicious prefixes/suffixes that belong to tables that probably store junk data.

with prefix_generator( prefix, p_prefix, object_name, lvl ) as (
select regexp_substr( object_name, '.*?_' ) prefix
, null as p_prefix
, object_name as object_name
, 1 as lvl
from (
select table_name as object_name
from all_tables
where owner like 'YOUR_OWNER'
and temporary like 'N'
) q0
union all
select g.prefix || regexp_substr(
substr( g.object_name, length( g.prefix ) + 1 ), '.*?_'
) as prefix
, g.prefix as p_prefix
, g.object_name
, g.lvl + 1
from prefix_generator g
where regexp_substr(
substr( g.object_name, length( g.prefix ) + 1 ), '.*?_'
) is not null
)
, groupped_generator as (
select prefix,
p_prefix
from prefix_generator
group by prefix,
p_prefix
)
, forward_builder(
prefix
, lvl
, padded_prefix
) as (
select g.prefix
, 0 as lvl
, g.prefix padded_prefix
from groupped_generator g
where g.p_prefix is null
group by g.prefix
union all
select n.prefix
, p.lvl + 1 as lvl
, lpad( ' ', ( p.lvl + 1 ) * 4 ) || n.prefix padded_prefix
from groupped_generator n
join forward_builder p
on p.prefix = n.p_prefix
) search depth first by prefix set order_by
, count_by_prefix as (
select prefix
, count( * ) cnt
from prefix_generator g
group by prefix
)
select b.padded_prefix
, c.cnt
from forward_builder b
join count_by_prefix c
on c.prefix = b.prefix
where 2 < c.cnt
order by order_by
;/

with postfix_generator( postfix, p_postfix, object_name, lvl ) as (
select regexp_substr( object_name, '_.*' ) postfix
, object_name as p_postfix
, object_name as object_name
, 1 as lvl
from (
select table_name as object_name
from all_tables
where owner like 'YOUR_OWNER'
and temporary like 'N'
) q0
union all
select regexp_substr(
substr( g.postfix, 2 ), '_.*'
) as postfix
, g.postfix as p_postfix
, g.object_name
, g.lvl + 1
from postfix_generator g
where g.postfix is not null -- recursion breaker
)
, groupped_generator as (
select postfix,
p_postfix
from postfix_generator
where p_postfix <> object_name
group by postfix,
p_postfix

)
, backward_builder(
p_postfix
, lvl
, padded_p_postfix
) as (
select g.p_postfix
, 0 as lvl
, g.p_postfix padded_p_postfix
from groupped_generator g
where g.postfix is null
group by g.p_postfix
union all
select n.p_postfix
, p.lvl + 1 as lvl
, lpad( ' ', ( p.lvl + 1 ) * 4 ) || n.p_postfix padded_p_postfix
from groupped_generator n
join backward_builder p
on p.p_postfix = n.postfix
) search depth first by p_postfix set order_by
, count_by_postfix as (
select postfix
, count( * ) cnt
from postfix_generator g
where postfix is not null
group by postfix
)
select b.padded_p_postfix
, c.cnt
from backward_builder b
join count_by_postfix c
on c.postfix = b.p_postfix
where 2 < c.cnt
order by b.order_by
;/

To select all prefixes and suffixes (not only those having more than two occurrences) delete ‘where 2 < c.cnt’ clause.