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

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.