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

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)

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

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 ObjectDim resultSet As ObjectSet 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 […]

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 ObjectPrivate Function GetConn_() As Object If m_conn Is Nothing Then Set m_conn = CreateObject(“ADODB.Connection”) End If Set GetConn_ = […]

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

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