Assigning Default Template for XML Publisher Report

Navigation:

1. System Administrator > System Administration > Concurrent > Programs

2. Query your Program Name

3. Click on Onsite Setting

4. From the LOV for the "Template" field select a template as default.

How to Print XML Publisher report SQL and the values of the parameters

How to Print XML Publisher report SQL and the values of the parameters?

When we enabled the DebugFlag (P_DEBUG_FLAG) the log file will provides the following:

Ø  Begin and End time of the report

Ø  Time taken to execute report SQL and generate XML

Ø  Prints the report SQL and the values of the parameters.

How do I set the DebugFlag (P_DEBUG_FLAG)?

Set P_DEBUG_FLAG when P_DEBUG_FLAG is not already set for the report.

Navigate to System Administrator > Concurrent > Program > Define.

Ø  Search for the report (e.g., Account Analysis Report), "XLNBRPT" is the short name in the Concurrent Program window.

Ø  Click on Parameters button.

Ø  Move to the last parameter.

Ø  Add a new parameter called DebugFlag with the following:

ü  Sequence - 910 (If it exists, give a different number)

ü  Parameter- P_DEBUG_FLAG

ü  Description - DebugFlag

ü  Enabled - Check

ü  Value Set - XLA_SRS_NO_VALIDATION

ü  Default Type - Constant

ü  Default Value - Y

ü  Required - Uncheck

ü  Display - Check Prompt

ü  P_DEBUG_FLAG

ü  Token - DebugFlag (Case sensitive. Give this exact value)

Submit the report with P_DEBUG_FLAG as Y and the log file should include the report SQL.

XML SEQUENCE SQL Function

What is XMLSEQUENCE SQL Function and How to use?

SQL function XML Sequence returns an XMLSequenceType value (a varray of XML Type instances). Because it returns a collection, this function can be used in the FROM clause of SQL queries.

Example 1:
SELECT VALUE (T).getstringval () Attribute_Value

  FROM TABLE (XMLSEQUENCE (EXTRACT (XMLType ('V1V2V3'), '/A/B'))) T;

Example 2:
CREATE TABLE emp_xml_tab OF XMLTYPE;

Then insert data into table
INSERT INTO emp_xml_tab

        VALUES (

                  XMLType ('112Joe50000217

                             Jane

                            60000412Jack40000'));

COMMIT;

/

To create a new XML document containing only employees who earn $42,000 or more, you can use the following query:

SELECT SYS_XMLAGG (VALUE (em), XMLFormat ('EMPLOYEES'))

  FROM emp_xml_tab doc,

       TABLE (XMLSEQUENCE (EXTRACT (VALUE (doc), '/EMPLOYEES/EMP'))) em

 WHERE EXTRACTVALUE (VALUE (em), '/EMP/SALARY') >= 42000;

These are the steps involved in this query:

·         Function extract returns a fragment of EMP elements.

·         Function XMLSequence gathers a collection of these top-level elements into XMLType instances and returns that.

·         Table makes a table value from the collection. The table value is then used in the query FROM clause.

Example 3:
XMLSEQUENCE: Generate a Document for Each Row of a Cursor
In this example, SQL function XMLSequence is used to create an XML document for each row of a cursor expression, and it returns an XMLSequenceType value (a varray of XMLType instances).

SELECT VALUE (em).getClobVal () AS "XMLTYPE"

  FROM TABLE (XMLSEQUENCE (CURSOR (SELECT *

                                     FROM hr.employees

                                    WHERE employee_id = 104))) em;


Example 4:
XMLSEQUENCE: Unseating Collections in XML Documents into SQL Rows

CREATE TABLE dept_xml_tab OF XMLTYPE;

INSERT INTO dept_xml_tab

     VALUES (XMLType ('Sports

               John33333

               Jack333444'));

INSERT INTO dept_xml_tab

     VALUES (XMLType ('Sports

               Marlin20000'));1 row created.
COMMIT;

/


SELECT EXTRACTVALUE (OBJECT_VALUE, '/Department/@deptno') AS deptno,

       EXTRACTVALUE (VALUE (em), '/Employee/@empno') AS empno,

       EXTRACTVALUE (VALUE (em), '/Employee/Ename') AS ename

  FROM dept_xml_tab,

       TABLE (

          XMLSEQUENCE (

             EXTRACT (OBJECT_VALUE,'/Department/EmployeeList/Employee'))) em;

Export and Import Data from XML Schema Database

Login with Username and Password

SQL> show parameter db_name;    -- It will shows database name
SQL> exit;

How to setup my SID?
set oracle_sid <2nd DBname>

Export from user XX to user AA
Export: Release 10.1.0.4.2 - Production on Thu Mar 01 07:50:03 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: XX/XX
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP > c:\ware.dmp
(2)U(sers), or (3)T(ables): (2)U > T
Export table data (yes/no): yes > Y
Compress extents (yes/no): yes > Y
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
TA
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > WAREHOUSES . . Exporting table WAREHOUSES     

9 rows exported
Table (T) or Partition (T:P) to be exported: (RETURN to quit) >
Export terminated successfully without warnings.
C:\

***************************************************
TO IMPORT DATA FROM C:\WARE.DMP FILE TO USER SCOTT
C :\> IMP SCOTT/TIGER FILE=C:\WARE.DMP FULL=Y

SQL Loader with XML DATA

1. Login with Username and Password
2. Create table load_test of xmltype;
3. Exit from user
4. Create a control file test.ctl

 

LOAD DATA

INFILE *

TRUNCATE INTO TABLE load_test

XMLType(xmldata)

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(

xmldata

)

BEGINDATA

KING<  ./NAME><  ./  EMP>,

SCOTT<. /NAME><  ./EMP>,

SMITH< . /NAME><  ./EMP>

5. c:\> sqlldr usename/password control= c:/tmp/test.ctl

6. Sqlplus   usename/password
  select * from load_test;

Example to store XML type data from SQL*loader

--------------------------------------------------------

1.  Login with Username and Password

2.  Create table load_test of xmltype;

3.  Create a data file c:\bhaskar.dat - contains XML data

4.  Create a control file c:\loaddata.ctl
LOAD DATA

INFILE *

INTO TABLE test_load

APPEND XMLType(XMLDATA)

( lobfn FILLER CHAR TERMINATED BY ',',

     XMLDATA LOBFILE(lobfn) TERMINATED BY ''

   )

BEGINDATA

c:\bhasakr.dat

5. c :\> sqlldr usename/password
    control=c:\ loaddata.ctl
6. sqlplus
    usename/password
  select * from load_test;