SQL Developer: New worksheet, new session?

SQL Developer loading. Yesterday.

Default Behaviour

The out of the box behaviour of SQL Developer is that SQL worksheets share the same connection.

What does that mean?

If you perform a insert, update or delete in one worksheet without issuing a commit, the result(s) can be viewed in another shared worksheet. This can be illustrated by the following example.

After launching SQL Developer and connecting to a database, I start two new SQL worksheets

The next two screen shots show that for both SQL worksheets that the table t is empty

Next using using the first worksheet, an insert into table t operation is performed. No commit has been issued.

Navigating to the second worksheet and querying the table again and the newly inserted row can be seen.

Unshared Connections

It is possible to open a SQL Worksheet with a dedicated database connection. In SQL Developer this is called an unshared connection.

From an existing worksheet the keyboard shortcut on Windows is CTL + SHIFT + N. This will only work from a shared worksheet. It is not possible to open a new unshared worksheet from an existing unshared worksheet.

The following screenshots demonstrates how an unshared connection differs from a shared connection.

Two shared SQL Worksheets: hr and hr~1 are started along with and one unshared: hr (Unshared). In the first worksheet I perform an insert operation which has not been committed.

Switching to the second shared worksheet, the uncommitted record is shown after querying the table.

Moving to the unshared connection and querying the table no row is returned.

The unshared worksheet is working as expected as a new session will not see uncommitted records.

Acknowledgements

Jeff Smith, the Oracle Product manager for SQL Developer has a superb blog where he shares many SQL Developer hints and tips. One of the posts
Opening Unshared Worksheets was the inspiration and source for this post.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and the perils of the RAISE statement

Since dbms_utility.format_error_backtrace has been available from Oracle 10g this isn’t another what it ishow to use it post. Instead I will be taking this opportunity to discuss a nuance of dbms_utility.format_error_backtrace that you may not be aware of.

dbms_utility.format_error_backtrace reports the call stack back to the exception or the last raise statement which if you are not aware of could catch you out.

A quick word on the examples that follow, I am not proposing you should use this functionality in its raw format as I have done here. I believe that for production code calls to dbms_utility.format_error_backtrace should be encapsulated within your exception handling package.

The following code creates 3 procedures. procedure a calls b and b calls c. Within procedure c an exception is raised. There is no intermediate exception handling and the exception passes back to procedure a which makes a call to dbms_utility.format_errror_backtrace.

create or replace procedure a
is
begin
 dbms_output.put_line('In procedure a');
 b();
exception
 when no_data_found then
 dbms_output.put_line(dbms_utility.format_error_backtrace);
end a;
/

create or replace procedure b
is
begin
 dbms_output.put_line('In procedure b');
 c();
end b;
/

create or replace procedure c
is
begin
 dbms_output.put_line('In procedure c');
 RAISE NO_DATA_FOUND;
end c;
/

When procedure a is run, the following results are observed:

begin
   a();
end;

In procedure a
In procedure b
In procedure c
ORA-06512: at "HR.C", line 5
ORA-06512: at "HR.B", line 5
ORA-06512: at "HR.A", line 5

The error stack shows the exception started on line 5 (line 24) . Excellent, when things go wrong we can track the problem down to the line number! However procedure b needs to change and the developer, Gary Careless makes the required enhancements, one of the changes is the introduction of a new exception handling block that includes a RAISE statement.

The amended version of procedure b is now:

create or replace procedure b
is
begin
 dbms_output.put_line('In procedure b');
 c();
 -- lots of new functionality here
exception
 -- new exception handler for the new functionality
 when others then
 -- do something here and re-raise the exception
 RAISE;
end b;
/

Now when procedure a is run again we see the following:

begin
   a();
end;

In procedure a
In procedure b
In procedure c
ORA-06512: at "HR.B", line 11
ORA-06512: at "HR.A", line 5

Oh no! we have lost the line number where the exception occurred. Instead of displaying that the exception was raised by line 5 of procedure c as it did in the first example, the callstack reports the exception location is now at the the location of the RAISE statement within procedure b!

