07 April 2014

Current_Schema and the Data Dictionary

Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.

The regular install script for Logger looks something like the following (parts removed and table names are changed):

set verify off serveroutput on

begin
   execute immediate 'create table new_table (x int)';
end;
/

prompt ****
prompt *** Query user_tab_columns
prompt ****
declare
    l_nullable varchar2(32767);
begin
   select nullable
    into l_nullable
    from user_tab_columns
   where table_name = 'NEW_TABLE'
     and column_name = 'X'
   ;
   dbms_output.put_line('Found the NEW_TABLE, do some stuff to it');
end;
/
The first lines make sure that the user is not prompted for input and to see output from DBMS_OUTPUT.
The "Create table" is done using dynamic SQL after which some more action is taken. To determine which action needs to be taken is handled in the PL/SQL block - now simply replace by some DBMS_OUTPUT. In this PL/SQL block the datadictionary view USER_TAB_COLUMNS is queried.
This install script is usually run in the schema where the objects need to be installed.

This time, however, the install script needed to be adjusted for the DBA to run the script. As the DBA didh't want to log into different schema's during the complete install, the files needed to include the following at the top of the scripts:

alter session set current_schema = &1;
What this does is best explained in the Oracle documentation:
This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.
The install script was now called (by the DBA) as follows:
@install.sql SCOTT

Adding the ALTER SESSION command at the top of the install script produced this output

Session altered.


PL/SQL procedure successfully completed.

****
*** Query user_tab_columns
****
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
I would have expected that changing the CURRENT_SCHEMA would also query the USER_TAB_COLUMNS related to the schema that we changed into. This is not the case.

New install script

The install script needed to be adjusted to query the DBA_TAB_COLUMNS instead of the USER_TAB_COLUMNS:

set verify off serveroutput on

alter session set current_schema = &1;

begin
   execute immediate 'create table new_table (x int)';
end;
/

prompt ****
prompt *** Query dba_tab_columns
prompt ****
declare
    l_nullable varchar2(32767);
begin
   select nullable
    into l_nullable
    from dba_tab_columns
   where owner = upper ('&1')
     and table_name = 'NEW_TABLE'
     and rownum = 1;
   dbms_output.put_line('Found the NEW_TABLE, do some stuff to it');
end;
/
Now the install script works as desired:
****
*** Query dba_tab_columns
****
Found the NEW_TABLE

PL/SQL procedure successfully completed.

Difference between SESSION_USER and CURRENT_SCHEMA

SQL> col session_user format a35
SQL> col current_schema format a35
SQL> select sys_context ('userenv', 'session_user') session_user
  2        ,sys_context ('userenv', 'current_schema') current_schema
  3    from dual
  4  /

SESSION_USER                        CURRENT_SCHEMA
----------------------------------- -----------------------------------
SYS                                 SCOTT

Not so strange

Knowing what you know after reading the above, the following is not so strange anymore:

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 STR                                                VARCHAR2(25)


SQL> select column_name
  2        ,data_type
  3    from user_tab_columns
  4   where table_name = 'T'
  5  /

COLUMN_NAME                    DATA_TYPE
------------------------------ -------------------------
ID                             NUMBER

These scripts were run on the following version:

SQL> select *
  2    from v$version
  3  /

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Links

04 March 2014

Bookreview: Oracle APEX Cookbook - 2nd edition by Marcel van der Plas and Michel van Zoest

A few weeks ago Packt Publishing asked me if I would give a book review for the recently released Oracle APEX Cookbook, the second edition by Marcel van der Plas and Michel van Zoest. In exchange I would get the e-book for free, so this can be considered a sponsored blogpost. Even though it is sponsored I will give my honest opinion about it.

The book contains fifteen chapters starting with "Creating a Basic APEX Application" covering several topics such as "Themes and Templates", "APEX Plug-ins", "Using Web Services", '"HTML5 and CSS3" and ending with "Mobile".
Each of the chapters contain several recipe on how to implement a particular feature or functionality.

The way each recipe is setup is as follows: it starts with a short introduction "Getting ready", followed by "How to do it..." and lastly "How it works...". Most recipes also include "There's more..." which gives you additional information about the topic at hand.

Because the book is written in this modular method, it is easy to pick and choose the order in which you want to go through the book. You feel that you know how to "create a basic APEX application" but haven't created your own plug-in, then skip to the chapter on "APEX Plug-ins". It is not necessary to read the book in the order of the chapters, which I tend to do.

Conclusion

Usually I read books before I go to sleep, and yes most of the time the are technical books. This book was no exception. Even though I work with APEX every day, sometimes it was hard to follow along with the different recipes. In my opinion this is not a shortcoming of the book, but the way I tend to read books. This book would be more suited to be used following the recipes while working in APEX.

The recipes in the book follow a similar pattern as the official Oracle documentation. Each recipe starts with a section called "How to do it..." and provide a list of "do this", e.g. "1. Click on the Create Page button" or "6. Click on Create". This works out fine as long as the wizards don't change (which could happen with new releases of APEX). For the beginner this could be confusing if they work with a slightly different version of APEX. For the more experienced APEX developer this should be no problem, they would know the intended behaviour.

