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 in 1..l_emp.count loop null; end loop; exit when l_emp.count < … Continue reading Example of bulk collect and performance using different values of limit

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; end; / * ERROR at line 1: ORA-01403: no data … Continue reading How to use PRAGMA EXCEPTION_INIT?

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 ( p_query in varchar2, p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss') … Continue reading print_table utility procedure

Grant access to source (package, procedure, function) without granting execute access

Depending on tool one can grant access to users if they need access to look at code for store procedure, function and package without giving the user execute access. For AquaStudio for example one can set up user with following access which would allow to look at the code, the access needs to be granted by sys. In this setup you are replace the view … Continue reading Grant access to source (package, procedure, function) without granting execute access