Results 1 to 6 of 6

Thread: Help - Again

  1. #1
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Unanswered: Help - Again

    Hi,
    I have modified the procedure Given by Mr Andrew . But When there are incompatible datatypes this is throwing an error and stopping. How can I continu ewith the execution ?
    I am executing the proc as follows:

    DECLARE
    tabname VARCHAR2(100);
    filename VARCHAR(2000);
    outfile utl_file.file_type;
    stmt VARCHAR(100):=' ';
    BEGIN
    FOR tabnamecur IN ( SELECT * FROM TAB )
    LOOP
    filename:=tabnamecur.tname;
    stmt :=' ';
    tabname := tabnamecur.tname;
    stmt := 'SELECT * FROM '||tabname;
    t2c( stmt ,filename);
    END LOOP;
    utl_file.fclose(outfile);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    utl_file.fclose(outfile);
    END;

    /***********Procedure**********/

    CREATE OR REPLACE PROCEDURE T2c( p_query IN VARCHAR2,filename IN VARCHAR2 )
    AUTHID CURRENT_USER
    IS
    v_cursor INTEGER := dbms_sql.open_cursor;
    v_column_value VARCHAR2(4000);
    v_status INTEGER;
    v_desc_tab dbms_sql.desc_tab;
    v_numcols NUMBER;
    v_record VARCHAR2(32000);
    outfile utl_file. file_type;
    BEGIN
    outfile := utl_file.FOPEN('DBDIR',filename,'w');
    EXECUTE IMMEDIATE
    'ALTER SESSION SET
    nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    dbms_sql.parse( v_cursor, p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( v_cursor, v_numcols, v_desc_tab );

    FOR i IN 1 .. v_numcols LOOP
    dbms_sql.define_column
    (v_cursor, i, v_column_value, 4000);
    END LOOP;

    v_status := dbms_sql.EXECUTE(v_cursor);

    WHILE ( dbms_sql.fetch_rows(v_cursor) > 0 ) LOOP
    v_record := NULL;
    FOR i IN 1 .. v_numcols LOOP
    dbms_sql.column_value
    ( v_cursor, i, v_column_value );
    v_record := v_record || '"' || v_column_value ||'",';
    END LOOP;
    dbms_output.put_line( RTRIM(v_record,',') );
    utl_file.PUT(outfile,RTRIM(v_record,','));
    utl_file.NEW_LINE(outfile,1);
    END LOOP;
    utl_file.fclose(outfile);
    EXECUTE IMMEDIATE
    'ALTER SESSION SET nls_date_format=''dd-MON-rrrr'' ';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    utl_file.fclose(outfile);
    WHEN OTHERS THEN
    utl_file.fclose(outfile);
    EXECUTE IMMEDIATE
    'ALTER SESSION SET nls_date_format=''dd-MON-rrrr'' ';
    RAISE;

    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help - Again

    What data types? What error?

  3. #3
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Re: Help - Again

    hi,
    The error that I am getting is :
    (when i describe the table - the datatype is raw - so i want it to ignore this error and continue - how to achieve that ?
    *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got SYS.AQ$_JMS_TEXT_MESSAGE
    ORA-06512: at "SCOTT.T2C", line 45
    ORA-06512: at line 15

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help - Again

    If you interrogate v_desc_tab.col_type you can find out the datatype of the column (e.g. I think 23 means RAW). You can then handle different data types differently - or skip those columns altogether.

  5. #5
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Re: Help - Again

    hi ,
    What do you mean by intrrogate ????????????/ yeah the data type is raw - so how can i skip raw cols ?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help - Again

    I mean, make the procedure aware that the column is RAW (or whatever) so that it knows it can't concatenate them into the string. You know:
    PHP Code:
    IF v_desc_tab(i).col_type IN (12, ...) THEN
      
    -- We can handle these
      dbms_sql
    .column_valuev_cursoriv_column_value );
      
    v_record := v_record || '"' || v_column_value ||'",';
    END IF; 
    So anything it can't handle, it doesn't try to handle.

Posting Permissions

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