Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: genrate xml from oracle 9i

    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. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    It may be possible to help, but you will need to post the PL/SQL that is causing the error. Don't forget to use the CODE tags when posting.

  3. #3
    Join Date
    Mar 2012
    Posts
    4

    genrate xml from oracle 9i

    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. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Does the C drive exist on the database server? That is the only place that util_file can write.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2012
    Posts
    4

    genrate xml from oracle 9i

    ys C drive is exist in the database server

    there are two drive there C or D

    both Drive give me same error

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Just a few comments about provided code and error message:
    The directory name is (as any Oracle object names, when created without enclosing between double quotes) case insensitive, interpreted as uppercase. You may easily identify it from data dictionary view:
    Code:
    select owner, directory_name, directory_path from dba_directories;
    Removal of the EXCEPTION WHEN OTHERS block would help finding exactly which statement in this anonymous block (where did that SYS.TEST in the error message come from?) fails. If you were in 10g, you could use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE instead of SQLERRM to achieve it (nice reason for updating to a supported Oracle version).

    SYS.TEST - are you really running it under SYS schema? It is really very bad idea.
    http://www.orafaq.com/forum/t/164126/0/
    http://asktom.oracle.com/pls/asktom/...10583864460294
    May not be the cause of the error though - however (as it is really not wise) I am reluctant to check it.

  7. #7
    Join Date
    Apr 2012
    Posts
    2
    I think it is useful for you...

    declare
    v_xml_document clob;
    ft_logFile UTL_FILE.FILE_TYPE;
    v_dir_name VARCHAR2(250);
    v_ControlFile VARCHAR2(4000);

    begin

    select dbms_xmlquery.getxml('select * from emp') into v_xml_document from dual;
    ft_logFile := utl_file.fopen('DIR_EXT','mk.xml' ,'W');
    utl_file.putf(ft_logFile,'%s\n',v_xml_document);
    utl_file.fclose(ft_logFile);

    end;
    /

  8. #8
    Join Date
    Mar 2012
    Posts
    4

    set xmlnode using

    I am using xml element in my query as follows

    SELECT XMLELEMENT("name", e.ename) AS employee
    FROM emp e
    WHERE e.empno = 1;

    and I am getting following error

    Error starting at line 1 in command:
    SELECT XMLELEMENT("name", e.ename) AS employee
    FROM emp e
    WHERE e.empno = 1
    Error at Command Line:1 Column:7
    Error report:
    SQL Error: ORA-00904: invalid column name
    00904. 00000 - "%s: invalid identifier"

    if there any other way to set xmlnode using oracle9i

    it's urgent please help me out

    Thank you in advance

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    XMLELEMENT function was introduced in Oracle 9iR2 (good reason to post your full 4digit Oracle version), as stated in Oracle documentation: http://docs.oracle.com/cd/B10501_01/...sql.htm#971925

    What was wrong with DBMS_XMLGEN? It should be available even in Oracle 9iR1. What is "set xmlnode using oracle9i"? XMLELEMENT returns variable with XMLTYPE data type, which is also not present in 9iR1.

Posting Permissions

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