wpg_docload.download_file : mime type not recognized by client

For a project we are currently working on, we needed to generate, and send a Word 2010 document to the client. The document was generated by a great PL/SQL document generation tool called Doxxy, and was sent to the client using the wpg_docload package. This is a standard Oracle pl/sql package that can be used to download files, BLOBs and BFILEs.

Before the download, we set the Content-type in the http header as follows :

owa_util.mime_header('application/vnd.openxmlformats-officedocument.wordprocessingml.document',FALSE);

When sending the document to the client, we got the following popup in our browser :

Image

So it looked like our browser didn’t recognized that this was an Word 2010 document.

Looking at the response header, using Firebug, we got the following result :

Image

Somehow the content type for Word 2010 was overwritten to text/html; charset=utf-8.

So, time for the good old trial and error approach, which, after a while, paid off.

Before setting the response header to : owa_util.mime_header(‘….’,FALSE); we need to issue the following commands :

htp.flush();
htp.init();

Now the code looks like this  :

-- first clear the header
 htp.flush;
 htp.init;
 -- set up HTTP header
 owa_util.mime_header('application/vnd.openxmlformats-officedocument.wordprocessingml.document', FALSE);
 -- set the size so the browser knows how much to download
 htp.p('Content-length: ' || DBMS_LOB.getlength(v_blob));
 -- the filename will be used by the browser if the users does a save as
 htp.p('Content-Disposition:attachment; filename="'||nvl(v_filename,'export')||v_ext||'"');
 -- Set COOKIE (for javascript download plugin)
 htp.p('Set-Cookie: fileDownload=true; path=/');
 -- close the headers
 owa_util.http_header_close;
 -- download the BLOB
 wpg_docload.download_file(v_blob);

After adding these 2 lines, we got the correct mime type :

Image

Many thanks to Willem Albert and Bjorn Fraeys for delivering the content for this blog !

Watch out with function result cache based on data dictionary views

Result cache is a powerful tool to gain performance in PL/SQL.
There are many examples on the internet that proves this, e.g. these articles on All things Oracle:
Result Cache(1)
Result Cache(2)

But I’m not going to talk about performance.
This article is some kind of warning.

First I’ll show you how result cache works on a normal view.
I’ll create a table, a view on this table and a function that counts the rows in the view.

SQL> create table x (field1 varchar2(1), field2 number(1));

Table created.

SQL> create or replace view vie_x as select * from x;

View created.

SQL> CREATE OR REPLACE FUNCTION vie_x_rowcount(p_field1 IN VARCHAR2)
RETURN NUMBER RESULT_CACHE
IS
   l_return NUMBER;
BEGIN
   SELECT count(*)
     INTO l_return
     FROM vie_x
     WHERE field1 = p_field1;

   RETURN l_return;

END vie_x_rowcount;
/

Function created.

SQL> insert into x(field1, field2) values('x', 1);

1 row created.

SQL> commit;

Commit complete.

These are the statistics for the result cache, just to show you we’re starting without any caching.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       0
Find Count		               0
Invalidation Count	           0

When we execute the function, the statistics show that there’s an entry created in the cache.

SQL> select vie_x_rowcount('x') from dual;

VIE_X_ROWCOUNT('X')
-------------------
		  1

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               0
Invalidation Count	           0

When we execute the same code again, we’ll get the same result and the statistics show us that the result is found in the cache.
Good job Oracle!

SQL> select vie_x_rowcount('x') from dual;

VIE_X_ROWCOUNT('X')
-------------------
		  1

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               1
Invalidation Count	           0

Let’s insert a new row in the table.
This time the statistics show us that the cache is “invalidated”, meaning the function has to be executed again to return the correct value.

