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 Files\Common Files\System\ado\adovbs.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.

Highlight empty tables in PowerDesigner model with VB Script

The wonderful thing about PowerDesigner automation is that you can actually connect to database from VB Script to get some crucial information that is unavailable (or was not captured) at a reverse engineering phase.
The following script queries Oracle ALL_TABLES system view to find out if table in the model stores any data. Then those tables that are empty are highlighted with thick maroon frame.

Reverse engineering Oracle 11gR2 scheme with Sybase PowerDesigner 12.5 on Windows 7 x64

The following setup was made to perform reverse engineering of Oracle 11gR2 scheme into physical diagram using Sybase PowerDesigner 12.5 on Windows 7 x64.

ConnectionType: JDBC
DBMS type: Oracle
User name: <your scheme name>
Password: <your password>
JDBC driver class: oracle.jdbc.driver.OracleDriver
JDBC connection URL: jdbc:oracle:thin:@//<your host>:<your port>/<your service name>
JDBC driver jar files: c:\Oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar

Plus had to install 32-bit Java and add edit path variable in batch file to make PowerDesigner use appropriate VM:

set path=C:\Program Files (x86)\Java\jre7\bin\client\;%path%
cd "c:\Program Files (x86)\Sybase\PowerDesigner 12\"
pdshell12.exe

Many thanks to Devtype blog and Jan Bartos' answer on google groups.

Thing to remember

Since rows can migrate from location-to-location when they are updated ROWID should never be stored an never be counted on to be the same in any database.

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

Oracle: Case insensitive regexp search through all sources

select *
from all_source
where regexp_like( text, regexp_search_string, 'i' )