Results 1 to 3 of 3

Thread: Long in cursor

  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Arrow Unanswered: Long in cursor

    Hello,

    I create a cursor with a column with a long datatype. When this value is really big. It returns an error at runtime:

    .ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 16

    Can anyone tell me if it is possible to walk through long columns?

    ----------------------
    DECLARE
    sBody LONG;
    CURSOR TrigCursor IS
    SELECT TRIGGER_BODY
    FROM DBA_TRIGGERS
    WHERE TRIGGER_NAME = 'TEST'
    ;
    BEGIN

    FOR TrigRecord IN TrigCursor LOOP
    NULL;
    END LOOP;

    END;
    /

    -----------------------

    thanx

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not "just like that", because LONGs are a pig to work with. I think you can read LONGs of arbitrary size using the DBMS_SQL package's COLUMN_VALUE_LONG procedure, but haven't done it myself.

  3. #3
    Join Date
    Nov 2002
    Posts
    833
    if you only want to see what's in your dba_triggers, why not use sqlplus


    SQL> set long 90000;


    SQL> select trigger_body from dba_triggers where rownum < 2;

    TRIGGER_BODY
    ---------------------------------------------------------------------

    DECLARE
    prop_count NUMBER;
    BEGIN
    SELECT count(*) into prop_count
    FROM system.def$_propagator;

    IF (prop_count > 0) THEN
    -- Raise duplicate propagator error
    sys.dbms_sys_error.raise_system_error(-23394);
    END IF;
    END;

    TRIGGER_BODY
    ---------------------------------------------------------------------
    p.s. for own objects I strongly suggest changing the datatype to {b,c}lob

Posting Permissions

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