Example of bulk collect and performance using different values of limit

Sample code that shows using BULK COLLECT and performance of using different values for LIMIT. declare cursor l_cur is select * from scott.emp; type emp_tbl is table of l_cur%rowtype index by pls_integer; l_emp emp_tbl; limit_in number; i number; begin limit_in := &limit_param; open l_cur; loop fetch l_cur bulk collect into l_emp limit limit_in; for i... Continue Reading →

Advertisements

How to use PRAGMA EXCEPTION_INIT?

PRAGMA EXCEPTION_INIT - allows one to map ORA- error and it can be raised in PL/SQL code. The SQL Error number passed in "EXCEPTION_INIT" is the same as error code except for "NO_DATA_FOUND" ORA-01403 which is 100. See http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#BABGIIBI - Summary of Predefined PL/SQL Exceptions Example: declare no_rows_found exception; pragma exception_init(no_rows_found, 100); begin raise no_rows_found;... Continue Reading →

print_table utility procedure

Found this store procedure on Tom Kyte's site which can be used to print columns in a table row wise, click on the link to read the discussion on it. The store procedure takes 2 parameters (1) - SQL statement to be executed, (2) - Date format to printed Code: create or replace procedure print_table... Continue Reading →

Create a free website or blog at WordPress.com.

Up ↑