Sponsored

Suppose you have a stored procedure in Oracle database which is using XML as parameter to process the data and you want to execute that stored procedure with XML parameter. For this case below is the example PLSQL block showing that how you can pass XML as a parameter to stored procedure in Oracle.

Follow exactly the steps of PLSQL block and change the XML data with your XML data and change the procedure p_xml_dummy with your stored procedure name. It can be function also method is same.
[code type="SQL"]DECLARE
   v_XML   CLOB;
BEGIN

   DBMS_LOB.CREATETEMPORARY (v_XML, TRUE);

   P_XML_DUMMY (TO_CLOB ('<main>
  <DATA_RECORD>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981/02/20 00:00:00</HIREDATE>
    <SAL>1600</SAL>
    <COMM>160</COMM>
    <DEPTNO>40</DEPTNO>
  </DATA_RECORD>
</main>'));

END;[/code]
Pass xml as parameter to stored procedure in Oracle

It's a good news for bloggers who are using Blogger platform for their blogs that Blogger introduced many beautiful themes on 20th March 2017. These themes are fully responsive and SEO friendly with all new fresh look.

So you can use these blogger templates launched in 2017 for your blogs rather than taking blogger templates from other resources.

Below is the screen shot of Theme section of Blogger from which you can select any theme for your blogger blog.

Free Blogger Templates / Themes 2017

A simple example is given below to insert a long string into CLOB data type in Oracle. You can not insert data directly into CLOB data type, you have to use Empty_Clob() function with returning clause in insert statement then use dbms_lob.write procedure to write the data. Because if you will try to insert directly a long string into CLOB column then you will get ORA-01704: string literal too long error while inserting the data.

Here is the PL SQL block example to insert a long string into a table having CLOB column.

[code type="SQL"]DECLARE
   v_clob   CLOB;
BEGIN
   INSERT INTO A_CLOB_TABLE (srlno, c_data)
        VALUES (123, EMPTY_CLOB ())
     RETURNING c_data
          INTO v_clob;

   DBMS_LOB.write (v_clob,
                   LENGTH ('provide some long string here'),
                   1,
                   'provide some long string here');
   COMMIT;
END;[/code]
Insert long string into clob data type in Oracle

In the previous blog post I have given the example to use Oracle Bulk Collect Using Cursor Rowtype Type Object. Which first creates the cursor then type object of cursor type and then open the cursor, fetching into bulk collect and then finally process them using Forall. But sometimes you may be require to process some data for just one time with a temporary anonymous PLSQL block and you don't want to write too much code to perform that task. For this kind of scenarios you can use Select Bulk Collect into option to perform any task quickly without wasting too much time to write code.

Below is the example of PLSQL block which will take the records from EMP table and update the BONUS table and also prints on the screen.
[code type="SQL"]SET SEVEROUTPUT ON;
DECLARE
   TYPE t_emp IS TABLE OF emp%ROWTYPE;

   e_rec   t_emp;
BEGIN
   SELECT *
     BULK COLLECT INTO e_rec
     FROM emp;

FORALL i IN e_rec.FIRST .. e_rec.LAST
     update bonus set amount = e_rec(i).sal * 15 / 100
        where empno = e_rec(i).empno;

Commit;
     
   FOR i IN e_rec.FIRST .. e_rec.LAST
   LOOP
      DBMS_OUTPUT.
       put_line (
            'Bonus Updated For Employee: '
         || e_rec (i).ename
         || ' '
         || 'Bonus: '
         || e_rec(i).sal * 15 / 100);
   END LOOP;
END;[/code]
Select bulk collect into example Oracle

In this post I am giving an example to perform an insert into using Oracle Bulk Collect and FORALL command. Created a PL SQL function which will take data from EMP table using a cursor then creates type array variable of that cursor row type and then it bulk collect and insert into BONUS table using FORALL.

Oracle Bulk Collect, Forall and cursor rowtype example

Below is the example:
[code type="SQL"]CREATE OR REPLACE FUNCTION f_currowtype
   RETURN BOOLEAN
IS
   CURSOR c_emp
   IS
      SELECT empno,
             ename,
             job,
             sal
        FROM emp;

   TYPE t_emp IS TABLE OF c_emp%ROWTYPE;

   e_rec   t_emp;
BEGIN
   OPEN c_emp;

   FETCH c_emp
   BULK COLLECT INTO e_rec;

   CLOSE c_emp;

   FORALL i IN e_rec.FIRST .. e_rec.LAST
     insert into bonus (empno, amount)
       values (e_rec(i).empno, e_rec(i).sal * 10 / 100);

   DBMS_OUTPUT.put_line ('Rows inserted: ' || SQL%ROWCOUNT);
 
   COMMIT;
   RETURN TRUE;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      RETURN FALSE;
END;[/code]

You can execute it as below:
[code type="SQL"]SET SERVEROUTPUT ON;

BEGIN
   IF f_currowtype
   THEN
      DBMS_OUTPUT.put_line ('Success');
   ELSE
      DBMS_OUTPUT.put_line ('Failed');
   END IF;
END;[/code]

Examples are given below to describe Oracle Joins using new syntax. Which is very easy to understand and very good in SQL query readability. Because if query is larger and having too many tables with too  many joins in old syntax it becomes complex to understand. So it is better to use these new SQL join syntax for better understanding.

To demonstrate first I would like to show below the table structure with data used in the examples.

Table 1: EMP
Oracle SQL joins new syntax with examples
Table 2: DEPT
Dept Table
Table 3: BONUS
Bonus Table
As you can see above the 3 tables EMP, DEPT and BONUS and for the examples I have changed some data in every table to show the desired results, like EMP table having DeptNo 40 which not exists in DEPT table and DEPT table having DeptNo 2 Dname IT which not exists in EMP table and in BONUS table there are only 4 employee records.

INNER JOIN Example

Inner Join is the simple join which returns all the rows from all the tables used in query by matching all the criteria. Example is below:
[code type="SQL"]SELECT e.empno,
       e.ename,
       e.job,
       e.deptno,
       d.dname,
       e.sal,
       e.comm  FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;[/code]
Output from the above query:
Oracle SQL Inner Join Example
You can see in above image that all the rows from EMP table and DEPT returned which are matching in both tables but record having DeptNo 40 Ename ALLEN is missing because it does not exists in DEPT table.

LEFT OUTER JOIN Example

Left Outer Join returns all the rows from the Left Side table and the only rows from the right side table who matches used in LEFT OUTER JOIN clause. In the below example it will return all the rows from EMP table even the record having DeptNo 40 which is not exists in DEPT table.
[code type="SQL"]SELECT e.empno,
       e.ename,
       e.job,
       e.deptno,
       d.dname,
       e.sal,
       e.comm
  FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;[/code]
Oracle SQL Left Outer Join Example new syntax

RIGHT OUTER JOIN Example

Right Outer Join return all the rows from the right side table and rows that matches from left side table. In the below example it will return all the rows from DEPT table including IT Dname which is not exists in EMP table but it will not return the EMP table record which is having DeptNo 40 because it does not exists in DEPT table.
[code type="SQL"]SELECT e.empno,
       e.ename,
       e.job,
       e.deptno,
       d.dname,
       e.sal,
       e.comm
  FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;[/code]
Oracle SQL Right Outer Join Example new syntax

FULL OUTER JOIN Example

Full Outer Join return all the rows from left side table and right side table even the rows does not matches from both the tables. In the below example it will return all the rows from EMP table including DeptNo 40 record and all the rows from DEPT table including IT Department record.
[code type="SQL"]SELECT e.empno,
       e.ename,
       e.job,
       e.deptno,
       d.dname,
       e.sal,
       e.comm
  FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno;[/code]
Oracle SQL Full Join Example new syntax

Now in the below examples BONUS table also included to demonstrate some multiple table examples with multiple joins.

INNER JOIN WITH MULTIPLE TABLES Example

In the below example inner joins are used with all three tables and will return only the records which are matching in all the tables.
[code type="SQL"]SELECT e.empno,
       e.ename,
       e.job,
       e.deptno,
       d.dname,
       e.sal,
       e.comm,
       b.amount bonus
  FROM emp e
       INNER JOIN dept d
          ON e.deptno = d.deptno
       INNER JOIN bonus b
          ON e.empno = b.empno;[/code]
Oracle SQL Joins Multiple

INNER JOIN WITH LEFT OUTER JOIN WITH MULTIPLE TABLE Example

Below example uses inner join and with EMP and DEPT table and left outer join with BONUS table, means it will return all the rows from EMP and DEPT tables which are matching but only rows from BONUS table which are matching with EMP table.
[code type="SQL"]SELECT e.empno,
       e.ename,
       e.job,
       e.deptno,
       d.dname,
       e.sal,
       e.comm,
       b.amount bonus
  FROM emp e
       INNER JOIN dept d
          ON e.deptno = d.deptno
       LEFT OUTER JOIN bonus b
          ON e.empno = b.empno;[/code]
Oracle SQL INNER with LEFT OUTER join example

FULL OUTER JOIN WITH MULTIPLE TABLES Example

Below example uses full outer join for all three tables and will return all the records from all the tables.
[code type="SQL"]SELECT e.empno,
       e.ename,
       e.job,
       e.deptno,
       d.dname,
       e.sal,
       e.comm,
       b.amount bonus
  FROM emp e
       FULL OUTER JOIN dept d
          ON e.deptno = d.deptno
       FULL OUTER JOIN bonus b
          ON e.empno = b.empno;[/code]
Oracle SQL Full Outer Join with multiple table example
Finally you can use WHERE clause at the end to specify more criteria as shown below:
[code type="SQL"]SELECT e.empno,
       e.ename,
       e.job,
       e.deptno,
       d.dname,
       e.sal,
       e.comm,
       b.amount bonus
  FROM emp e
       INNER JOIN dept d
          ON e.deptno = d.deptno
       LEFT OUTER JOIN bonus b
          ON e.empno = b.empno
 WHERE d.deptno = 30;[/code]
Oracle SQL New Joins with Where clause example

Earlier I posted to count number of characters in a string using SQL and in this post example is given to count number of special characters in a string using SQL in Oracle.
[code type="CSS"]SELECT replace(a_string, ' ', 'spaces') Character, COUNT (a_string) Occurrences
    FROM (    SELECT SUBSTR (a_string, LEVEL, 1) a_string
                FROM (SELECT replace(translate(lower('abc_xyz@web.com'), 'abcdefghijklmnopqrstuvwxyz1234567890',  ' '), ' ', '')
                                a_string
                        FROM DUAL)
          CONNECT BY LEVEL <= LENGTH (a_string))
GROUP BY a_string[/code]
Count special characters using sql in Oracle

Oracle form to Apex
If you want to use your Oracle Form's functionality in Oracle Apex application then you need to convert first Oracle Form into XML using frmf2xml utility in Oracle Forms 11g and then you need to create a migration project into Oracle Apex.

Below are the steps given to perform this task.

1. Convert Oracle Form to XML using frmf2xml.bat

Run the frmf2xml.bat by providing your FMB file with its location from the Oracle form's instance home directory as shown below:

CD C:\Oracle\Middleware\asinst_2\bin

frmf2xml.bat d:\temp\testab.fmb

It will convert and save the XML file into same location of your form.

Convert oracle form to xml for Oracle apex

2. Create an Oracle Apex Workspace.
Run Oracle Apex and create a workspace with the same Oracle Forms application schema.

3. Create a migration project in workspace you created. App Builder > Migrations
Log in to the workspace and create the migration project and import the XML you created.

4. Create the Oracle Apex Application and Associate with the Migration Project.
Create a new application using in Application Builder and then go to App Builder > Migrations and edit your project and associate the project with your new Apex application.

Convert oracle form to apex application

Below is the example of SQL query to read / extract data from XML in Oracle. You can query any XML easily by providing its namespace clause and the path information through SQL query.

SQL Query Example to Read Data from XML in Oracle

Suppose you have XML data in the following format:

<main>
  <DATA_RECORD>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH11V9</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1980/12/17 00:00:00</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7499</EMPNO>
...

Then you must specify /main/DATA_RECORD for the name space clause in XMLTABLE() function and to extract column name specify column name as path as shown in below example:

Note: Name space clause and Path names are case sensitive.

SELECT *
  FROM XMLTABLE (
          '/main/DATA_RECORD'
          PASSING xmltype ('
<main>
  <DATA_RECORD>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH11V9</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1980/12/17 00:00:00</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981/02/20 00:00:00</HIREDATE>
    <SAL>1600</SAL>
    <COMM>300</COMM>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7654</EMPNO>
    <ENAME>MARTIN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981/09/28 00:00:00</HIREDATE>
    <SAL>1250</SAL>
    <COMM>1400</COMM>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7698</EMPNO>
    <ENAME>BLAKE</ENAME>
    <JOB>MANAGER</JOB>
    <MGR>7839</MGR>
    <HIREDATE>1981/05/01 00:00:00</HIREDATE>
    <SAL>2850</SAL>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7788</EMPNO>
    <ENAME>SCOTT</ENAME>
    <JOB>ANALYST</JOB>
    <MGR>7566</MGR>
    <HIREDATE>1987/04/19 00:00:00</HIREDATE>
    <SAL>30000</SAL>
    <DEPTNO>20</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7839</EMPNO>
    <ENAME>KING</ENAME>
    <JOB>PRESIDENT</JOB>
    <HIREDATE>1981/11/17 00:00:00</HIREDATE>
    <SAL>5000</SAL>
    <DEPTNO>10</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7844</EMPNO>
    <ENAME>TURNER</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981/09/08 00:00:00</HIREDATE>
    <SAL>1500</SAL>
    <COMM>0</COMM>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7876</EMPNO>
    <ENAME>ADAMS</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7788</MGR>
    <HIREDATE>1987/05/23 00:00:00</HIREDATE>
    <SAL>1100</SAL>
    <DEPTNO>20</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>7900</EMPNO>
    <ENAME>JAMES</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981/12/03 00:00:00</HIREDATE>
    <SAL>950</SAL>
    <COMM>22</COMM>
    <DEPTNO>30</DEPTNO>
  </DATA_RECORD>
  <DATA_RECORD>
    <EMPNO>1</EMPNO>
    <ENAME>ddd</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7782</MGR>
    <HIREDATE>2016/05/01 00:00:00</HIREDATE>
    <DEPTNO>3</DEPTNO>
  </DATA_RECORD>
</main>')
          COLUMNS ename PATH 'ENAME',
JOB PATH 'JOB',
sal NUMBER PATH 'SAL')

Sometime happens that you posted a blog for a particular topic but you put the title which is not that relevant and after publishing the blog post you realize that you made a mistake and then you change the title of your blog post and publish it again but in Google search result it is still showing the old title.

I got this problem too and I found the solution in Google Webmaster by fetching the blog post URL with Render option, which really worked.

So the steps are go to Google Webmaster in Crawl section click on Fetch as Google then provide the URL and click on Fetch and Render as shown in below image.

How to Update / Change Google Search Results for Your Website

And you are done, after few hours the result would be updated on Google Search.

The SQL example is given below to count number of character occurrences in a string in Oracle. You can more customize the SQL as per your requirement.
[code type="SQL"]SELECT replace(a_string, ' ', 'spaces') Character, COUNT (a_string) Occurrences
    FROM (    SELECT SUBSTR (a_string, LEVEL, 1) a_string
                FROM (SELECT lower('a quick brown fox jumps right over the lazy dog.')
                                a_string
                        FROM DUAL)
          CONNECT BY LEVEL <= LENGTH (a_string))
GROUP BY a_string  [/code]
The above SQL will count number of occurrences in a string and output would be as below:

CHARACTER OCCURRENCES
------------------ -----------
q                            1
i                            2
k                            1
h                            2
e                            2
z                            1
o                            4
j                            1
s                            1
d                            1
r                            3
g                            2
a                            2
b                            1
p                            1
t                            2
c                            1
u                            2
w                            1
x                            1
y                            1

CHARACTER          OCCURRENCES
------------------ -----------
.                            1
spaces                       9
n                            1
f                            1
m                            1
v                            1
l                            1

28 rows selected.
count number of characters in a string oracle sql

Upgrade Oracle Application Express from 5.0 to 5.1

Below are the steps to upgrade Oracle Apex from 5.0 to 5.1 version. As previously I already posted to Install Oracle Applicaiton Express 5.0 on Oracle 12c PDB. After downloading the Oracle Apex from Oracle extract the zip file and change the current directory to the directory where you extracted and then connect to Oracle database using SYS credentials as shown below.

Run CMD command and then type the commands as shown below to change the directory and to connect to Oracle database.

CD D:\Software\Apex5_1\apex_5.1_en\apex

sqlplus /nolog

Connect as below if using Oracle Database 12c PDB:

conn sys/yourpsw@orcl12c as sysdba

Alter Session Set Container = pdb12c;
Alter Database Open;

Else connect as shown below:

conn sys@Oradb11g as sysdba
Enter Password: *****

Then follow these 5 steps to upgrade to Oracle Apex 5.1. But remember if your tablespaces are different from SYSAUX then change accordingly in below commands.

1. Run apexins1.sql script.

[code type="SQL"]@apexins1.sql SYSAUX SYSAUX TEMP /i/[/code]

2. Run apexins2.sql script.

[code type="SQL"]@apexins2.sql SYSAUX SYSAUX TEMP /i/[/code]

3. Run apexins3.sql script.

[code type="SQL"]@apexins3.sql SYSAUX SYSAUX TEMP /i/[/code]

4. Run apex_epg_config.sql script and set the appropriate directory.

[code type="SQL"]@apex_epg_config.sql D:\Software\Apex5_1\apex_5.1_en[/code]

5. Run the below script.

[code type="SQL"]BEGIN
   DBMS_EPG.
    set_dad_attribute (dad_name     => 'APEX',
                       attr_name    => 'cgi-environment-list',
                       attr_value   => 'REQUEST_PROTOCOL=https');
   DBMS_EPG.
    set_dad_attribute (
      dad_name     => 'APEX',
      attr_name    => 'cgi-environment-list',
      attr_value   => 'HTTP_HOST=external-name:external-port');
   COMMIT;
END;
/

DECLARE
   ACL_PATH   VARCHAR2 (4000);
BEGIN
   SELECT ACL
     INTO ACL_PATH
     FROM DBA_NETWORK_ACLS
    WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

   IF DBMS_NETWORK_ACL_ADMIN.
       CHECK_PRIVILEGE (ACL_PATH, 'APEX_050100', 'connect')
         IS NULL
   THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (ACL_PATH,
                                            'APEX_050100',
                                            TRUE,
                                            'connect');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_NETWORK_ACL_ADMIN.
       CREATE_ACL ('power_users.xml',
                   'ACL that lets power users to connect to everywhere',
                   'APEX_050100',
                   TRUE,
                   'connect');
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ('power_users.xml', '*');
END;
/

COMMIT;

DECLARE
   ACL_PATH   VARCHAR2 (4000);
BEGIN
   -- Look for the ACL currently assigned to 'localhost' and give APEX_050100
   -- the "connect" privilege if APEX_050100 does not have the privilege yet.
   SELECT ACL
     INTO ACL_PATH
     FROM DBA_NETWORK_ACLS
    WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

   IF DBMS_NETWORK_ACL_ADMIN.
       CHECK_PRIVILEGE (ACL_PATH, 'APEX_050100', 'connect')
         IS NULL
   THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (ACL_PATH,
                                            'APEX_050100',
                                            TRUE,
                                            'connect');
   END IF;
EXCEPTION
   -- When no ACL has been assigned to 'localhost'.
   WHEN NO_DATA_FOUND
   THEN
      DBMS_NETWORK_ACL_ADMIN.
       CREATE_ACL ('local-access-users.xml',
                   'ACL that lets users to connect to localhost',
                   'APEX_050100',
                   TRUE,
                   'connect');
      DBMS_NETWORK_ACL_ADMIN.
       ASSIGN_ACL ('local-access-users.xml', 'localhost');
END;
/

COMMIT;

BEGIN
   DBMS_NETWORK_ACL_ADMIN.
    APPEND_HOST_ACE (
      HOST   => '*',
      ace    => xs$ace_type (privilege_list   => xs$name_list ('connect'),
                             principal_name   => 'APEX_050100',
                             principal_type   => xs_acl.ptype_db));
END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.
    APPEND_HOST_ACE (
      HOST   => 'localhost',
      ace    => xs$ace_type (privilege_list   => xs$name_list ('connect'),
                             principal_name   => 'APEX_050100',
                             principal_type   => xs_acl.ptype_db));
END;
/
[/code]
And now you successfully completed the up-gradation of Oracle Application Express from 5.0 to 5.1.

You can check and set the http port if not already set, as shown below:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

EXEC DBMS_XDB.SETHTTPPORT(8383);

If you have any issues during installation please comment below. Thanks.

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.