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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]