To get the accurate output from dbms_utility.format_error_backtrace you need to call it

  • In the exception section that first raised the exception

or

  • Call it from the outermost PL/SQL block and ensure that the intermediate routines do not raisere raise the exception.

The location of exception handling within an application is a project wide decision which should be agreed before development begins. However if your error handling package includes a call to dbms_utility.format_error_backtrace then you should keep this nuance in mind.

No surprises with Oracle 12c Identity Column performance

Oracle 12c introduced the Identity Column. You can find out more about this feature in the new features guide and within the CREATE TABLE documentation.

In this article I will use Tom Kyte’s run stats utility to compare the performance of the IDENTITY column with the explicit use of a sequence object. The script below will be used to insert 10,0000, 100,000 and finally a million rows.

The tests were performed using Virtual Box running the pre built Database App Development VM. The database version at this time was 12.1.0.2.0 and all the examples were developed using SQL Developer 4.0.3.16

To support the tests,  the following objects are required. Firstly, a table using the IDENTITY column is created. Note the syntax for creating an IDENTITY column. In addition as I know there will be some large inserts into this table I have adjusted cache size of the sequence accordingly.


CREATE TABLE t_identity(id      NUMBER         GENERATED AS IDENTITY CACHE 1000
                                               CONSTRAINT t_identity_pk PRIMARY KEY,
                        details VARCHAR2(32))
/

Next a table and a sequence which will be used to hold the results of the inserts via a regular Oracle sequence is created. Again the sequence cache size has been increased from the default.

CREATE TABLE t_seq(id      NUMBER CONSTRAINT t_seq_pk PRIMARY KEY,
                   details VARCHAR2(32))
/

CREATE SEQUENCE s1 CACHE 1000
/

Below is the test script. As a brief overview, it initialises the call to the runstats package, it then inserts the required number of records into the table with the IDENTITY column.

The runstats package is called again to show that the first part of the processing has finished and the second part is about to start. The second insert is identical to the first one with the exception of the explicit call to the sequence object.

Thanks to Oracle Base for the tip about using TIMESTAMP as a seed to dbms_random. I am not advocating using row by row processing to insert volumes of data of this size in the real world!


BEGIN

   runstats_pkg.rs_start();

END;
/

DECLARE

   l_data VARCHAR2(32);
   l_seed VARCHAR2(32);

BEGIN

   l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');

   dbms_random.seed (val => l_seed);

   FOR i IN 1 .. 1000000
   LOOP

      l_data := dbms_random.string(opt => 'a', len => 32);

      INSERT INTO t_identity(details)
      VALUES(l_data);

   END LOOP;

   COMMIT;

END;
/

BEGIN

   runstats_pkg.rs_middle();

END;
/

DECLARE

   l_data VARCHAR2(32);
   l_seed VARCHAR2(32);

BEGIN

   l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');

   dbms_random.seed (val => l_seed);

   FOR i IN 1 .. 1000000
   LOOP

      l_data := dbms_random.string(opt => 'a', len => 32);

      INSERT INTO t_seq(id,
                        details)
      VALUES(s1.nextval,
             l_data);

   END LOOP;

   COMMIT;

END;
/

BEGIN

   runstats_pkg.rs_stop(1000);

END;
/

Here is the runstats output for each of the runs

10,000 rows inserted


Run1 ran in 106 cpu hsecs
Run2 ran in 105 cpu hsecs
run 1 ran in 100.95% of the time
...
Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff  Pct
171,097   171,432   335   99.80%

100,000 rows inserted


Run1 ran in 1216 cpu hsecs
Run2 ran in 1156 cpu hsecs
run 1 ran in 105.19% of the time
...
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff      Pct
1,719,582   2,061,024   341,442   83.43%

1,000,000 rows inserted

