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…
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;…
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…
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…
