Tag: plsql

Database, SQL | 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.


 

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, SQL | PLSQL
			  

			
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!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, SQL | PLSQL
			  

			
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!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, Java | DevOps | APEX | Scripts, SQL | PLSQL
			  

			
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!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, SQL | PLSQL, Tools
			  

			
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!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, SQL | PLSQL
			  

			
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!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, SQL | PLSQL
			  

			
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!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					SQL | PLSQL
			  

			
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!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, SQL | PLSQL
			  

			
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!