Dev PL/SQL? Nesta sessão veremos alguns truques e ferramentas que podem mudar a sua vida. Pra melhor! 🙂
Quando: Terça-feira – 13/04/2021 20:00 BRT
Onde: https://www.youtube.com/c/GUOBOficial
Realização: GUOB
Palestrante: Matheus Boesing
Tag: plsql
Disabling PL/SQL Warnings
Hi all!
So, the DBA keep insisting that the Procedure need to compile without warnings? Easy!
This is actually a nice option if you are compiling a code in a client and don’t want to show that your code has warnings, which is kind of ok, once it’s almost impossible to code without warnings.
And this is not even new. Have a look on this documentation from 10.2.
Ok, so how to do it?
ALTER SESSION SET plsql_warnings = 'disable:all';
Have a look in the example below:
SQL> CREATE OR REPLACE PROCEDURE plw5001
2 IS
3 a BOOLEAN;
4 a PLS_INTEGER;
5 BEGIN
6 a := 1;
7 DBMS_OUTPUT.put_line ('Will not compile?');
8 END plw5001;
9 /
Warning: Procedure created with compilation errors.
SQL>
SQL> SHOW ERRORS
Errors for PROCEDURE PLW5001:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4 PLW-05001: previous use of 'A' (at line 3) conflicts with this
use
6/4 PL/SQL: Statement ignored
6/4 PLS-00371: at most one declaration for 'A' is permitted
SQL>
SQL> ALTER SESSION SET plsql_warnings = 'disable:all';
Session altered.
SQL>
SQL> CREATE OR REPLACE PROCEDURE plw5001
2 IS
3 a BOOLEAN;
4 a PLS_INTEGER;
5 BEGIN
6 DBMS_OUTPUT.put_line ('Will not compile?');
7 END plw5001;
8 /
Procedure created.
Auditing Logons with Triggers
Hello all!
So, I was attending a client who wanted to audit logon on database without Advanced Security pack. Complicated? Not at all.
Performatic? eehh, not really the best option, but Oracle is not really good for opening sessions anyway (this is why we have connection pools), so I consider this not bad…
Ok, how to do it?
A sequence for ID control:
create sequence sys.logon_capture_seq;
A Table for logon logging:
create table sys.logon_capture ( id number, capture_time date, authenticated_identity varchar2(30), authentication_method varchar2(30), identification_type varchar2(30), network_protocol varchar2(30), session_user varchar2(30), os_user varchar2(30), host varchar2(30), ip_address varchar2(30), program varchar2(30), module varchar2(30), action varchar2(30), service_name varchar2(30)) tablespace logon_capture;
* Here is an important point: Be always sure to have enough space on this tablespace, otherwise all new connections can be frozen.
Create the logon trigger:
create or replace trigger SYS.trg_capture_logons
after logon on database
when (SYS_CONTEXT ('USERENV', 'SESSION_USER') not in ('SYS'))
begin
insert into sys.logon_capture (id,capture_time,authenticated_identity,authentication_method,identification_type,network_protocol,session_user,os_user,host,ip_address,program,module,action,service_name)
select
sys.logon_capture_seq.nextval,
sysdate,
substr(sys_context('userenv','authenticated_identity'),1,30),
substr(sys_context('userenv','authentication_method'),1,30),
substr(sys_context('userenv','identification_type'),1,30),
substr(sys_context('userenv','network_protocol'),1,30),
substr(sys_context('userenv','session_user'),1,30),
substr(sys_context('userenv','os_user'),1,30),
substr(sys_context('userenv','host'),1,30),
substr(sys_context('userenv','ip_address'),1,30),
substr(program,1,30),
substr(sys_context('userenv','module'),1,30),
substr(sys_context('userenv','action'),1,30),
substr(sys_context('userenv','service_name'),1,30)
from v$session
where sid = sys_context('userenv','sid');
commit;
exception
when others then null;
end;
/
Know what is a good idea? a cleanup job, keeping only the last 6 month of “audit” data:
begin DBMS_SCHEDULER.CREATE_JOB ( job_name => 'SYS.PURGE_LOGON_CAPTURE', job_type => 'PLSQL_BLOCK', job_action => 'begin delete from sys.logon_capture where capture_time < add_months(sysdate, -6); commit; end;', number_of_arguments => 0, start_date => trunc(sysdate+1) + 23/24, repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=40;BYSECOND=0', enabled => false, auto_drop => false, comments => ''); end; / exec DBMS_SCHEDULER.ENABLE ( name => 'SYS.PURGE_LOGON_CAPTURE' ); select owner, enabled from dba_scheduler_jobs where job_name = 'PURGE_LOGON_CAPTURE'
Hope it helps you!
PL/Scope – Did you know that?
Hi All!
I decided to make a serie of posts with really quick notes about some really awesome features we simply don’t use. Today’s one is about PL/Scope.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
PL/Scope
PL/Scope is a tool that gathers information about user defined identifiers at compile time. Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of “IDENTIFIERS:NONE”. To enable collection, switch value to “IDENTIFIERS:ALL”. The data is stored in the SYSAUX tablespace, this space needs to be well planned and managed..
The PL/Scope data is available from the %_IDENTIFIERS views. The following query displays data gathered during the compilation of a test procedure:
COLUMN name FORMAT A30
SELECT LPAD(' ', level*2, ' ') || name AS name, type, usage, usage_id, line, col
FROM user_identifiers
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id;
NAME TYPE USAGE USAGE_ID LINE COL
------------------------------ ------------------ ----------- ---------- ---------- ----------
TEST_PLSCOPE PROCEDURE DECLARATION 1 1 11
TEST_PLSCOPE PROCEDURE DEFINITION 2 1 11
P_IN FORMAL IN DECLARATION 3 1 25
L_VAR VARIABLE DECLARATION 4 2 3
L_VAR VARIABLE ASSIGNMENT 5 4 3
P_IN FORMAL IN REFERENCE 6 4 12
L_VAR VARIABLE ASSIGNMENT 7 6 3
L_VAR VARIABLE REFERENCE 8 6 12
8 rows selected.
(this script was taken from here)
Some additional information about it: The documentation states that some identifiers will not listed unless the STANDARD package is recompiled after the PLSCOPE_SETTINGS parameter is set properly. However there is some opening community discussions about invalidation of basic procedures not being revalidated again, even on use of urlrp procedure. This way, we don’t recommend to use it directly on Production environment without validating before in an equivalent environment and taking all caution measures as possible.
Hope you enjoy it. Cheers!
Oracle SQL: Aggregate List – LISTAGG
Know this command?
I think it’s very useful, but not so often I see that in use… More about it:
It was introduced in Oracle 11g Release 2, as an analytic function to make the life easier fot the PLSQL Dev, specially when working to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.
In case WM_CONCAT is in use for this end, be aware that WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). Also, WM_CONCAT has been removed from 12c onward, once it was completely replaced by LISTAGG.
More info and examples can be found here:Â https://oracle-base.com/articles/12c/listagg-function-enhancements-12cr2
Hope you enjoy it. Cheers!
PLSQL: Which code is taking longer?
So you have a slow process calling several PLSQL Codes, including Procedures, Functions and etc, but don’t know what is taking longer?
Your problems has ended…Â
In Oracle 11gR1 was introduced the PL/SQL Hierarchical Profiler to help developers by providing hierarchical profiler data analysis for PL/SQL programs.
From Oracle base:
“The hierarchical profiler consists of the DBMS_HPROF package, which feels similar to the DBMS_PROFILER and DBMS_TRACE packages, and the plshprof command line utility to converts the profile information into HTML format.”
For example, we can set a profiler for procedure proc_example_1 and check in all calls and related statistics:
BEGIN
DBMS_HPROF.start_profiling (
location => 'ORACLE_DIR',
filename => 'prof.txt');
proc_example_1(p_number => 10);
DBMS_HPROF.stop_profiling;
END;
/
And to see results, a simple output is:
COLUMN owner FORMAT A20
COLUMN module FORMAT A20
COLUMN type FORMAT A20
COLUMN function FORMAT A25
SELECT symbolid,
owner,
module,
type,
function
FROM dbmshp_function_info
WHERE runid = 1
ORDER BY symbolid;
SYMBOLID OWNER MODULE TYPE FUNCTION
---------- -------------------- -------------------- -------------------- -------------------------
1 TEST proc_example_1 PROCEDURE proc_example_1
2 TEST proc_example_2 PROCEDURE proc_example_2
3 TEST proc_example_3 PROCEDURE proc_example_3
4 SYS DBMS_HPROF PACKAGE BODY STOP_PROFILING
5 TEST proc_example_3 PROCEDURE __static_sql_exec_line5
5 rows selected.
Which can also be seen with this nice SQL from Oracle Base (Tim Hall):
SET LINESIZE 500 PAGESIZE 1000
COLUMN name FORMAT A100
SELECT RPAD(' ', (level-1)*2, ' ') || a.name AS name,
a.subtree_elapsed_time,
a.function_elapsed_time,
a.calls
FROM (SELECT fi.symbolid,
pci.parentsymid,
RTRIM(fi.owner || '.' || fi.module || '.' || NULLIF(fi.function,fi.module), '.') AS name,
NVL(pci.subtree_elapsed_time, fi.subtree_elapsed_time) AS subtree_elapsed_time,
NVL(pci.function_elapsed_time, fi.function_elapsed_time) AS function_elapsed_time,
NVL(pci.calls, fi.calls) AS calls
FROM dbmshp_function_info fi
LEFT JOIN dbmshp_parent_child_info pci ON fi.runid = pci.runid AND fi.symbolid = pci.childsymid
WHERE fi.runid = 1
AND fi.module != 'DBMS_HPROF') a
CONNECT BY a.parentsymid = PRIOR a.symbolid
START WITH a.parentsymid IS NULL;
NAME SUBTREE_ELAPSED_TIME FUNCTION_ELAPSED_TIME CALLS
--------------------------------------------------- -------------------- --------------------- ----------
TEST.proc_example_1 31262 31 1
TEST.proc_example_2 31231 133 10
TEST.proc_example_3 31098 3241 100
TEST.proc_example_3.__static_sql_exec_line5 27857 27857 1000
Cheers!
Increase PLSQL Performance with SIMPLE_INTEGER Data Type
Did you know the SIMPLE_INTEGER datatype is a subtype of the PLS_INTEGER datatype and can increase the speed of integer arithmetic in natively compiled code as well as in interpreted code?
Nice, right?
This is an 11g feature, so not that new… Have a look and use it!
Check below for datatype declaration example:
PLS_INTEGER:
var_old_fashioned PLS_INTEGER := 0;
SIMPLE_INTEGER:
var_new_way SIMPLE_INTEGER := 0;
Cheers!
Oracle: Easily Decoding ROWID
Hi all,
Recently I needed to decode the rowid so I could find some information about it, I found this bit of code that I thought useful:
SET SERVEROUTPUT ON
DECLARE
v_rid VARCHAR2(20) ;
v_type NUMBER;
v_obj NUMBER;
v_rfno NUMBER;
v_bno NUMBER;
v_rno NUMBER;
BEGIN
v_rid := 'AAAAASAABAAAADxAAb';
dbms_output.put_line('Row_ID = "'||v_rid||'"');
dbms_rowid.rowid_info(CHARTOROWID(v_rid), v_type, v_obj, v_rfno, v_bno, v_rno);
IF v_type = 0 THEN
dbms_output.put_line('RowID Type -> Restricted');
ELSE dbms_output.put_line('RowID Type -> Extended');
END IF;
dbms_output.put_line('Object ID = "'||v_obj||'"');
dbms_output.put_line('Relative File Number = "'||v_rfno||'"');
dbms_output.put_line('Block Number = "'||v_bno||'"');
dbms_output.put_line('Row Number = "'||v_rno||'"');
END;
/
Note that I have hard-coded the rowid but it is relatively easy to either edit this or indeed to incorporate this into a procedure.
Here’s the sample output
Row_ID = "AAAAASAABAAAADxAAb" RowID Type -> Extended Object ID = "18" Relative File Number = "1" Block Number = "241" Row Number = "27"
Hope this helps!
Cheers!
PL/SQL Generalized Invocation
Did you know we have that since 11g? The generalized invocation allows a subtype to invoke a method of a parent type (supertype) using the following syntax:
(SELF AS supertype_name).method_name
Check the example below to understand it. First, creating original type:
CREATE OR REPLACE TYPE type_test AS OBJECT (MEMBER FUNCTION return_text RETURN VARCHAR2) NOT FINAL; / CREATE OR REPLACE TYPE BODY type_test AS MEMBER FUNCTION return_text RETURN VARCHAR2 IS BEGIN RETURN 'This is the original text.'; END; END; /
And now creating a subtype of this object, which adds a new attribute and method as well as overriding the member’s function.
CREATE OR REPLACE TYPE subtype_test UNDER type_test (OVERRIDING MEMBER FUNCTION return_text RETURN VARCHAR2); / CREATE OR REPLACE TYPE BODY subtype_test AS OVERRIDING MEMBER FUNCTION return_text RETURN VARCHAR2 IS BEGIN RETURN (self AS type_test).return_text || ' This is an additional subtype text.'; END; END; /
And when calling:
SET SERVEROUTPUT ON DECLARE my_subtype subtype_test; BEGIN DBMS_OUTPUT.put_line(my_subtype.show_attributes); END; / This is the original text. This is an additional subtype text.
A type can invoke the member functions of any parent type in this way, regardless of the depth of the inheritance.
Pretty nice, right?
Cheers!
WHEN OTHERS -> NULL: Hidding your PLSQL Errors?
Are you using WHEN OTHERS -> NULL to hide your PLSQL errors?
Don’t be so sure…
WHEN OTHERS exception handlers that do nothing and don’t raise errors using RAISE or RAISE_APPLICATION_ERROR can often hide code failures that result in hard to identify bugs.
To avoid this, a new PL/SQL compiler warning was added in 11g to identify those kind of situations. Check example below:
SQL> ALTER SESSION SET plsql_warnings = 'enable:all';
Session altered.
SQL> CREATE OR REPLACE PROCEDURE warning_test AS
2 BEGIN
3 RAISE_APPLICATION_ERROR(-20000, 'This is an Exception!');
4 EXCEPTION
5 WHEN OTHERS THEN
6 NULL;
7 END;
8 /
SP2-0804: Procedure created with compilation warnings
SQL> SHOW ERRORS
Errors for PROCEDURE OTHERS_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8 PLW-06009: procedure "WARNING_TEST" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR
Nice, right?!
There are also some other warnings improvements like:
- New NO_DATA_NEEDED Predefined Exception: ORA-06548: For parallel access and pipelined table functions the caller of a pipelined function does not need more rows to be produced by the pipelined function.
Warnings:
- Severe
– 5018 – omitted optional AUTHID clause
– 5018 – omitted optional AUTHID clause
– 5019 – deprecated language element
– 5020 – parameter name must be identified - Informative
– 6016 – native code generation turned off (size/time)
– 6017 – operation will raise an exception
– 6018 – an infinity or NaN value computed or used
Cheers!