When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.

Meta characters are the characters in a programming language that are interpreted by the programming language to have specific meanings within the language itself.

A short introduction on Oracle Apex: 
Oracle Application Express is mainly used to develop Web based applications with SQL and PL/SQL. The Web-based applications can be developed using a web browser which can easily be deployed for any desktop or mobile applications. There are various version that are been released by Oracle. In recent release on May 2015 they released APEX version 5. Oracle recommends that Oracle APEX to be used on latest web browsers available to have a good experience.

Below are the few listed browsers that oracle suggests:

• Mozilla Firefox 35
• Google Chrome 40
• Apple Safari 7
• Microsoft Internet Explorer 9

 
We can also use the earlier versions of the web browsers by using the legacy themes.

To access full documentation on Oracle APEX, you can open the link from by clicking on the link here. 

In this article we will see how we can upgrade the existing APEX version to Oracle Application Express Release 5.0.1 version. You can also see my other articles on upgrading APEX version from version 3 to 4 on 11g.
 
Here my DB version is 11g Release 11.2.0.1.0 where my current APEX version is 4.2.6.00.03:
 

Connected to Oracle Database 11g Release 11.2.0.1.0 
Connected as SYS


SQL> SELECT 

Select all Open in new window

…

Elementary operating system is a very pretty, high speed, lightweight Linux operating system for 32 and 64-bit x86 computers. This OS is built on an Ubuntu core. To install elementary OS we need to download the ISO file from its website i.e. https://elementary.io/
If you scroll down on the website you will see like below, to download click on Download button as shown below:


Now you can choose 32 0r 64 bit ISO file:


Now open your virtual machine box to create our new VM.
Click on New button to start:

Provide the name of the VM you want to create, I used as “elementaryOS”.  Now click on next button and proceed further. 
Select the memory size and click on next.

Click on create.

Click next.

Keep as default and click next and then choose the directory where you want the VM to be installed. Finally click on create button. 

Now click on the setting gear icon to map the iso file. 



Now “Ok” button.  After all the basic settings start the VM to install the OS. 


Now click on “Install elementary”.

Click on continue. 

Click Install Now and proceed with default selection including location as below. 

Select the Keyboard type you want to choose I am going with the default option with US English. 

…

I got a requirement to export the table data into CSV file. The major issue was like the column values from the table needed to be transposed and used as a header on the flat file. The challenge was that the column values were more than 30 characters and can reach up to 4000 characters.
 
I am using the sample table to simulate the issue and to check how we can resolve this problem.
 
Here is my sample table:


Create the sample table:
 

create table MY_SURVEY_RESP
(
SURVEY_REQUEST_NAME VARCHAR2(100),
QUESTION_NAME	VARCHAR2(4000),
RESPONDENT	VARCHAR2(100),
RESPONSE  VARCHAR2(100)
);

Select all Open in new window

To insert some sample data let's execute the below PLSQL block:
 

BEGIN
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R1 Compliance narrative form'
                ,'USER-1'
                ,'');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'R1 Compliance narrative form'
                ,'USER-1'
                ,'');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'R1 Evidence List'
                ,'USER-1'
                ,'Registered an evidence');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R1 Evidence List'
                ,'USER-1'
                ,'Registered an evidence');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
            

Select all Open in new window

…

This article started out as an answer to a question asked here on EE on why someone would want to use ETL ("Extract, Transform, Load") tools rather than writing application code or using tools native to the database platforms. My response and this article are written from the perspective of a database guy.

I've seen a few ETL tools like the open source Talend; but the only tool I've actually used in a production environment is Informatica so that tool heavily influences my perception. I never became an expert with Informatica but I got to work with some people that were; and, having survived, I'm glad for the experience.

First a few things that I turned out to LOVE.
 

