how to insert data from XML file to a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: how to insert data from XML file to a table

  1. #1
    Join Date
    Jun 2010
    Posts
    1

    how to insert data from XML file to a table

    Hi,

    I'm using Oracle 10g Express Edition
    I need help in How to insert the data from XML file into the table.
    Below is the example i'm working on..

    I have create ridb user with below mentioned privileges:


    Account Status Locked Unlocked
    Default Tablespace: USERS
    Temporary Tablespace: TEMP


    User Privileges :
    Roles:
    CONNECT
    RESOURCE


    Direct Grant System Privileges:
    CREATE DATABASE LINK
    CREATE MATERIALIZED VIEW
    CREATE PROCEDURE
    CREATE PUBLIC SYNONYM
    CREATE ROLE
    CREATE SEQUENCE
    CREATE SYNONYM
    CREATE TABLE
    CREATE TRIGGER
    CREATE TYPE
    CREATE VIEW




    & table is created TRIALZIPCODES below mentioned is the DDL:

    CREATE TABLE TRIALZIPCODES
    (
    STATE_ABBR VARCHAR2(20) NOT NULL
    , ZIP_CODE NUMBER(10, 0) NOT NULL
    , ZIP_CODE_EXT VARCHAR2(20)
    );



    Below is the XML FILE: which is stored in C:\OracleProject Folder
    File name: trial.xml



    -
    -
    CA
    94301

    -
    CO
    80323
    9277





    PL/SQL Procedure:which i'm trying to execute from SQLDeveloper

    create or replace
    PROCEDURE TRIAL AS
    BEGIN
    DECLARE
    -- declare attributes

    charString varchar2(80);
    finalStr varchar2(4000) := null;
    rowsp integer;
    v_FileHandle UTL_FILE.FILE_TYPE;
    l_context_handle dbms_xmlgen.ctxHandle;
    insCtx DBMS_XMLStore.ctxType;

    begin

    -- DBMS_XMLGEN.setRowTag ( ctx IN ctxHandle, rowTag IN VARCHAR2);
    -- DBMS_XMLGEN.setRowSetTag ( ctx IN ctxHandle, rowSetTag IN VARCHAR2);
    -- the name of the table as specified in our DTD
    DBMS_XMLGEN.SETROWSETTAG(l_context_handle,'zipcodes');
    -- the name of the data set as specified in our DTD
    DBMS_xmlgen.setRowTag(l_context_handle,'mappings');
    -- for getting the output on the screen
    dbms_output.enable(1000000);
    -- open the XML document in read only mode
    v_FileHandle := utl_file.fopen('c:/OracleProject','trial.xml', 'r');

    loop

    BEGIN

    utl_file.get_line(v_FileHandle, charString);
    exception
    when no_data_found then
    utl_file.fclose(v_FileHandle);
    exit;

    END;
    dbms_output.put_line(charString);
    if finalStr is not null then
    finalStr := finalStr || charString;
    else
    finalStr := charString;
    end if;
    end loop;
    -- for inserting the XML data into the table
    insCtx := DBMS_XMLSTORE.NEWCONTEXT('RIDB.TRIALZIPCODES');
    insCtx := DBMS_XMLSTORE.INSERTXML(insCtx, finalStr);
    dbms_output.put_line('INSERT DONE '||TO_CHAR(rowsp));
    DBMS_XMLStore.closeContext(insCtx);


    END;

    END TRIAL;

    For the first time when i complied i got the errors as :

    Procedure RIDB.PROCEDURE1@RIDB
    Error(16,14): PLS-00201: identifier 'UTL_FILE' must be declared
    Error(16,14): PL/SQL: Item ignored
    Error(29,1): PLS-00320: the declaration of the type of this expression is incomplete or malformed
    Error(29,1): PL/SQL: Statement ignored
    Error(33,1): PL/SQL: Statement ignored
    Error(33,19): PLS-00320: the declaration of the type of this expression is incomplete or malformed
    Error(36,1): PL/SQL: Statement ignored
    Error(36,17): PLS-00320: the declaration of the type of this expression is incomplete or malformed

    So i logged in as sys & grant the permission to execute on UTL_FILE to ridb (user):
    SQL Statement:

    grant execute on utl_file to ridb

    So, it got compiled successfully but when i execute it gives me error as:
    Source does not have a runnable target.

    What does this mean?

    So I browse through forum & i got to know that i need to initial the UTL_FILE_DIR ="C:/OracleProject" in init.ora
    So can i edit the init.ora with notepad.When i tried to do that it says permission denied

    In my system it shows the init.ora file in path C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts
    but there is also other file initXETemp in the same path do i need to do the changes in it.

    I have tried even editing the SPFILE as mentioned below:

    C:\oraclexe\app\oracle\product\10.2.0\server\dbs\SPFILEEXE - I had edit this file using notepad & set the value of UTL_FILE_DIR ="C:/OracleProject". So next time when i restarted i'm unable to log on to the database.

    So i had reinstall the software again.

    Could you please let me know how to proceed..

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Stop causing self-inflicted wounds because you haven't read any documentation.

    Exporting the Server Parameter File

    You can use the CREATE PFILE statement to export a server parameter file (SPFILE) to a text initialization parameter file. Doing so might be necessary for several reasons:

    * For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2 views.

    * To modify the spfile server parameter file by first exporting it, editing the resulting text file, and then re-creating it using the CREATE SPFILE statement

    The exported file can also be used to start up an instance using the PFILE clause.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width