Tracking the SQL and its line no from PL/SQL

When we use PL/SQL for processing the data, it is easy to track each SQL that is executing and its line no# associated with the PL/SQL units.

Let’s start with a demo.

demo@ORA11G> create or replace package mypkg
  2  as
  3     procedure p1;
  4     procedure p2;
  5  end;
  6  /

Package created.

demo@ORA11G> create or replace package body mypkg
  2  as
  3     procedure p1 as
  4     begin
  5             for x in (select b1.owner,b2.username
  6                     from all_objects b1, all_users b2)
  7             loop
  8                     null;
  9             end loop;
 10     end;
 11
 12     procedure p2 as
 13     begin
 14             for x in (select b1.owner,b2.username
 15                     from all_objects b1, all_users b2)
 16             loop
 17                     null;
 18             end loop;
 19     end;
 20  end;
 21  /

Package body created.

Say I have a package “mypkg” with a couple of methods in it, now let’s execute this method from a connection.

demo@ORA11G> select userenv('sid') from dual;

USERENV('SID')
--------------
             5

demo@ORA11G> exec mypkg.p2;


While this execution is in-progress, we could easily track the SQL that is currently executing along with its line no# with in the package.

rajesh@ORA11G> select sql_fulltext, program_id, program_line#,
  2          (select object_name
  3            from all_objects
  4            where object_id = program_id) object_name ,
  5        plsql_entry_object_id c1,
  6        plsql_entry_subprogram_id c2,
  7        plsql_object_id  c3,
  8        plsql_subprogram_id c4
  9  from v$session b1,
 10       v$sql b2
 11  where b1.sid = 5
 12  and b1.sql_id = b2.sql_id
 13  and b1.sql_child_number = b2.child_number;

SQL_FULLTEXT         PROGRAM_ID PROGRAM_LINE# OBJECT_NAME          C1         C2      C3            C4
-------------------- ---------- ------------- ------------ ---------- ---------- ---------- ----------
SELECT B1.OWNER,B2.U      88507             5 MYPKG             88506          2
SERNAME FROM ALL_OBJ
ECTS B1, ALL_USERS B2


The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID from V$SESSION would tell us the name of the package and method that is currently executing.

So the above output from V$SESSION tell us that we are executing second method from the package MYPKG.

rajesh@ORA11G> select owner,object_name,object_type,object_id
  2  from all_objects
  3  where object_id in( 88506,88507 );

OWNER      OBJECT_NAM OBJECT_TYPE           OBJECT_ID
---------- ---------- -------------------- ----------
DEMO       MYPKG      PACKAGE BODY              88507
DEMO       MYPKG      PACKAGE                   88506


Similarly the PROGRAM_ID and PROGRAM_LINE# from the V$SQL provide us the information about the program unit and its line no# to which this SQL is currently located.

So the PROGRAM_ID = 88507 represents that it is a PACKAGE BODY (for the package MYPKG) and the PROGRAM_LINE# represents the line no# at which this SQL is present.

rajesh@ORA11G> column text format a40
rajesh@ORA11G> select line, text
  2  from all_source
  3  where name ='MYPKG'
  4  and type ='PACKAGE BODY'
  5  and owner ='DEMO'
  6  and line <=10
  7  order by line ;

      LINE TEXT
---------- ----------------------------------------
         1 package body mypkg
         2 as
         3      procedure p1 as
         4      begin
         5              for x in (select b1.owner,b2.username
         6                      from all_objects b1, all_users b2)
         7              loop
         8                      null;
         9              end loop;
        10      end;

10 rows selected.

rajesh@ORA11G>

SQL JSON query operator – Part III

This entry is about JSON_EXISTS. JSON_EXISTS takes a path expression and check if that path selects one (or multiple) values in the JSON data.


demo@ORA12C> create table t( doc_id  int,
  2     doc_details varchar2(1000),
  3     constraint valid_json_check check(doc_details is json) );

Table created.

demo@ORA12C> insert into t values (1 , '{"id":1, "name" : "Jeff"}' );

1 row created.

demo@ORA12C> insert into t values (2 , '{"id":2, "name" : "Jane", "status":"Gold"}' );

1 row created.

demo@ORA12C> insert into t values (3 , '{"id":3, "name" : "Jill", "status":["Important","Gold"]}' );

1 row created.

demo@ORA12C> insert into t values (4 , '{"name" : "John", "status":"Silver"}' );

1 row created.

demo@ORA12C> commit;

Commit complete.



Let’s find rows where the field name ‘status’ exists.


demo@ORA12C> select count(*)
  2  from t
  3  where json_exists(doc_details,'$.status');

  COUNT(*)
----------
         3

demo@ORA12C>


We can use JSON_EXISTS to enforce that every customer has an ID field using a check constraint, and also clean up the data having no ID fields. 


demo@ORA12C> delete from t
  2  where not json_exists(doc_details,'$.id') ;

1 row deleted.

demo@ORA12C> alter table t
  2  add constraint t_id_check
  3  check( json_exists(doc_details,'$.id') );

Table altered.

demo@ORA12C> insert into t(doc_id,doc_details)
  2     values (4 , '{"name" : "John", "status":"Silver"}' );
insert into t(doc_id,doc_details)
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.T_ID_CHECK) violated


demo@ORA12C>