Where this book is ahead of the way the official Oracle documentation is set up, is the explanation after the part "How to do it..." called "How it works...". In this section the concepts are explained of what you just created (if you follow along which I obviously didn't).

At first when I started reading the book, I tend to start with the first chapter and work my way through to the end, I was under the impression that the intended audience was the beginning APEX developer. But as the book progressed, I ran into topics which I hardly ever (or never at all) have touched and picked up some good hints and tips. I'm sure that I will get the text handy when I need to, for instance, translate an application or work with websheets. The question then arises: if I haven't used these features before - would I need them in the future?

All in all, I like the book and would surely recommend it - for all APEX developers and especially for beginning APEX developers.

Thanks Packt for providing me a copy of the book and allowing me to review it.

Links

Packt Publishing: APEX Cookbook, 2nd edition

07 February 2014

Triggering and Handling a Custom Event in APEX

On one of the pages there was a need to refresh a report region when a node in an APEX Tree was clicked. As far as I know there is no built-in method to create a Dynamic Action to a node in an APEX Tree, so this might call for a Custom Event.
If there is a built-in method, please leave a comment :)

Trigger Custom Event

To trigger a Custom Event, change the "link" column in the APEX Tree Query to something like the following:

select ...
      ,'javascript:apex.event.trigger(document, "nodeClicked", "'
        ||value_from_table||'"); void(0);' as link
from ...
This will include a little bit of javascript in the anchor when the APEX Tree is generated which triggers the "nodeClicked" event passing the "value_from_table" that you want to pass in.
The "value_from_table" will be available in the Custom Event as the Data object.

Handling the Custom Event

To handle the Custom Event, follow the "Create Dynamic Action" wizard and fill in the following:

  • Event: Custom
  • Custom Event: nodeClicked
  • Selection Type: Dom Object
  • Dom Object: document
When you want to get the "value_from_table", you will need to reference
this.data;

In my case I needed the "value_from_table" and use "Set Value" as a TRUE action, and refresh a report.

Links

APEX Javascript documentation

04 December 2013

Invisible Not Null Column

Yesterday I attended John King (@royaltwit) session on Oracle 12c for developers. He gave an overview of all the goodies that are available to us as developers. The whole plugging and unplugging of database, though very cool and exiting, is most likely not very relevant to most developers.
When he discussed the possibility to have invisible columns in a table, it triggered the question: What happens when a NOT NULL column is made invisible? To see what would happen, I set up a small test case on my Oracle 12c database.
SQL> select banner
  2    from v$version
  3  /

BANNER
--------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
First create a table, two columns - both NOT NULL, and some data.
SQL> create table t
  2  (id   number not null
  3  ,text varchar2(10) not null
  4  )
  5  /

Table created.

SQL> insert into t values (1, 'Hello')
  2  /

1 row created.

SQL> insert into t values (2, 'World')
  2  /

1 row created.

SQL> commit;

Commit complete.
Next to change the TEXT column to be invisible.
SQL> alter table t modify (text invisible)
  2  /

Table altered.

SQL> desc t
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID        NOT NULL NUMBER

SQL> select * from t
  2  /

 ID
----------
  1
  2
The data that we entered is still there, or at least the ID values are still there.
So the column is now invisible, what if we try to add new data into it?
SQL> insert into t values (3)
  2  /
insert into t values (3)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T"."TEXT")

Even though you can't see the column, the NOT NULL constraint is still enforced.
When you name the column explicitly, you can still do DML on it.
SQL> update t 
  2  set text = upper(text)
  3  /

2 rows updated.
But not like this
SQL> insert into t values (3, 'Goodbye');
insert into t values (3, 'Goodbye')
            *
ERROR at line 1:
ORA-00913: too many values
This following is still valid, and again shows that naming the columns in an INSERT statement is a good practice.
SQL> insert into t (id, text) values (3, 'Goodbye');

1 row created.

20 November 2013

APEX Tree based on Pipelined Table Function

Representing hierarchical data in an APEX Tree is fairly straight forward, the wizard to create a tree region assists you with that. But when you need to show data from multiple tables, you will need to provide your own query. In a previous blogpost I have written how to achieve that.
It is also possible to create a Pipelined Table Function which can be used a the basis of the APEX Tree. In this blogpost I will show you how to do that.

18 November 2013

PIVOT and UNPIVOT

The PIVOT and UNPIVOT operators were introduced in Oracle 11g and the other day I helped out a friend using these operators.
He wanted the data in the columns to be "shifted to the left". To explain a little more, take a look at the following data:
        ID V1    V2    V3    V4    V5
---------- ----- ----- ----- ----- -----
         1 a     b                 e
         2 aaa   bbbb  cccc
         3             a     b     e
         4 a           c           e
         5       b           d
Above is the data as it appeared in the table.
The requirement was to shift the data to the left like the data below:
        ID C1    C2    C3    C4    C5
---------- ----- ----- ----- ----- -----
         1 a     b     e
         2 aaa   bbbb  cccc
         3 a     b     e
         4 a     c     e
         5 b     d

The data in the columns is moved over one or more columns to the left.

15 November 2013

Tree with Checkboxes: Save the Data - JS Array to PL/SQL

In two previous blogposts I described a method to transform a regular APEX Tree component to a Tree component with checkboxes. The second blogpost described a way to retrieve the data from the database using JSON and AJAX and check the appropriate checkboxes. In this last part of the series on Tree with Checkboxes I will describe a method to save the checked values to the database.