What can you do with APEX_COLLECTION

The APEX_COLLECTION (Oracle 12c onwards) package can be a good choice when you need to work with
data in a temporary, in-memory table-like structure.

When should I use the APEX_COLLECTION?

  1. Temporary data storage: If you need to temporarily store data that is
    generated by a report or a form, using APEX_COLLECTION can be a good option. Since the
    data is stored in memory rather than in a physical table in the database, you don’t need
    to worry about the overhead of creating and managing a database table.
  2. Working with large datasets: If you need to work with large datasets,
    APEX_COLLECTION can be more efficient than other options, such as creating a temporary
    table in the database. Since the data is stored in memory, it can be accessed and
    manipulated more quickly than data that is stored in a physical table.
  3. Non-persistent data: If you don’t need to persist the data between
    sessions or across different users, APEX_COLLECTION can be a good choice. Since the data
    is stored in memory, it is automatically cleared when the session ends or when the user
    logs out.
  4. Flexible data structure: APEX_COLLECTION provides a flexible data
    structure that can be easily modified at runtime. You can add, update, and delete
    records as needed, and you can define the structure of the collection dynamically based
    on your requirements.

When should I avoid it?

While APEX_COLLECTION can be a useful tool for working with temporary data in Oracle APEX,
there are some scenarios where it may not be the best choice. Here are a few situations
where you may want to consider alternative approaches:

  1. Large or complex datasets: While APEX_COLLECTION can be efficient for
    working with large datasets, it may not be the best choice for very large or complex
    datasets. In such cases, it may be better to use a physical table in the database or a
    dedicated data store, such as a NoSQL database or a document store.
  2. High transaction volume: If you have a high volume of transactions or
    concurrent users, APEX_COLLECTION may not be able to keep up with the demand. In such
    cases, it may be better to use a dedicated database table or another data storage
    mechanism that is optimized for high performance.
  3. Data persistence: If you need to persist the data across sessions or
    across different users, APEX_COLLECTION may not be the best choice. While the data is
    stored in memory and can be accessed quickly, it is not durable and will be lost when
    the session ends or when the user logs out. In such cases, it may be better to use a
    dedicated database table or another data storage mechanism that is designed for
    durability and persistence.
  4. Complex data structures: While APEX_COLLECTION can be flexible and
    dynamic, it may not be the best choice for working with very complex data structures or
    data types. If you need to work with data that has a complex or hierarchical structure,
    it may be better to use a dedicated data store or a specialized data modeling tool that
    is designed to handle such data.

That being said, there are also some limitations to using APEX_COLLECTION, such as the
inability to create indexes or enforce constraints, and the limited support for complex data
types. In some cases, it may be more appropriate to use a physical table or other data
storage
mechanism, depending on your specific requirements and use case.

 Alright, now that you know the pros and cons, here is a basic tutorial of what you need to work
with it.

Using APEX_COLLECTION

Create an APEX collection:

You can create an APEX collection using the APEX_COLLECTION.CREATE_COLLECTION procedure. This
procedure
creates a new collection with the specified name and collection type.

BEGIN
APEX_COLLECTION.CREATE_COLLECTION(
p_collection_name => 'MY_COLLECTION',
p_collection_type => 'APEX_COLLECTION_TYPE_VARCHAR2'
);
END;

Add records to the collection:

You can add records to an APEX collection using the APEX_COLLECTION.ADD_MEMBER procedure. This
procedure
adds a new record to the specified collection with the specified values.

BEGIN
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => 'MY_COLLECTION',
p_c001 => 'John',
p_c002 => 'Doe',
p_c003 => 'johndoe@example.com'
);
END;

Access records in the collection:

You can access the records in an APEX collection using the APEX_COLLECTION.GET_MEMBERS function. This
function returns a cursor that can be used to iterate over the records in the collection.

DECLARE
l_cursor apex_t_varchar2;
l_rec apex_collection_types.apex_collection_varchar2_nt;
BEGIN
l_cursor := apex_collection.get_members(p_collection_name => 'MY_COLLECTION');
LOOP
FETCH l_cursor BULK COLLECT INTO l_rec LIMIT 100;
FOR i IN 1..l_rec.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_rec(i).c001 || ' ' || l_rec(i).c002 || ': ' || l_rec(i).c003);
END LOOP;
EXIT WHEN l_cursor%NOTFOUND;
END LOOP;
END;