SQL> insert into x values('x', 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               1
Invalidation Count	           1

And the expected result…

SQL> select vie_x_rowcount('x') from dual;

VIE_X_ROWCOUNT('X')
-------------------
		  2

The Oracle database has its own data dictionary, a set of tables where it stores all information about the database and what’s in it.
Data of these tables are available through views, data dictionary views.
In the following example I’ll use the data dictionary view that holds the information on columns.
I created a function that returns the number of columns for a certain table.

SQL> CREATE OR REPLACE FUNCTION number_of_columns(p_table_name VARCHAR2)
RETURN NUMBER RESULT_CACHE
IS

   l_return NUMBER;

BEGIN

   SELECT count(*)
     INTO l_return
     FROM user_tab_columns
    WHERE table_name = p_table_name;

   RETURN l_return;

END number_of_columns;
/

Function created.

To make sure we’ll start with a clean cache, I’ll flush it using the dbms_result_cache.flush procedure.

SQL> execute dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       0
Find Count		               0
Invalidation Count	           0

When we execute the function, we’ll get the expected result: the function is executed and a cache entry is created.

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     2

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               0
Invalidation Count	           0

We can execute it again and see that the return value is retrieved from the cache.

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     2

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               1
Invalidation Count	           0

Let’s add a column to the table.
This should add a new row in a data dictionary table and thus in the data dictionary view we use in our function.

SQL> alter table x add (field3 date);

Table altered.

SQL> desc x
 Name					   Null?    Type
 ------------------------- -------- ----------------------------
 FIELD1 					        VARCHAR2(1)
 FIELD2 					        NUMBER(1)
 FIELD3 					        DATE

Now execute the function again.
And the result is…

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     2

Not what we expected!
When we take a look at the result cache statistics, it shows that the cache wasn’t invalidated and the result was retrieved from the result cache.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               2
Invalidation Count	           0

When we flush the cash and execute the function again, we’ll get the correct result cache.

SQL> execute dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     3

So, it seems that the result cache isn’t invalidated on data dictionary tables.
And indeed this is what I found in the Oracle documentation:

You cannot cache results when the following objects or functions are in a query:

  • Temporary tables and tables in the SYS or SYSTEM schemas

Accessing SSL encrypted websites using UTL_HTTP and ORAPKI command line utility

Introduction
In an earlier post, I explained the purpose and usage of the Oracle Wallet Manager. This explanation assumed that the user has the ability to use the graphical user interface to execute the OWM program included with each DBMS installation.

However, sometimes there is no graphical user interface available on the server, and the user is limited to SSH access.
Additionally, the disadvantage of using a user interface tool is that it is not scriptable and re-runnable on other server/environments.

For just that reason, Oracle also provides a command line utility to perform the same tasks, called ORAPKI.
This post will show you how to perform the same tasks as we did in the previous post, using only the command line.

Step 1: creating a wallet:
The base command to create a new, empty wallet is:

orapki wallet create -wallet <wallet name or path>

The name of the wallet will be used as a folder within your home folder by default. If you prefer to use a specific folder, the full path to the folder can be used as wallet name as well. Make sure the Oracle user has permission to write to this folder though.

When any command on a wallet is executed, a prompt will be given to enter the wallet password. If the command is to be used in a script, it is better to include the password right away in the command. This can be done by appending -pwd <password> to any command.

orapki wallet create –wallet testwallet –pwd test1234

Step 2: display contents
To show the contents of any wallet, use the display command.

You will see that a number of trusted certificates are included in your wallet after it has been created, just like when it was created through the GUI.

[oracle@myorcl12c ~]$ orapki wallet display -wallet testwallet -pwd test1234

Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Step 3: import certificates
Now we will import previously downloaded certificates into our wallet. (refer to the previous post for details on how to obtain such files)

Firstly I have stored 3 certificate files on the server:
/home/oracle/Certificates/BuiltinObjectToken:EquifaxSecureCA
/home/oracle/Certificates/GoogleInternetAuthority
/home/oracle/Certificates/*.google.be

Then following commands will do the import:

[oracle@myorcl12c ~]$ orapki wallet add -wallet testwallet -trusted_cert -cert /home/oracle/Certificates/BuiltinObjectToken:EquifaxSecureCA -pwd test1234
[oracle@myorcl12c ~]$ orapki wallet add -wallet testwallet -trusted_cert -cert /home/oracle/Certificates/GoogleInternetAuthority -pwd test1234
[oracle@myorcl12c ~]$ orapki wallet add -wallet testwallet -trusted_cert -cert /home/oracle/Certificates/*.google.be -pwd test1234
[oracle@myorcl12c ~]$ orapki wallet display -wallet testwallet -pwd test1234

Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=Google Internet Authority,O=Google Inc,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject:        CN=*.google.be,O=Google Inc,L=Mountain View,ST=California,C=US
Subject:        OU=Equifax Secure Certificate Authority,O=Equifax,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

When adding the Wallet reference to your PL/SQL code, the folder /home/Oracle/testwallet should be used for the example above.

Eg:

[oracle@myorcl12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 27 14:11:43 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lo_req  UTL_HTTP.req;
3    lo_resp UTL_HTTP.resp;
4  BEGIN
5    UTL_HTTP.SET_WALLET ('file:/home/oracle/Wallet/','test1234');
6    lo_req := UTL_HTTP.begin_request('https://www.google.com');
7    lo_resp := UTL_HTTP.get_response(lo_req);
8    dbms_output.put_line(lo_resp.status_code);
9    UTL_HTTP.end_response(lo_resp);
10  END;
11  /

200
PL/SQL procedure successfully completed.
SQL>

Step 4: clear wallet
As a final step, use following command to clear the wallet.

orapki wallet remove -wallet testwallet -trusted_cert_all -pwd test1234

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 :-)

5 neat little features of the 12C database to remember

In this post, I’d like to introduce 5 of the many new features Oracle 12C brings to us, database developer’s.
Of course this blog would be to long to explain them all in detail, so I will stick
to a small introduction.

  1. Generating a primary key without triggers, using nextval or identity
    In 12C, you are now able to use sequence.nextval or the new keyword ‘identity’ as default values.
    The ‘identity’ keyword will generate the value max(id)+1 for your primary key. So now you don’t need to create triggers anymore, when generating PK’s with a sequence.
    And problems with sequences that are not in sync, when moving/copying tables to another schema/database, can be avoided by using the ‘identity’ keyword.
    Example PK row declaration :
    id_pers         number default person_seq.nextval primary key;
    id_pers         number generated as identity;
  2. Accessible key word : define which code can call your function/procedure.
    One of the major problems of PL/SQL is, when developing a lot of packages/procedures/functions, in the end there is no telling who is called by who. This problem can now be answered by ‘white listing’. This means that, on creation, you are telling the package/function/procedure/type by whom it is accessible, or may be used.
    The accessible by clause takes packages/functions/procedures/triggers as accessor clause.
    Example white listing :
    - create procedure get_sales_data accessible by (my_sales_proc)…
    – create procedure get_sales_data accessible by (my_after_update_trigger)
    - create package my_package accessible by (my_other_package)…When the object is not accessible, following error will be thrown during compilation, or at runtime, in case of an anonymous PL/SQL block :PLS-00904: insufficient privilege to access object MY_PACKAGE.MY_PROCEDURE
  3. Temporal Validity of a row
    Sometimes rows in a table are valid or not, depending on a timeframe. For instance a subscription for a magazine, may only be valid for a year. Adding this validity to a row goes as follows :

     create table subscriptions
     ( person_id             number,
     subscription_id                 number,
     person_name                   varchar2(500),
     subscr_start_date   date,
     subscr_end_date              date,
     period for valid(subscr_start_date , subscr_end_date)
    )
    

    Now with following query we can select the ‘valid’ subscriptions :

     select * from subscriptions
     as of period for valid sysdate;
    
  4. New PL/SQL Package UTL_CALL_STACK
    The UTL_CALL_STACK package provides subprograms to return the current call stack for a PL/SQL program. This could already be done by DBMS_UTILITY.FORMAT_CALL_STACK, but this new package returns this information in a more structured way, and includes the depth of the call (calling level) and the names of the subprograms.
    This will make this information more usable in code. Related to this subject, 2 new directives are added in 12c, next to $$PLSQL_LINE and $$PLSQL_UNIT that already existed).
    – $$PLSQL_OWNER
    – $$PLSQL_TYPE

       dbms_output_put.line("Owner of this package is "+$$PLSQL_OWNER);
    

    Will print : Owner of this package is SCOTT

  5. An Invoker’s Rights Function Can Be Result Cached
    Caching results of a PL/SQL function already exists in 11g. Basically what happens is that, for a certain function, you define that the result, for given parameters should be cached in memory.
    So first time function getPerson(123) is executed, the data is fetched from the database, second time the function is called with parameter ‘123’, the result is fetched from the cache in memory, resulting in a better performance.
    Whenever a DML statement is executed on the table(s) used in that function, the cache is automatically cleared, causing the next call to return the new data. (Since 11G rel. 2, Oracle manages these dependencies himself.)
    So in our case, Oracle caches the result’s of function getPerson() for every key it is called with.
    Through Oracle Database 11g Release 2 (11.2), only definer’s rights PL/SQL functions could be result cached. Now in 12c, the identity of the invoker is implicitly added to that key.

As already mentioned, the possibilities of these new features go way beyond what I describe here. But hopefully it’s a start to a few experiments on your side !

More info can be found at http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm

Accessing SSL encrypted websites using UTL_HTTP and Oracle Wallet Manager

Introduction
If you have used the UTL_HTTP package in PL/SQL to call upon external web pages or services, you might have seen following error message come by:
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1130
ORA-29024: Certificate validation failure

It indicates that the web site you are trying to access is in fact SSL encrypted and requires a valid certificate to read.
Most modern browsers download this automatically when visiting any encrypted page, but to do this in a PL/SQL procedure, a couple of manual steps need to be taken.

This is where Oracle Wallet Manager comes in. It is shipped with the DBMS software and can typically be found in the $ORACLE_HOME/bin folder.
What this program does, is to facilitate the process of storing certificates in a single file in PKCS #12 format, called a Wallet.
Next, it suffices to simply add a reference to the wallet in your PL/SQL code to be able to use these certificates when accessing SSL encrypted content.

Sample case
Let’s take the following piece of PL/SQL code:

DECLARE
 lo_req  UTL_HTTP.req;
 lo_resp UTL_HTTP.resp;
BEGIN
 lo_req := UTL_HTTP.begin_request('http://www.google.be');
 lo_resp := UTL_HTTP.get_response(lo_req);
 dbms_output.put_line(lo_resp.status_code);
 UTL_HTTP.end_response(lo_resp);
END;
/

This will output the status code “200”. This implies the page returned HTTP 200: OK, indicating the request succeeded.

If you change the URL from http://www.google.be to https://www.google.be, the same command would raise exception “ORA-29024 Certificate validation failure” as mentioned earlier.

Wallet Manager
From the command line, start the wallet manager by simply entering ‘owm’ after having set the correct Oracle environment parameters.
In Windows the program can be found in the start menu under /<oracle version folder>/Integrated Management Tools/Wallet Manager.

Wallet_01
First, let’s create a new wallet.
From the menu, select Wallet, New…

A prompt is shown to enter a password for the Wallet. For this example, we’ll use “test1234”.

Wallet_02

After entering the password, a prompt asks to create a new certificate request, which we will not do for now.

By default, a number of trusted certificates are created within the wallet. These can be seen in the overview screen.

Wallet_03
Now, let’s focus on our google.be website.

First thing to do, is to export the actual certificate from this website, together with the rest of the trust chain. The easiest way to do this is by using a regular browser application. Following screenshots are from Firefox, but a similar approach can be taken with Internet explorer or Chrome.

Go to the website, and click on the padlock in front of the URL.
Wallet_04

Click on “More information” and then “View certificate”. In the Details tab, export each of the certificates in the hierarchy (3 in this case).
Wallet_05

Then go to the Wallet manager, and import all 3 trusted certificates.
Wallet_06

Finally, save the wallet to a desired location. Make sure it’s a folder that can be accessed by the Oracle user, and is preferably not accessible by unauthorized users!
In this example, /home/oracle/Wallet/ is chosen.

Wallet_07

Wallet_08

Now we can return to our SQL*plus session. One extra statement should be added to the script:

UTL_HTTP.SET_WALLET (‘file:<path to the wallet folder, don’t include the filename!>’,<the wallet password>);

This statement should be executed before the begin_request step.

Here’s the full output of the script:

[oracle@myorcl12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 27 14:11:43 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lo_req  UTL_HTTP.req;
3    lo_resp UTL_HTTP.resp;
4  BEGIN
5    UTL_HTTP.SET_WALLET ('file:/home/oracle/Wallet/','test1234');
6    lo_req := UTL_HTTP.begin_request('https://www.google.com');
7    lo_resp := UTL_HTTP.get_response(lo_req);
8    dbms_output.put_line(lo_resp.status_code);
9    UTL_HTTP.end_response(lo_resp);
10  END;
11  /

200
PL/SQL procedure successfully completed.
SQL>

In a follow up post, I will explain how to achieve the same result using the orapki command line utility instead of Wallet manager.

Red Gate Deployment Suite for Oracle, a valuable component in your APEX ECO-System

When working with APEX, you also need a number of tools to improve the efficiency of your work. It isn’t enough to just have the APEX IDE at your disposal. Besides the APEX IDE for the application development, you may use a bunch of other tools. This is what I mean by the APEX ECO-System.

For the PL/SQL development and creation of the objects, such as tables, views, synonyms, sequences, … you may use SQL Developer. The DML scripts with the code for creating those objects may be generated by SQL Datamodeler. This tool is used to design the data model, which offers a graphical representation of the relationships between tables and offers the possibility to generate database objects automatically on our database.  Both tools are part of the Oracle Database Development tools, just like APEX.

Once the objects are modeled and generated on the database, you can start with the development of the application in APEX. After some time, when a first version of the application is ready, it can be deployed on a test environment. Without the aid of an external tool, it’s necessary to make a script of each package, trigger, sequence, synonym, table, view,… in order to create them on the test environment. And when you start with change requests and enhancements, it becomes even more complex!

After the end-user has tested the application and given his feedback, you normally will have to change your packages, tables,… or you will have to add some new functionalities, or you’ll have to change the existing ones. Without the aid of an external tool, you’ll have to make a script of each change you make, in order to deploy the changes on test as well. This is very time consuming and the risk of making mistakes is very big.

In the past, in my personal experience, it took me lots of hours to make a script for each column definition, table, relationship, package, trigger, view,… which has changed during the development cycle. It was very annoying and especially when you forgot one… It’s very time-consuming to find differences between the development and test environment.
But, since I got in touch with Red Gate, all those problems are gone!

Red Gate’s Deployment Suite for Oracle contains a tool that allows you to deploy schema objects and compare schema’s (Schema Compare for Oracle). But that’s not all. Unlike other tools such as SQL Developer, which  only gives the possibility to compare schema’s in a limited way, the Red Gate Deployment Suite allows you to deploy Data from one schema to another as well (Data Compare for Oracle). And last but not least, since the 12th of March, Red Gate extended their product portfolio. They also provide a tool for Source Control (=Version Control) of your database code! This is done by Source Control for Oracle!

A small overview…

Schema Compare for Oracle, allows you to make a full installation script of your database objects. This is very useful because you don’t have to worry about the question if all modifications are scripted, neither about the possible dependencies between the different objects. You are sure that everything on your development environment is scripted in a good way and, when you choose to deploy everything on the test environment, you are sure that it is executed in the right sequence.  Everything which has to do with the deployment of the schema objects is handled with the Red Gate Schema Compare tool, which is included in the Deployment Suite.

While Schema Compare for Oracle is used for objects, Data Compare for Oracle will take care of all your data. This is very useful when you have a test environment with a lot of reference data, which is entered by the Business. Don’t you recognize the situation where your end users have entered data while testing the application in a test environment assuming that this data would also be available in the production environment? This tool allows you to compare data on different schemas, and to deploy changes from the one to the other.

Very recently, Red Gate added an extra tool to his portfolio: Source control for Oracle. It all started with a live lab at KScope12 where every attendee could contribute to the first prototypes. Read more about it here. Less than a year later, a first version is downloadable. Since it is completely new, there is not yet that much experience with it.

Since iAdvise is an official partner of Red Gate, we’ve had the opportunity to test the tool before its official release. After a few months of testing, we can tell you that this is the first mature tool which really helps developers to manage their database code!

The whole idea is to give developers a tool to put schema objects on an SVN repository from the database and to pull those objects from the SVN repository, back to another database. If you ever tried doing this manually, you know that this can take up a lot of your valuable time. Source Control for Oracle will take the manual labor out of the picture and put the files on the SVN for you. While doing this, Source Control for Oracle will notice if an object already exists on SVN and the tool will ask the user what he needs to do with it. This will decrease the chances of anyone overwriting your work. You can also add a comment when you push something to the SVN. You can always read back and see what was changed, why it was changed, when it was changed, and by whom it was changed. In this way, Source Control for Oracle provides a whole version control system for database code.

One of the nice features of the tool is that when you define all your schemas in a SVN repository, Source control for Oracle will compare your schemas in the database automatically with the definitions in SVN, when you start the tool.

As you can read, the Deployment Suite for Oracle isn’t just the first database tool you meet; it’s a lot more than that! At iAdvise, we’re convinced about the added value and we believe this tool will help a lot of people in their needs. If you aren’t yet convinced about the tools, then take a minute to look at the website of Red Gate and check out the videos and testimonials of other users, I’m sure you’ll change of thoughts!

Interested to see & learn more about iAdvise?
Follow us on twitter!

@iadvise_live

http://www.iadvise.eu

All things Oracle: Generating XML from SQL

Did you ever needed your table data in an XML format?
Well, it isn’t that hard…  Oracle provides several ways to generate XML, in both SQL and PL/SQL.

I wrote an article on All Things Oracle in which I explain step by step how you can generate XML from table data.
In this first part I show you some SQL functions which are easy to use.  Check it out!