Another 5 neat 12c features for Oracle developers

In this post I will put 5 other new 12c features in the spotlight (in addition to the features of a previous post), that really makes the 12c an improvement against the previous versions of the Oracle database.

To get this result I listed up all the major new features and wanted to know my top 5 features that would make my life easier(as a developer), excluding the features from the previous  post (I certainly would have added the sequence modification(feature 1)), when doing development on an Oracle database.

  1. Top end query -> I really like this feature and I’m still wondering why it took Oracle so long before creating it. It is something I could have used a lot in the past, but instead I had to create far too complicated, not as nice readable queries to achieve this. How does it work? Well it’s very easy and it’s readable and can be used in a wide variety of cases. Some examples:
    Only get the first 3 rows:
    select * from X order by id
    fetch first 3 rows only;

    Skip the first 3 rows and get the next 3 rows:

    select * from X order by id
    offset 3 rows fetch next 3 rows only;

    Get the first 50% of records

    select * from X order by id
    fetch first 50 percent rows only;

    Get the first 3 rows together with the records equal to these department id’s

    select * from emp order by deptno
    fetch first 3 rows with ties;

    If you want the capture the last rows, you can obviously change ‘first’ with ‘last’…

  2. In the 12c database the use of 32767 characters for a VARCHAR2 in SQL is now available instead of the maximum of 4000(this is also the case for RAW and nvarchar2).
    We all have been waiting a long time for this one and before we had to use the clob datatype.
    But beware this is not an out of the box feature, you will have to execute the lines below before this is enabled :
    shutdown immediate
    startup upgrade
    alter system set max_string_size=EXTENDED scope=both;
    @<ORACLE_HOME>/rdbms/admin/utl32k.sql
    Shutdown immediate
    Startup

    More info can be found on: http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10321.htm

  3. The invisible column is a feature of which I was wondering where I could use it for.
    Well it could be handy when you are adding a column to your table, but you don’t want any existing code to be impacted by it.
    Another case where it could be useful, is when using audit columns.  Columns as the creation_dt, update_dt, user_creation and user_update will only be of any added value when you would like to audit a certain column.
    Packages with inserts, updates, references to this table will not be impacted by the creation of this column.
    On the other hand there is also a risk that you forget that this column is in there, because you have to explicitly call for it (a describe or select * will not show this column). You can create invisible columns like this:

    ALTER TABLE
    ADD  INVISIBLE;

    If you want to make the column visible again, use this:

    ALTER TABLE
    MODIFY VISIBLE;

    In summary it could be handy, but don’t forget this column or it will pollute your table.

  4.  The with clause inline plsql feature is also something that I think is very welcome.
    It will make it possible to create a procedure or function inside your select statement instead of having to create this in a package or function. Oracle also says that this will optimize the performance against having to call a schema procedure/function(I still have to test this).
    A little example:

    WITH
    FUNCTION fnc$_add_one(p_num IN NUMBER) IS
    BEGIN
    RETURN p_num+1;
    END;
    SELECT fnc$_add_one(1)
    FROM DUAL;
  5.  Most of the time I use the ANSI way of writing for a left outer join, but the oracle way of writing left outer joins is still often used by many of the oracle developers.
    But there was one thing that you could do in ANSI, that you couldn’t in the oracle way.  You couldn’t write multiple tables on the left of an outer join, untill12c…
    In 11g and before when coding something like this:
    select *
    from a,b,c
    where a.id = b.id
    and a.id = c.id(+)
    and b.id = c.id2(+);

    This resulted in -> ORA-01417: a table may be outer joined to at most one other table

    In 12c this will work, also the ANSI solution obviously still works both on 12c and on 11g

    select *
    from a
    JOIN b ON (a.id = b.id)
    LEFT OUTER JOIN c ON (a.id = c.id AND b.id = c.id2);

Together with the previous post this makes 10 reasons why you should start to use the Oracle 12c database :-)