Run1 ran in 12308 cpu hsecs
Run2 ran in 11835 cpu hsecs
run 1 ran in 104% of the time
...
Run1 latches total versus runs -- difference and pct
Run1         Run2         Diff      Pct
18,480,661   18,761,711   281,050   98.50%

and the difference between the two methods is negligible.

Summary

In this article, using Tom Kyte’s runstats utility, I have invested the performance of the Oracle 12c new feature; IDENTITY column

Acknowledgements

Tom Kyte for his seminal package runstats

 

An introduction to Application Context

The inspiration for this article came from reading Mark Hoxey excellent post on avoiding ORA-04068: existing state of packages has been discarded One of the potential solutions to this problem suggested by Mark is to use Application Context. I didn’t know too much about Application Context so this post is my way of documenting and sharing what I learnt.

What is Application Context?

Application Context a set of name value pairs stored in memory. Each context is essentially a namespace which contains one or more name value pairs. Application Context is a large subject and Oracle list a number of potential uses cases for Application Context.

My goal for this post is not to rewrite the documentation but to provide a walk through of creating an Application Context that could serve as replacement for the constant or variable data that is often duplicated in various package bodies scattered throughout your application.

All the examples in this article were built using Oracle’s pre-built Database App Development Virtual Machine which used Database version 11.2.0.2.

Example

CREATE OR REPLACE CONTEXT user_ctx USING pkg_user_application_context
/

The first line creates an Application Context with the name user_ctx. The USING clause identifies the PL/SQL package that can set or reset the context variables. In this example pkg_user_application_context is used. It is worth mentioning that the package specified by the USING clause does not need to exist at the time when the context is created but it must exist at run time. So the next step is create pkg_user_application_context

CREATE OR REPLACE PACKAGE pkg_user_application_context
IS

 PROCEDURE set_context_values;

END pkg_user_application_context;
/

CREATE OR REPLACE PACKAGE BODY pkg_user_application_context
IS
 PROCEDURE set_context_values
 IS

    ld_hire_date      DATE;
    ln_department     NUMBER(5);
    lv_is_a_manager   VARCHAR2(1);

 BEGIN

 -- Pretend look ups from HR tables happens here...

 DBMS_SESSION.SET_CONTEXT('user_ctx', 'hire_date', TO_CHAR(ld_hire_date, 'YYYYMMDD'));

 DBMS_SESSION.SET_CONTEXT('user_ctx', 'department_id', TO_CHAR(ln_department,'99999'));

 DBMS_SESSION.SET_CONTEXT('user_ctx', 'is_a_manager', lv_is_a_manager);

END pkg_user_application_context;
/

The package body shows a look up of various values from tables (which are not shown to keep the example from becoming too bloated) These values are then used by the call to DBMS_SESSION.SET_CONTEXT to create a number of name value pairs under the context created earlier.

A couple of points worth making; the values are stored as characters so for numbers and dates the appropriate calls to TO_CHAR are required and all the values created belong to the same Application Context, user_ctx

If an attempt is made to change the values of the Application context outside of the package pkg_user_application_context such as the following anonymous block :

BEGIN
   DBMS_SESSION.SET_CONTEXT('user_ctx', 'is_a_manager', 'Y');
END;
/

you will see the  following error message:


ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at line 2   

With both the Application Context and supporting package created we can now go ahead and start using the values within our application:

CREATE OR REPLACE PACKAGE BODY pkg_some_other_package
IS
 PROCEDURE interesting_hr_stuff
 IS

    ld_hire_date      DATE;
    ln_department     NUMBER(5);
    lv_is_a_manager   VARCHAR2(1);

 BEGIN

    ld_hire_date := TO_DATE(SYS_CONTEXT('user_ctx','hire_date'), 'YYYYMMDD');

    ln_department := TO_NUMBER(SYS_CONTEXT('user_ctx','department_id'));

    lv_is_a_manager := SYS_CONTEXT('user_ctx','is_a_manager');

    -- now work with the variables 

 END interesting_hr_stuff

END pkg_some_other_package;
/