1. Self documenting Data Flow. Trying to answer the question "Where did you get this piece of information?" can be difficult (and that's being kind) with scripts, programs, stored procedures and so on. However, tracing through a workflow is easy. Of course, if the processing is complex with lots of data sources and many transformations it may still take awhile to decipher it all; but the flow itself is still essentially just connecting the dots.
2. Semi-agnostic processing. I absolutely detest platform agnostic development. BUT... tools like Informatica have a nice compromise to them. If you change platforms of your data sources, you must rewrite your source qualifiers, but... after that you're essentially done. So, your inputs can use SQL syntax, stored procedures,

…

Web application components that can be published and used on the Internet are called web services. Here we will see how we can use Oracle PLSQL to access web services.

In Oracle we have a package called UTL_HTTP. By using this package we can call web services using a POST method and get a response from it. For this example I am using the  web service available on www.w3schools.com for converting Celsius to Fahrenheit: http://www.w3schools.com/webservices/tempconvert.asmx?op=CelsiusToFahrenheit

We will be sending the POST method using UTL_HTTP package to the above URL for any value as Celsius and then that value gets converted and responds back to use with Fahrenheit value. So the conversion will be happening on the web service.

Let us first check manually using a browser for any values that can be converted. When we open the URL to the service we will see something as shown below:

Let's put 10 as input to the parametrer value for Celsius and check what output we are getting:

After giving the input click on Invoke button to get the response as like below:

So from the above response we can see that 10 Celsius converted as Fahrenheit with a value of 50.

<string xmlns="http://www.w3schols.com/webservices/">50</string>

Select all Open in new window

…

This article shows how to upgrade from APEX version 3.2.1.00.10 to 4.2.6.00.03 on Oracle Database 11g Release 11.2.0.1.0.
First of all why we need to upgrade, the default version provided by Oracle doesn't support few of the features which is later version they improved the PDF printing options , Mobile application builder and many more.

The details on the version changes can be retrieved from the below link:

• http://en.wikipedia.org/wiki/Oracle_Application_Express

By upgrading the APEX version we are not upgrading the Oracle database, this will remain as it is but all the latest options to build an small application can be done having more features on upgrading the application side. This is not make any significance of performance improvement lastly this supports HTML 5 too and the basic themes are also present, where as we can also configure it. 

By default APEX version 3  comes when we install Oracle database from 10g onwards.  Once we have it installed we need to unlock the "anonymous " user and check if our HTTP port by executing the below SQL :
 

SQL> select dbms_xdb.getHTTPPort from dual;

Select all Open in new window

Now if we see any result then fine else we can set the HTTP port by executing the below code:
 

EXEC dbms_xdb.sethttpport(8082);

Select all Open in new window

Now if we open the URL on our local we will see the below screen for login:

Initially the screen looks as like above. To check the version details we can login to our database to verify :

Login as sys user and check:

Select all Open in new window

…

I'm working on giving a course in Oracle advanced SQL. One of the topics is the MODEL clause. I needed documentation to explain this clause, so I made an article for this.

This clause allows you to use your data like an array, do calculations on that array and then return the calculated data.  This sounds a lot like working with a spreadsheet.

In this part I I'll be looking at the model clause with a single dimension.
 

Prerequisites: A running database, data and a user.

To get everything out of this, I installed my very first 12c Oracle enterprise database with DBCA. It's a single instance (non-plugged) enterprise edition 12.1.0.1 database on a Windows Server 2008 machine named TESTU12. As data I used the separate samples download from oracle.com that contains shemas hr, pm, sh, oe, ix and bi. I created user TEST with the DBA role and create session granted. (It's for testing so the DBA role is least troublesome). For testing I'm using SQLplus.
 

The model clause syntax

Syntactically the model clause sits behind the group by clause in the query block:
 
The model clause itself has the MODEL keyword, three optional parts and the main model section:

The main model section has two mandatory sections: the model column clause and the model rules clause:
The model columns clauses has this definition:
and the model rules clause is like so :

Minimal select statement with model clause

…

You have a query that takes too long and you want help to analyze the execution plan?

Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if we have a performance issue, this probably means that the estimation is wrong. I prefer SQL Monitoring when we have Tuning Pack, or the cursor plan with row-source statistics in all other cases. If you post it in a forum, don't forget to keep it formatted or it's impossible to read.

Here are the two ways I prefer to get an execution plan, depending on whether you have the tuning pack licence or not.
 

Method 1 - Without Tuning Pack

I set the sqlplus environment to nicely spool to text file and set the STATISTICS_LEVEL to ALL in order to gather plan execution statistics:
 
 

set pagesize 10000 linesize 300 trimspool on serveroutput off
alter session set statistics_level=all;

Select all Open in new window

 
Then I execute the query. Don't forget to set the current_schema to the user that executes the statement, and to bind variables:
 
 

-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

Select all Open in new window

 
Finally I get the execution plan to a text file:
 
 

spool plan.txt
select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds +cost'));
spool off

Select all Open in new window

 
The plan will have both the estimations (E-Rows) and the actual number of rows (A-Rows) from the last execution. Note that if it is a parallel query statement, you must omit the 'last' in the format or you will have statistics only for the coordinator process.
 

Method 2 - With Tuning Pack

When you have tuning pack, you have access to the great SQL monitoring feature.
 

Select all Open in new window

…