PDA

View Full Version : How to copy an Oracle Long Column in PL/SQL

Harold Hunsaker
06-10-02, 16:52
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.
:confused:
alligatorsql.com
06-10-02, 17:43
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
Harold Hunsaker
06-14-02, 16:53
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.:)
alligatorsql.com
06-14-02, 19:52
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