The example shows another package (the spec is not shown) where the values are retrieved from the Application Context using the built in SYS_CONTEXT. The values are explicitly converted back to the expected data types and then used within the application.

Summary

In this article I have explained what an Application Context is and demonstrated how to create one use as a replacement for storing information that was previously held in package body constants and variables.

Acknowledgements

Mark Hoxey

Using SQL with Associative Arrays of records in Oracle 12c

The ability of using SQL to operate on Associative Arrays or PL/SQL tables as they were known when I started working as a Database Developer is one of my favourite new features of 12c. I wasn’t aware of it until I read the post by Connor Mcdonald.

It’s announcement within the new features guide is easy to overlook:

“The table operator can now be used in a PL/SQL program on a collection whose data type is declared in PL/SQL. This also allows the data type to be a PL/SQL associative array. (In prior releases, the collection’s data type had to be declared at the schema level.)”

The following example was developed using the the pre-built Oracle Virtual Machine Database Database App Development image with the versions:

SQL Developer: 4.0.0.13

Oracle Database: 12.1.0.1.0

The first step is to create a package spec which will contain the record type and Associative Array.

CREATE OR REPLACE PACKAGE cake
IS

   TYPE cake_record IS RECORD (cake_name   VARCHAR2(100),
                               cake_price  NUMBER(4));

   TYPE cake_aa IS TABLE OF cake_record
      INDEX BY PLS_INTEGER;

END cake;

There should be nothing surprising with this code, a record type is defined (Lines 4 & 5) and then an Associative Array is defined (Lines 7 & 8) based on the record.

To demonstrate the new functionality I create the following anonymous block:

DECLARE

laa_cakes   cake.cake_aa;
ln_price    NUMBER(4);

BEGIN

   laa_cakes(1).cake_name := 'Eccles';
   laa_cakes(1).cake_price := 10;

   laa_cakes(2).cake_name := 'Cinnamon Bun';
   laa_cakes(2).cake_price := 40;

  SELECT c.cake_price
    INTO ln_price
    FROM table(laa_cakes) c
   WHERE c.cake_name = 'Cinnamon Bun';

   DBMS_OUTPUT.PUT_LINE('The price of the Cinnamon Bun is: '|| TO_CHAR(ln_price));                     

END;
/

Lines 3 – 12, I create a Associative Array of the type defined in the package and populate it with a couple of cakes and their prices. I then use SQL via the TABLE  pseudofunction to query the Associative Array to tell me the price of one of the cakes.

And once run, I see the following output:

Before 12c I would have had to start looping through the Associative Array to look for the item of interest.

It is important to note that in order to use the TABLE pseudofunction the Associative Array has to reside in a package spec.

Summary

In this post I have demonstrated how to use SQL with Associative Array of Records.

Acknowledgements

Connor Mcdonald

Does using a sequence in a PL/SQL expression improve performance?

In versions of the Oracle Database prior to 11g Release 1, to populate a variable with the next value from a sequence you would have call the NEXTVAL function from within a SQL statement. From Oracle Database 11g Release 1 you can now use the NEXTVAL function in a PL/SQL expression.

So other than less typing, does using a sequence in this way improve performance?

The following examples are all based on the following sequence

CREATE SEQUENCE emp_seq
/

and were developed using the Oracle Developer Days Environment and used the following versions:

Oracle Linux running via Oracle Virtual Box
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Oracle SQL Developer 3.2.20.10.

To recap, the pre 11g method was:

...
SELECT emp_seq.nextval
  INTO some_variable
  FROM dual;
...

and from 11g Release one you can use the following:

...
some_variable := emp_seq.nextval;
...

Better Performance?

Using the method described by Adrian Billington you will be able see if the 11g method improves performance.

ALTER SESSION SET SQL_TRACE = TRUE
/

DECLARE

   emp_id PLS_INTEGER;

BEGIN

   FOR i IN 1 .. 10000
   LOOP

      emp_id := emp_seq.NEXTVAL;   

   END LOOP;

   FOR i IN 1 .. 10000
   LOOP

      SELECT emp_seq.NEXTVAL AS nval
        INTO emp_id
        FROM dual;

   END LOOP;

