Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    7

    Unanswered: How to copy an Oracle Long Column in PL/SQL

    Hello. Can anyone tell me how to copy a table with a long column using PL/SQL? I tried the following code using a cursor but it errors out (ORA-06502: PL/SQL: numeric or value error)

    The cursor copy works when I create the tmp table without the long and remove the long column from the cursor copy.

    SQLWKS> CREATE TABLE "Tmp_CB_REPORT"
    2> (
    3> CB_RPT_ID NUMBER(38, 0),
    4> CB_ID NUMBER(38, 0),
    5> CB_REGION_ID NUMBER(38, 0),
    6> WHEN_PULLED DATE NOT NULL,
    7> PARSED NUMBER(1, 0) DEFAULT (0) NOT NULL,
    8> RAW_DATA LONG NOT NULL,
    9> JOINT NUMBER(1, 0) DEFAULT (0) NOT NULL,
    10> RPTTYPE NUMBER(38, 0) NOT NULL,
    11> OPTION1 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    12> OPTION2 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    13> OPTION3 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    14> OPTION4 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    15> OPTION5 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    16> OPTION6 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    17> OPTION7 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    18> OPTION8 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    19> OPTION9 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    20> OPTION10 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    21> OPTION11 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    22> OPTION12 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    23> OPTION13 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    24> OPTION14 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    25> OPTION15 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    26> OPTION16 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    27> OPTION17 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    28> OPTION18 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    29> OPTION19 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    30> OPTION20 NUMBER(1, 0) DEFAULT (0) NOT NULL,
    31> REGION_ID NUMBER(38, 0),
    32> RPT_TYPE_DESC VARCHAR2(40)
    33> ) ;
    Statement processed.
    SQLWKS> /
    SQLWKS>
    SQLWKS> LOCK TABLE CB_REPORT IN EXCLUSIVE MODE NOWAIT;
    Statement processed.
    SQLWKS>
    SQLWKS> DECLARE
    2> CURSOR datacursor IS SELECT CB_RPT_ID, CB_ID, CB_REGION_ID, WHEN_PULLED, PARSED, RAW_DATA, JOINT, RPTTYPE, OPTION1, OPTION2, OPTION3, OPTION4, OPTION5, OPTION6, OPTION7, OPTION8, OPTION9, OPTION10, OPTION11, OPTION12, OPTION13, OPTION14, OPTION15, OPTION16, OPTION17, OPTION18, OPTION19, OPTION20, REGION_ID, RPT_TYPE_DESC FROM CB_REPORT;
    3> datarecord datacursor%ROWTYPE;
    4> BEGIN
    5> OPEN datacursor;
    6> LOOP
    7> FETCH datacursor INTO datarecord;
    8> EXIT WHEN (datacursor%NOTFOUND);
    9> INSERT INTO "Tmp_CB_REPORT"(CB_RPT_ID, CB_ID, CB_REGION_ID, WHEN_PULLED, PARSED, RAW_DATA, JOINT, RPTTYPE, OPTION1, OPTION2, OPTION3, OPTION4, OPTION5, OPTION6, OPTION7, OPTION8, OPTION9, OPTION10, OPTION11, OPTION12, OPTION13, OPTION14, OPTION15, OPTION16, OPTION17, OPTION18, OPTION19, OPTION20, REGION_ID, RPT_TYPE_DESC) VALUES (datarecord.CB_RPT_ID, datarecord.CB_ID, datarecord.CB_REGION_ID, datarecord.WHEN_PULLED, datarecord.PARSED, datarecord.RAW_DATA, datarecord.JOINT, datarecord.RPTTYPE, datarecord.OPTION1, datarecord.OPTION2, datarecord.OPTION3, datarecord.OPTION4, datarecord.OPTION5, datarecord.OPTION6, datarecord.OPTION7, datarecord.OPTION8, datarecord.OPTION9, datarecord.OPTION10, datarecord.OPTION11, datarecord.OPTION12, datarecord.OPTION13, datarecord.OPTION14, datarecord.OPTION15, datarecord.OPTION16, datarecord.OPTION17, datarecord.OPTION18, datarecord.OPTION19, datarecord.OPTION20, datarecord.REGION_ID, datarecord.RPT_TYPE_DESC);
    10> END LOOP;
    11> END;
    12> /
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 7
    SQLWKS>

    Any help is appreciated.

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    It works ?

    Hello,

    I have written a little test procedure and it works ...
    Here it is ... it runs without throwing an exception

    PROCEDURE SCOTT.TESTPROC
    IS
    CURSOR cuSelect IS
    SELECT feld1, feldlong FROM scott.testtab;
    rRec cuSelect%ROWTYPE;
    BEGIN
    OPEN cuSelect;
    FETCH cuSelect INTO rRec;
    WHILE cuSelect%FOUND LOOP
    dbms_output.put_line('Long = ' || rRec.feldlong);
    FETCH cuSelect INTO rRec;
    END LOOP;
    CLOSE cuSelect;

    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Fehler');
    END;

    Defintion feld1 = VARCHAR2
    feldlong long

    Database 8.1.6

    Regards
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  3. #3
    Join Date
    May 2002
    Posts
    7
    Hello:

    I modified you SQL to copy the records into a similar table and it also works. So it appears there is a problem with the data in the original table, although I do not know what it is.

    Thanks for your help.

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Lightbulb

    Hello,

    could it be, that the LENGTH of the LONG field is greater that 32767 ?
    I rember that %ROWTYPE with a LONG converts the field to char and
    operation on VARCHAR > 32767 throws exceptions (that depends on the PL/SQL version you are using)

    That is only an idea ....

    Regards
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

Posting Permissions

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