Oracle Basic Query Rewrite

Oracle Training in Chennai from the Best Oracle PLSQL Training Institute in Chennai. Learn Oracle PL SQL from the Best Oracle PLSQL Training center in Chennai with the most experienced trainer in the field. Greens Technology Adyar provides Oracle SQL, PLSQL, Performance Tuning training in Chennai to professionals and corporates on advanced SQL, PL SQL with XML, Oracle Database 12c Analytic SQL for Data Warehousing and Performance Tuning.

Overview of Query Rewrite in Oracle

In a recent forum thread I was asked how I would load an XMLTYPE column in a table from a file. This article explain one way of doing this.

First we create the necessary schema objects.

CREATE OR REPLACE DIRECTORY xml_dir AS 'c:temp';

CREATE TABLE xml_tab (
  id        NUMBER(10),
  filename  VARCHAR2(100),
  xml       XMLTYPE
)
/

ALTER TABLE xml_tab ADD (
  CONSTRAINT xml_tab_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE xml_tab_seq;

Next we create a procedure to load a file into the table.

CREATE OR REPLACE PROCEDURE load_xml (p_dir       IN  VARCHAR2,
                                      p_filename  IN  VARCHAR2) AS
  l_bfile  BFILE := BFILENAME(p_dir, p_filename);
  l_clob   CLOB;
BEGIN
  DBMS_LOB.createtemporary (l_clob, TRUE);
  
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  INSERT INTO xml_tab (
    id,
    filename,
    xml
  )
  VALUES (
    xml_tab_seq.NEXTVAL,
    p_filename,
    XMLTYPE.createXML(l_clob)
  );
  COMMIT;
  
  DBMS_LOB.freetemporary (l_clob);
END;
/

Assuming the emp.xml is present in the appropriate location it can be loaded using the following command.

EXEC load_xml(p_dir => 'XML_DIR', p_filename => 'emp.xml');
The contents of the XMLTYPE can be checked using the following query.
SET LONG 5000
SELECT xml
FROM   xml_tab;

For more information see:

Hope this helps. Regards Dinesh...

Back to the Top.