END;
/

SQL Trace is enabled and the new 11g method of using NEXTVAL via an expression is called 10000 times after which the pre 11g method of using a SQL statement is also called 10000 times. Note the alias of nval is used with the SQL statement method this will help us identify the SQL statement method.

Here is the output from the trace file. The first entry is for the new 11g method and the second entry is for pre 11g method (as proved by the use of the alias nval)

20140502_TraceFile

The trace file shows very little differences when using the new 11g method. Which is not surprising as the trace file also shows that “under the covers” Oracle has not changed the implementation for the new 11g method and NEXTVAL is still obtained via a SQL statement.

Summary

In this article I have shown the two methods that are available to populate a variable with the next value from a sequence and that there is no performance gains to be had by choosing one method over another.

Acknowledgements

Adrian Billington (oracle-developer.net)

Queuing for Oracle APEX (Part 2)

In Part 1 of this article I described the steps required to first create a queue and then how to enqueue messages using server side PL/SQL called from an Apex application.

In part 2 I will describe the steps required to dequeue the messages and how they may be displayed using Apex.

The first step is to write the procedure that will dequeue the messages from the queue. This procedure should be created in the same schema as the Queue Owner and the execute privilege should be given to Apex user.


CREATE OR REPLACE PROCEDURE feedback_dq
IS

   lkv_queue_name        CONSTANT VARCHAR2(60) := 'aquser.feedback_queue';
   lt_dq_opt             dbms_aq.dequeue_options_t;
   lt_mprop              dbms_aq.message_properties_t;
   lo_the_feedback       q_message;
   lt_deq_msgid          RAW(16);
   lb_further_feedback   BOOLEAN := TRUE;

   le_no_messages        EXCEPTION;
   PRAGMA EXCEPTION_INIT(le_no_messages, -25228);

BEGIN

   lt_dq_opt.wait := DBMS_AQ.NO_WAIT;

   WHILE lb_further_feedback
   LOOP
      BEGIN
         dbms_aq.dequeue
         (
            queue_name         => lkv_queue_name,
            dequeue_options    => lt_dq_opt,
            message_properties => lt_mprop,
            payload            => lo_the_feedback,
            msgid              => lt_deq_msgid
         );

         INSERT INTO feedback(id,
                              title,
                              details,
                              email_address,
                              date_time_created)
         VALUES(lo_the_feedback.ID,
                lo_the_feedback.title,
                lo_the_feedback.details,
                lo_the_feedback.email_address,
                SYSDATE);

         COMMIT;

      EXCEPTION

         WHEN le_no_messages THEN

            lb_further_feedback := FALSE;

      END;

   END LOOP;

END feedback_dq;

The procedure used to dequeue the messages is straight forward so I will only explain the salient points.

The wait parameter of the dequeue option is set to NO_WAIT (Line 16). This ensures that if there are no messages, control returns from dbms_aq.dequeue.

dbms_aq.dequeue is called (line 21) and if there is a message its contents are inserted into the feedback table.

When there are no more messages and because NO_WAIT has been specified dbms_aq.dequeue raises an ORA-25228. This is handled in the exception section which sets the boolean controlling the loop false and allows the procedure to complete.

With the dequeue procedure in place all that is left is to create an Apex page that will call the feedback_dq procedure and display the contents in a report on the same page.

Here is the page I created. The “Get Feedback button” simply calls the procedure feedback_dq and the report is based on the feedback table. So before any messages have been dequeued the page looks like this:

Untitled

and then after the “Get Feedback” button had been pressed, the dequeued messages are displayed in a report:

Untitled1

Summary

In this two part article I have shown how you get started with using Oracle Streams Advanced Queuing with Application Express.

Acknowledgements and further reading:

Oracle Developer.net introduction to advanced queuing

Oracle Streams Advanced Queuing User’s Guide