Merge records in the collection:

You can merge records in an APEX collection using the APEX_COLLECTION.MERGE_COLLECTION procedure. This
procedure merges the specified records from the source collection into the target collection.

BEGIN
APEX_COLLECTION.MERGE_COLLECTION(
p_target_collection_name => 'MY_COLLECTION',
p_source_collection_name => 'OTHER_COLLECTION',
p_member_key => '1',
p_update_column => 'C003',
p_update_value => 'newvalue@example.com'
);
END;

Change records in the collection:

You can change records in an APEX collection using the APEX_COLLECTION.UPDATE_MEMBER procedure. This
procedure
updates the specified record in the collection with the specified values.

BEGIN
APEX_COLLECTION.UPDATE_MEMBER(
p_collection_name => 'MY_COLLECTION',
p_seq => 1,
p_c001 => 'Jane',
p_c002 => 'Doe',
p_c003 => 'janedoe@example.com'
);
END;

Delete records from the collection:

You can delete records from an APEX collection using the APEX_COLLECTION.DELETE_MEMBER procedure. This
procedure
deletes the specified record from the collection.

BEGIN
APEX_COLLECTION.DELETE_MEMBER(
p_collection_name => 'MY_COLLECTION',
p_seq => 1
);
END;

Conclusion

That’s it! Using the APEX_COLLECTION package, you can create, access, merge, change, and delete records in a
collection in Oracle APEX.

Getting started with APEX_STRING.SPLIT

Dealing with string manipulation in PL/SQL isn’t hard, but it can be more work than it’s worth. Converting lists of values coming from HTML inside a single text item, separating lists based on multilple types of separators or even just the first N elements can require you to make an algorithm for something that kind of trivial.

For that reason I would like to show you the APEX_STRING.SPLIT function that should take care of most of you issues. Let’s take a look at the following example:

DECLARE
l_text VARCHAR2(100) := 'apple,banana,melon,orange';
l_delimiter VARCHAR2(1) := ',';
l_array apex_t_varchar2;
BEGIN
l_array := apex_string.split(l_text, l_delimiter);
FOR i IN 1 .. l_array.count
LOOP
DBMS_OUTPUT.put_line(l_array(i));
END LOOP;
END;

This is a basic example that receives 2 parameters, one which is the varchar2 that we want to split and the other is the separator. Easy

Now lets take a look on this example where the delimiters are different between themselves.

DECLARE
l_text VARCHAR2(100) := 'apple;banana,melon:grapes,lemon';
l_delimiter VARCHAR2(10) := '[;,:]';
l_array apex_t_varchar2;
BEGIN
l_array := apex_string.split(l_text, l_delimiter);
FOR i IN 1 .. l_array.count
LOOP
DBMS_OUTPUT.put_line(l_array(i));
END LOOP;
END;

As you may have noticed, the separator is provided as a regular expression, which means we get a lot of flexibility when working this way.

For this last example, we’ll be applying a delimiter.

DECLARE
l_text VARCHAR2(100) := 'apple,banana,cherry,date';
l_delimiter VARCHAR2(1) := ',';
l_array apex_t_varchar2;
BEGIN
l_array := apex_string.split(l_text, l_delimiter, 2);
FOR i IN 1 .. l_array.count
LOOP
DBMS_OUTPUT.put_line(l_array(i));
END LOOP;
END;

This is quite easy as well; it simply stops our breaking of the varchar after Nth iterations. where the Nth item in the array will have all remaining values.

 

Do you have CLOBs or Numbers?

There are two other functions I think are worth mentioning, and the have nearly the same signature, which could help you with numbers and values larger than varchar2.

  • SPLIT_CLOBS: take is a CLOB as the first parameter, same separation and limit rules. Return an apex_t_clob
  • SPLIT_NUMBERS: Doesn’t have the limit parameter and return an apex_t_number

 

Conclusion

That’s about it, with this package you can do a lot more and if you are working with APEX I highly recommend you taking a look at it’s documentation at https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/APEX_STRING.html#GUID-CAFD987C-7382-4F0F-8CB9-1D3BD05F054A

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!