1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to insert data from XML to a table

Discussion in 'General' started by sravanthi24@yahoo.com, Jun 28, 2010.

  1. sravanthi24@yahoo.com

    sravanthi24@yahoo.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    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

    <?xml version="1.0" ?>
    <metadata>
    - <Zipcodes>
    - <mappings Record="4">
    <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
    <ZIPCODE>94301</ZIPCODE>
    </mappings>
    - <mappings Record="5">
    <STATE_ABBREVIATION>CO</STATE_ABBREVIATION>
    <ZIPCODE>80323</ZIPCODE>
    <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
    </mappings>
    </Zipcodes>
    </metadata>


    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 initialize 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. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    utl_file_dir is a deprecated parameter, meaning it's no longer to be used. You should be creating directories instead:

    Code (SQL):
     
    SQL> CREATE directory xml_dir AS 'C:\\OracleProject';
     
    and for this you'll need to grant the create directory privilege to ridb. Your open command then becomes:

    Code (SQL):
     
    v_FileHandle := utl_file.fopen(XML_DIR,'trial.xml', 'r');
     
    Also, on Windows, the directory 'separator' is \, not /, and when creating directories you need to 'double up' on the \, as illustrated in the create directory command above.
     
    sravanthi24@yahoo.com likes this.
  3. sravanthi24@yahoo.com

    sravanthi24@yahoo.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I have created the directory from sys as mentioned below:

    CREATE directory XML_TEST_DIR AS 'c:\\sravanthi\\FSS\\OracleProject';

    Grant read,write on directory XML_TEST_DIR to ridb;

    & decalred the variable (since i'm getting the error "XML_TEST_DIR must be declared" when i compiled the proc )& included in my procedure as shown below:

    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;
    XML_TEST_DIR VARCHAR2(40) := 'c:\\sravanthi\\FSS\\OracleProject';
    begin
    -- 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(XML_TEST_DIR,'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;

    when i execute i'm getting the error as :

    Connecting to the database RIDB.
    ORA-29280: invalid directory path
    ORA-06512: at "SYS.UTL_FILE", line 33
    ORA-06512: at "SYS.UTL_FILE", line 436
    ORA-06512: at "RIDB.TRIAL", line 29
    ORA-06512: at line 2
    Process exited.

    Could you please let me know...do i need to initialzed the UTL_FILE variable.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    My error with the statement, the directory name needs to be in single quotes:

    v_FileHandle := utl_file.fopen('XML_TEST_DIR','trial.xml', 'r');
     
    sravanthi24@yahoo.com likes this.
  5. sravanthi24@yahoo.com

    sravanthi24@yahoo.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I appreciate your help.

    I have given the directory name in single quotes..still i'm getting the same error..

    Connecting to the database RIDB.
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 475
    ORA-29283: invalid file operation
    ORA-06512: at "RIDB.TRIAL", line 20
    ORA-06512: at line 2
    Process exited.
    Disconnecting from the database RIDB.


    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;
    XML_TEST_DIR VARCHAR2(40) := 'c:\\sravanthi\\FSS\\OracleProject';
    begin
    -- 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('XML_TEST_DIR','trialxml.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;


    Could you please let me know how to proceed...
     
  6. sravanthi24@yahoo.com

    sravanthi24@yahoo.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Sorry it gives me error : invalid file operation.
     
  7. sravanthi24@yahoo.com

    sravanthi24@yahoo.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    David,

    I appreciate your help
    Could you please let me know about the error :invalid file operation.
    I had made changes by mentioning the directory name in single quotes i get the following error:
    Connecting to the database RIDB.
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 475
    ORA-29283: invalid file operation
    ORA-06512: at "RIDB.TRIAL", line 20
    ORA-06512: at line 2
    Process exited.
    Disconnecting from the database RIDB.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Check the permissions on that file, it may not be readable by oracle.
     
  9. sravanthi24@yahoo.com

    sravanthi24@yahoo.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    David,

    Since i'm using windows 7 i have set the file permission for the trialxml.xml file from all the users as shown in image

    Could you please let me know how to set the file permission readable by oracle.
     

    Attached Files:

  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please check that 'r' is a valid file operation as it may need to be in upper case ('R') to be recognized.
     
  11. sravanthi24@yahoo.com

    sravanthi24@yahoo.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I tried changing thr 'r' to 'R'

    i get the error as :
    Connecting to the database RIDB.
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 475
    ORA-29283: invalid file operation
    ORA-06512: at "RIDB.TRIAL", line 21
    ORA-06512: at line 2
    Process exited.
    Disconnecting from the database RIDB.

    I have a doubt that, do i need to set advanced permission for the file it shows full control is not inhereted.Please check the attachment of the screen shot.
     

    Attached Files:

  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    As I said earlier it is a file or directory permissions issue; fix that and the error will go away. You stated the permissions were correct earlier so I took your word for that.
     
  13. sravanthi24@yahoo.com

    sravanthi24@yahoo.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    David,

    Could you please let me know how to set the file or directory access permission. From windows i had given full control access for (C:\sravanthi\FSS\OracleProject ) for each directory & the xml file .

    Still i'm getting the same error..

    So, do i need to set any other information other than that.

    & could you please check my directory variable declare statment in procedure. Did i mentioned the path correctly by '\' (slashes)

    XML_TEST_DIR VARCHAR2(40) := 'c:\\sravanthi\\FSS\\OracleProject';
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are not doing this correctly -- you need to use the CREATE DIRECTORY command as I showed you in an earlier response. What you have tried to do won't work and you're finding that out as Oracle doesn't know of this directory and has no declared rights/privileges on it. To repeat the instructions I gave you earlier:

    1) grant create directory to the ridb user.
    2) execute the followng statement at the SQL> prompt:
    create directory xml_test_dir as 'c:\\sravanthi\\FSS\\OracleProject';
    3) use this created directory in your command
    4) DO NOT TRY TO CREATE SOME VARIABLE IN THE PL/SQL BLOCK NAMED THE SAME AS THIS DIRECTORY.
     
  15. bajai

    bajai Guest

    Hello Experts,

    I have done the above and run the proc. succesfully with the output etc.
    <?xml version="1.0" ?>
    <metadata>
    - <Zipcodes>
    - <mappings Record="4">
    <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
    <ZIPCODE>94301</ZIPCODE>
    </mappings>
    - <mappings Record="5">
    <STATE_ABBREVIATION>CO</STATE_ABBREVIATION>
    <ZIPCODE>80323</ZIPCODE>
    <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
    </mappings>
    </Zipcodes>
    </metadata>
    INSERT DONE
    OUTPUT = INSERT DONE

    In wich table is the data stored in the database?

    TIA.
    Bajai.
     
  16. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Bajai,

    Please some more details like what you have done to insert the data from xml tags. By that we can help you in this.
     
  17. oyeniyi

    oyeniyi Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    abidjan
    how i can execute this code?