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!

genrate xml from oracle 9i

Discussion in 'SQL PL/SQL' started by nrupa, Mar 23, 2012.

  1. nrupa

    nrupa Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    Hello,

    I am using oracle 9i database in my project.

    I am trying to generate an xml from oracle 9i.

    I am creating a procedure but i got an error like this.

    Debugger connected to database.
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at "SYS.UTL_FILE", line 121
    ORA-06512: at "SYS.UTL_FILE", line 293
    ORA-06512: at "SYS.TEST", line 30
    ORA-06512: at line 2


    plz do needful

    it's very urgent.

    thank u in advance
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please post your code. No one can help you if we can't see what you're trying to do.
     
  3. nrupa

    nrupa Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    thank you for reply

    first I am created a table, then create a directory and then creating a procedure.

    I am posting all the code here step by step

    creating a table,

    CREATE TABLE EMP
    (
    EMPNO VARCHAR2(20 BYTE),
    ENAME VARCHAR2(10 BYTE),
    DEPTNO VARCHAR2(20 BYTE)
    );

    then creating a directory,

    CREATE OR REPLACE DIRECTORY test_files AS 'C:\';

    GRANT WRITE,READ ON DIRECTORY test_files TO PUBLIC;

    then oracle block

    DECLARE
    v_file UTL_FILE.file_type;
    v_xml CLOB;
    v_more BOOLEAN := TRUE;
    directory_name CONSTANT VARCHAR2(80) := 'test_files';
    new_xml_filename CONSTANT VARCHAR2(80) := 'test.xml';
    buffer_size CONSTANT BINARY_INTEGER := 32767;
    BEGIN
    DBMS_OUTPUT.ENABLE(100000);
    -- Create XML document from query.
    v_xml := DBMS_XMLQUERY.getxml('SELECT * from EMP');
    DBMS_OUTPUT.put_line('length' || LENGTH(v_xml));
    -- Output XML document to file.
    v_file := UTL_FILE.FOPEN(
    location => directory_name,
    filename => new_xml_filename,
    open_mode => 'w',
    max_linesize => buffer_size);
    WHILE v_more LOOP
    UTL_FILE.put(v_file, Substr(v_xml, 1, 32767));
    IF LENGTH(v_xml) > 32767 THEN
    v_xml := SUBSTR(v_xml, 32768);
    ELSE
    v_more := FALSE;
    END IF;
    END LOOP;
    UTL_FILE.fclose(v_file);
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255));
    UTL_FILE.fclose(v_file);
    END;
    /
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This

    v_xml := SUBSTR(v_xml, 32768);

    should be this

    v_xml := SUBSTR(v_xml, 32767);
     
  5. nrupa

    nrupa Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    thank you for your reply,

    but it's not working :(

    it's give same error msg,


    Error report:
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at "SYS.UTL_FILE", line 120
    ORA-06512: at "SYS.UTL_FILE", line 293
    ORA-06512: at line 14
    06510. 00000 - "PL/SQL: unhandled user-defined exception"
    *Cause: A user-defined exception was raised by PL/SQL code, but
    not handled.
    *Action: Fix the problem causing the exception or write an exception
    handler for this condition. Or you may need to contact your
    application administrator or DBA.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It seems an access related issue with the directory. Where is Oracle server ? Where did you create Oracle directory ?
     
  7. nrupa

    nrupa Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    In my Local Machine
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Oracle server also in you local machine ?
     
  9. nrupa

    nrupa Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    yes, everything in my local machine

    and also i have rights to create and modified directory 'c:/'
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Please show us the outout for

    Code (SQL):

    SELECT owner, directory_name, directory_path   FROM all_directories ;
     
    (from sqlplus)
     
  11. nrupa

    nrupa Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    SQL> show parameter utl_file_dir

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    utl_file_dir string
     
  12. nrupa

    nrupa Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    Owner directory_name
    ------------------------------ ------------------------------
    directory_path
    --------------------------------------------------------------------------------
    sys test_files
    c:\
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to speak with your DBA as it appears the utl_file package is in need of attention.
     
  14. nrupa

    nrupa Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    thank u for ur rpl

    can i download or insatall this package in my oracle version 9.0.1
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I did not see that utl_file_dir is NULL; in 9.0.1 you need to set that parameter to the directory or directories where you want to write files. 9i requires that the directory name be a string and that it match an entry in the utl_file_dir parameter. Not until 10g did utl_file use internal directories created with the 'create or replace directory' syntax.
     
  16. nrupa

    nrupa Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    hey at where I set that parameter to the directory or directories for UTL_FILE_DIR
     
  17. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    As per your code.. u dont need to worry about UTL_FILE_DIR.

    Btw.. By any chanse, did you create the oracle directory as below ?

    Code (SQL):
    CREATE OR REPLACE DIRECTORY test_files AS 'c:\';  --> lower letter "c" ?
    This could be a reason for the error.