Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    41

    Unanswered: urgent reply is required for Triggers

    Hi All,

    I have a trigger body of lines 2873 in database.

    i am trying to fetch the trigger_body of that trigger to some file,
    but i am getting Value or Numeric error.

    i am not understanding what could be the problem,
    i am thinking since trigger_body is of LONG datatype,
    is it creating any problem.

    thanks in advance.
    'A candle will loose nothing by lighting an another candle'

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Have you tried 'set long 10000' or some other arbitrarily high number?

    Also posting your select statement and version numbers might not hurt.
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Feb 2004
    Posts
    41
    Thank you very much for the reply,
    Yes i tried set long also, but still the problem exists.
    here i am pasting my code. actually i am trying to read long value piece by piece and write into some file, so that that file is used in my scripts.

    here is the sql

    create or replace procedure showlong( p_query in varchar2,
    p_name in varchar2,
    p_value in varchar2 )
    as
    l_cursor integer default dbms_sql.open_cursor;
    l_n number;
    l_long_val varchar2(200);
    l_long_len number;
    l_buflen number := 200;
    l_curpos number := 0;
    v_fileh UTL_FILE.FILE_TYPE;
    begin
    v_fileh := UTL_FILE.FOPEN('/backup_vol','triggers.sql','w');
    dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
    dbms_sql.bind_variable( l_cursor, p_name, p_value );

    dbms_sql.define_column_long(l_cursor, 1);
    l_n := dbms_sql.execute(l_cursor);

    if (dbms_sql.fetch_rows(l_cursor)>0)
    then
    loop
    dbms_sql.column_value_long(l_cursor, 1, l_buflen,
    l_curpos , l_long_val,
    l_long_len );
    l_curpos := l_curpos + l_long_len;
    --dbms_output.put_line( l_long_val );
    UTL_FILE.PUT_LINE (v_fileh,l_long_val);
    exit when l_long_len = 0;
    end loop;
    end if;
    dbms_output.put_line( '====================' );
    dbms_output.put_line( 'Long was ' || l_curpos ||
    ' bytes in length' );
    dbms_sql.close_cursor(l_cursor);
    exception
    when others then
    if dbms_sql.is_open(l_cursor) then
    dbms_sql.close_cursor(l_cursor);
    end if;
    raise;
    end showlong;
    /

    set echo on
    begin
    showlong( 'select trigger_body from user_triggers where trigger_name = :x', ':x',
    'TRIG_AUD_CLASSOFSERVICE' );
    end;
    /

    this is working fine, but its not writing the completing code, last some 10 lines of code is not written, i am doubting about exit criteria of the code.

    pls do the needful.
    thanks in advance
    'A candle will loose nothing by lighting an another candle'

  4. #4
    Join Date
    Feb 2004
    Posts
    41
    ok this problem is solved. the mistake was file handler v_fileh was not closed.
    its working fine.

    but i want to know one more thing can i find out the size or number of lines of trigger_body from the database.
    'A candle will loose nothing by lighting an another candle'

Posting Permissions

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