Oracle PL/SQL

The official way to achieve pagination in Oracle DB is not very elegant and very very slow. The reasons why it is slow are 2:The order by command must be…

Please see:Java-PL/SQL: Read from SYS_REFCURSOR

With pipeline functions we can "select" from a function as if it was a table, by fetching results as soon as they are prepared by the function, without waiting all…

Using XML transformation in a cursor in Oracle PL/SQL, we can fetch just one column of the recordset returned back by a SYS_REFCURSOR:declare    the_sys_refcursor               sys_Refcursor;     P_Return_Set_O                  sys_Refcursor;      p_column_name                  …

The procedure below helps in order to find a specific value in the tables declared at TABLES_SET cursor: CREATE procedure OLTP_SIA.find_specific_value (theString IN VARCHAR2) is match_count INTEGER; the_table     VARCHAR2(150); CURSOR…

The procedure below helps to find which record has text instead of number, so it produces "ORA-01722: invalid number" when you try to join with another number field. DECLAREEMPLID VARCHAR2(15);LAST_EMPLID…

With the procedure below you can find which columns are totally null, without data. It is useful, when you need an empty column e.g. for a new field: BEGIN DECLARE…

The following insert statement is used to fill an interface table from a remote database, using a dblink (tblCalls_v1@sqlserverdb). It commits every 500: BEGIN DECLARE CURSOR C1 IS SELECT "ContactID"…