Category Archives: PL/SQL

Example of bulk collect and performance using different values of limit

Posted on by
Reply

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 < limit_in;
end loop;
close l_cur;
end;

Limit value 1: 25.76 seconds
Limit value 10: 03.54 seconds
Limit value 100: 01.28 seconds
Limit value 1000: 00.95 seconds
Limit value 2000: 01.00 seconds
Limit value 3000: 01.03 seconds
Limit value 5000: 01.06 seconds
Limit value 10000: 01.20 seconds

As you notice in the case above as one increases the value of LIMIT after 1000, the execution time increases slightly. The following was timed using “set timing on” on SQL*Plus.

Advertisements

How to use PRAGMA EXCEPTION_INIT?

Posted on by
Reply

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 found
ORA-06512: at line 4

declare
too_many_rows exception;
pragma exception_init(too_many_rows, -1422);
begin
raise too_many_rows;
end;
/
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

print_table utility procedure

Posted on by
2

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')
-- this utility is designed to be installed ONCE in a database and used
-- by all. Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);

-- small inline procedure to restore the sessions state
-- we may have modified the cursor sharing and nls date format
-- session variables, this just restores them
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default, the
-- format mask I use includes that. In order to be “friendly”
-- we save the date current sessions date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;

execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;

-- to be bind variable friendly on this ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar, if not, set it so when we parse — literals
-- are replaced with binds
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;

-- parse and describe the query sent to us. we need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

-- define all columns to be cast to varchar2's, we
-- are just printing them out
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end if;
end loop;

-- execute the query, so we can fetch
l_status := dbms_sql.execute(l_theCursor);

-- loop and print out each column on a separate line
-- bear in mind that dbms_output only prints 255 characters/line
-- so we'll only see the first 200 characters by my design…
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end if;
end loop;
dbms_output.put_line( '—————–' );
end loop;

-- now, restore the session state, no matter what
restore;
exception
when others then
restore;
raise;
end;
/

Sample Output:
SQL> exec print_table('select * from dba_users where username = ''SYSTEM''');
USERNAME : SYSTEM
USER_ID : 5
PASSWORD : D4DF7931AB130E37
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE :
DEFAULT_TABLESPACE : SYSTEM
TEMPORARY_TABLESPACE : TEMP
CREATED : 12-aug-2009 21:12:59
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : SYS_GROUP
EXTERNAL_NAME :
—————–

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

Posted on by
Reply

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 all_objects with dba_objects and all_source with dba_source.

grant select on sys.dba_source to ;
create synonym .all_source for sys.dba_source;

grant select on sys.dba_objects to ;
create synonym .all_objects for sys.dba_objects;