Using a database link with heterogeneous services in APEX

Last weeks I ran into some issues with APEX, so let’s take some time to write something about them.

The most recent is about heterogeneous services (also referenced as HS). Here is the case:
We have an Oracle DB instance with some APEX applications and in one of those applications I need data from a MS SQL server database. Not a very good idea, but the set-up couldn’t be changed and both databases can handle it, so I gave it a try.

Our DBA installed heterogeneous services and created a database link between the two databases. Querying the necessary views was pretty easy (I only needed a couple of synonyms for ease of maintenance) and data came in faster than expected.

The next step was to query these views with APEX.
Because I created Oracle views on the views of MS SQL server, this was also very straightforward for APEX.

After a while however, there were issues in other applications and also the APEX builder itself raised errors while uploading files and importing applications. The following error returned:

ORA-02047: cannot join the distributed transaction in progress

, usually accompanied by some other ORA error codes. Not a very descriptive error message. A colleague pointed out to me that it has something to do with database links and heterogeneous services.

So, what is the problem?
APEX starts a new database session for my application. In this session the database link to the MS SQL server database is opened and data is queried. Since APEX constantly changes and re‑uses database sessions (this technique is called session pooling), the database session is released after a while and put back in the session pool until another application needs a new/another session. Because the database link in my session is only used for querying, there is no line of code that closes the database link so it remains open, even when the session is put back into the session pool. When another application takes this session from the pool, there might be issues with specific transactions, like setting a context (e.g. setting NLS parameters), because Oracle wants to perform this operation on both the Oracle database and the linked MS SQL server database. Because an NLS parameter is set when uploading a file, the end user receives the ORA-02047 error.

What can one do to prevent this from happening? There are several solutions for this issue:

  1. Don’t use database links with heterogeneous services. This is the easiest solution but isn’t always possible.
  2. Only access data over a DB link with HS in a package, function, procedure,… and close the DB link at the end of your block of code, which is not always possible either.
  3. Close any open database link, or only the specific one, when you put your session back into the session pool.

For the third solution, something interesting is implemented in APEX 4.1.

At application level, you can define some PL/SQL code that should be executed when initializing or returning a session from/to the session pool.

You can find this section under Shared Components  >  Security Attributes. All the way at the bottom is the section ‘Database Session’:

In earlier versions of APEX, something similar was already available for VPD (Virtual Private Database) but this only allowed execution of code at session initialization. The APEX development team has now expanded this functionality for any PL/SQL in general, so not only VPD-related, and both at session initialization and returning.

For my issue, all I had to do was write some code to close the database link when it was still open. I created a function in my application database schema which returns the number of open database links with a specified name:

CREATE FUNCTION is_dblink_open(dblink  IN VARCHAR2)
RETURN INT
AS
  l_cnt   INT;
BEGIN
  SELECT COUNT(db_link) INTO l_cnt
  FROM v$dblink
  WHERE db_link = dblink;
  RETURN l_cnt;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;

Before you create this function, don’t forget to grant access to v$dblink:

GRANT select ON v_$dblink TO my_app_schema;

