JDA

1. Difference b/w count(*) and count(1)
2. If a record is inserted in an table, the relevant MV should automatically be refreshed. How can it be done?
3. Why XML is used for any system to system communication
4. DELETE and TRUNCATE
5. Symantics
6. What do you exactly mean by rowid and rownum
7. How do you load data from file to table. Have you ever user SQL Loader.

Working with TABLE Type

CREATE OR REPLACE TYPE SCOTT.v_emp_data AS OBJECT

(

  EMPNO    number(4),

  ENAME     varchar2(10),

  JOB  varchar2(9),

  mgr number(4),

  hiredate date,

  sal  number(7,2),

  comm   number(7,2),

  deptno     number(2)

)

/

CREATE OR REPLACE TYPE SCOTT.v_emp_tab as table of  v_emp_data;

/

CREATE OR REPLACE PROCEDURE scott.d_loaddata (i_data v_emp_tab)

AS

   v_data   v_emp_tab;

BEGIN

   INSERT INTO emp_load

      SELECT *

        FROM TABLE (i_data);

   NULL;

END;

Working with XMLType and xmltable

Query to get multiple records from a single XML.

select * from xmltable('*' passing xmltype('<employee>
<emp>

    <empno>102</empno>

    <ename>Krishna</ename>

    <job>Accounts</job>

    <mgr></mgr>

    <hiredate></hiredate>

    <sal>15000</sal>

    <comm>10</comm>

    <deptno>50</deptno>

</emp>

<emp>

    <empno>101</empno>

    <ename>Raja</ename>

    <job>Development</job>

    <mgr>102</mgr>

    <hiredate></hiredate>

    <sal>15000</sal>

    <comm>10</comm>

    <deptno>50</deptno>

</emp>

</employee>').extract('//emp')

columns empno int path 'empno',

ENAME varchar2(10) path 'ename',

JOB varchar2(9) path 'job',

mgr number path 'mgr' ,

HIREDATE date path 'hiredate',

sal number path 'sal' ,

comm number path 'comm' ,

deptno number path 'deptno'

) x;

                                     

Procedure to insert multiple records from an XML into a table:        

CREATE OR REPLACE PROCEDURE scott.d_loadxmldata (i_data xmltype)

AS

BEGIN

INSERT INTO emp_load

select * from xmltable('*' passing i_data.extract('//emp')

columns empno int path 'empno',

ENAME varchar2(10) path 'ename',

JOB varchar2(9) path 'job',

mgr number path 'mgr' ,

HIREDATE date path 'hiredate',

sal number path 'sal' ,

comm number path 'comm' ,

deptno number path 'deptno'

) x;

commit;

END;

How to convert a oracle table data into XML

SQL> SELECT DBMS_XMLGEN.GETXML('SELECT * FROM EMP') TEST_XML FROM DUAL;