Execute with SYS. Note the v_$ instead of v$! (more info: http://goo.gl/euxcs).

All that was left is to use this function in the Database Session Clean Up Code:

DECLARE
  l_dblink VARCHAR2(100) := 'MY_SQL_SRV_DBLINK';
BEGIN
  IF is_dblink_open(l_dblink) > 0 THEN
    DBMS_SESSION.close_database_link(l_dblink);
  END IF;
END;

Or if you want to be sure that all open database links are closed, you could use:

BEGIN
  FOR l_db_link IN ( SELECT db_link AS name FROM v$dblink ) LOOP
    DBMS_SESSION.close_database_link(l_db_link.name);
  END LOOP;
END;

Special thanks to my colleague Jan for pointing out to the HS problem and to Patrick Wolf for helping me on this issue through the Oracle APEX forum (read the full post here: http://goo.gl/vVoeb).

This way, I hope to help out other people too when they run into this issue.

All things Oracle

There’s a new Oracle source available:  All Things Oracle.

The aim of All Things Oracle is to provide a gateway to the wealth of information and material available for Oracle developers and DBAs.
The site brings articles and other resources of Oracle experts.
Just to name a few:

All very experienced experts that will bring interesting articles!

I will also contribute to this site.
My specialities are SQL, PL/SQL, Forms and Forms Modernization, so expect articles on these topics in the near future on All Things Oracle.

Statement restart

Today I explained the “statement restart” problem to a colleague.
Every database developer has to know about this one…

Short: Any statement can be restarted!
This means that when you do one update statement, oracle CAN restart the statement.
Every code in a trigger can be executed multiple times, so watch out with package variables and autonomous transactions in triggers.

Some code to test it:

CREATE TABLE test_trigger(val NUMBER)
/

CREATE OR REPLACE PACKAGE global_var
IS
g_val NUMBER;
END global_var;
/

CREATE OR REPLACE TRIGGER test_trig
BEFORE UPDATE
ON TEST_TRIGGER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
global_var.g_val := global_var.g_val + 1;
END ;
/

BEGIN
global_var.g_val := 0;
END;
/

INSERT
INTO test_trigger
( val
)
VALUES
( 0
)
/

DECLARE
l_val NUMBER;
BEGIN
SELECT val
INTO l_val
FROM test_trigger;

dbms_output.put_line(‘value in package global variable:’||global_var.g_val);
dbms_output.put_line(‘value in table:’||l_val);
END;
/

BEGIN
FOR i IN 1..100000
LOOP
UPDATE test_trigger
SET val = val +1;
END LOOP;
END;
/

DECLARE
l_val NUMBER;
BEGIN
SELECT val
INTO l_val
FROM test_trigger;

dbms_output.put_line(‘value in package global variable:’||global_var.g_val);
dbms_output.put_line(‘value in table:’||l_val);
END;
/

What’s the value of your global variable?

Or like Tom Kyte says: “Triggers or evil!”
More on this topic by Tom Kyte: That old restart problem again

Forms 11G and DB function with result cache

Result cache… a very cool feature in the 11g database.
If you don’t know it, this is a must read: Pl/SQL function result cache in 11g

But also watch out with it in Forms 11g.
It seems there’s a bug.
When compiling a program unit that calls a function with result cache in forms, you’ll get a compile error, which look likes this:

Error 801 at line 7, column 2: internal error[*** ASSERT at file pdw1.c, line 4061; PSDGON missing. Can’t get object number; XNSPC1PTEST__P[7,2]

This is logged on “My Oracle Support” as a bug.
It’s not a forms bug, but a PL/SQL Client bug.
And this “bad” version of the PL/SQL client is used in forms11g(or in general FMW11g).
It works with older and newer versions of the PL/SQL Client(Forms 10g + function result cache should work).
Hopefully there’s a patch soon, so that everybody can use result cache ;-)

ORA-01400 during refresh of MVIEW

 

When trying to refresh a materialized view based on a query that uses a mssql table (using the SqlServer Gateway), I run into the following error:

ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from MSSQLDB1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712

 

After some investigation I found this was caused by a “NOT NULL” constraint on the mview-table.

A small scenario:

STEP 1: Create a Materialized View

SQL> CREATE MATERIALIZED VIEW COUNTY_IS_NULL
2 REFRESH FORCE ON DEMAND
3 AS
4 SELECT a.agreementnum
5 ,a.county county_id
6 ,a.createddate agree_cre_date
7 FROM bmssa.EXU_AGREEMENTTABLE@mssqldb1 a
8 WHERE a.agreementnum = ' E00000001';

 

Materialized view created.

 

STEP 2: View Content of the Materialized View (County_Id is null!)

SQL>
SQL> SELECT * FROM county_is_null;

 

AGREEMENTN COUNTY_ID  AGREE_CRE
---------- ---------- ---------
E00000001 05-JUL-07
 

SQL>
SQL> SELECT NVL(county_id, 0) FROM county_is_null;

NVL(COUNTY
----------
0

 

 STEP 3: Refresh the Materialized View

SQL>
SQL> BEGIN
2 dbms_mview.refresh('COUNTY_IS_NULL');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from MSSQLDB1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 2

 

STEP 4: Disable the NOT NULL Constraint on COUNTY_ID

SQL>
SQL> SELECT constraint_name, search_condition FROM user_constraints c
WHERE c.table_name='COUNTY_IS_NULL';

CONSTRAINT_NAME SEARCH_CONDITION
------------------------------ -------------------------------------------------
SYS_C0029024 "AGREEMENTNUM" IS NOT NULL
SYS_C0029025 "COUNTY_ID" IS NOT NULL
SYS_C0029026 "AGREE_CRE_DATE" IS NOT NULL

 

SQL>
SQL> ALTER TABLE county_is_null MODIFY CONSTRAINT sys_c0029025 DISABLE;
Table altered.

 

STEP 5: Refresh the Materialized View

SQL>
SQL> BEGIN
2 dbms_mview.refresh('COUNTY_IS_NULL');
3 END;
4 /

 

PL/SQL procedure successfully completed.
SQL>

  

No Slide Zone – Tom Kyte

Today was the first time to attend a session in the ‘No Slide Zone’ which is one of the special programs presented at Oracle Open World. During these sessions no slides can be used, in other words this is no typical conference track.

The keywords for a ‘No Slide Zone’-session: ‘discussion, interaction, whiteboarding and more’.

The session I had chosen, ‘Tom Kyte presents 10 new features for Oracle Database 11g’ … and it was fun ;o)

Using different paper notes he mentioned and discussed the 11 different features which are now made available in 11g and explained which problems they solved.

Every time he mentioned a feature and explained the usage and gain of it, he threw away the card which was a really fun way of presenting.

After he had mentioned the 11 new features, the people could ask some questions … and to make sure questions were asked … books regarding 11g were given to the first 12 people asking a question. Everyone in the room had a question … this is a reminder for myself for future presentations and sessions.

So let’s stop talking and give you those features wright ;o):

  • Virtual Columns: ability to use functions instead of creating a new column for calculations => the outcome is beter cardinality which means a beter execution plan
  • Compress Data & MetaData
  • Finer Grained Dependencies => if the signature of a procedure doesn’t change (the input/output parameters), the procedure won’t be invalidated. When columns are added or dropped from tables, no invalidation occurs, if specs are added to packages, no invalidation occurs
  • Compress data for transactional data not only for read-only data => compressioning will work for conventional paths as well (CRUD-methods) which will save space and audit retrieval will be faster
  • Flash Data Archive: enew background process is used, flashback-process which will use the flashback query for data retrieval
  • Partioning is extended and now enables: partionining by virtual columpns; reference-partioning which will give the ability to partition on master-data from a child-table. The child-table doesn’t need to be denormalized anymore, so the attribute of the master-table is available in the detail-table. The last partioning-feature added is interval-partioning which will give you the ability to performed sparsed partioning
  • Encrypted tablespace => store data on block, in the index in clear data and afterwards when the data is written in clear text, the tablespace block will be encrypted
  • Cache more stuff => statement results can be cached; server-result cache which means just-in-time materialized views are used (use the buffer cache or shared pool); pl/Sql function cache which will mark the function as result cache
  • Stand-by database => for logical standby’s datatype support has been extended and for physical standby’s, active standy is now fully supported
  • Real Application Testing => catch the workload of your db 10g release 1 db and run the same workload on your db release 2 database using Real Application Testing

It was an amazing session and these are amazing features which will improve the DB’s performance and maybe, somewhere in the very, very far future DBA’s aren’t needed anymore ;o)