CODEWITHSUNDEEP
oracle-databasecursorlong-integerdeclare
Sercan Sülün
Sercan Sülün

Reputation: 19

ORACLE DECLARE CURSOR for LONG datatyped Column inserting

I want to transfer values in a database to another database. But in my source database there are table with columns with LONG datatype. So i cant simply use the insert command for these LONG typed Columns.

So i read this : http://it.toolbox.com/blogs/siebel-answers/ora00997-illegal-use-of-long-datatype-when-performing-sql-insert-intoselect-from-27132

and build this code:

  DECLARE 
CURSOR b is 
select
Column_1,
Column_2
FROM DB1.TABLE1;
rb b%ROWTYPE; 
BEGIN 
OPEN b;
LOOP  
FETCH b INTO rb; 
EXIT WHEN b%NOTFOUND; 
INSERT INTO TABLE_1
(
 Column_1,
 Column_2
  )
  VALUES
  (
  rb.Column_1,
    rb.Column_2
  );
  END LOOP;
  COMMIT;
END;

It works well but then i add another blockafter it:

 DECLARE 
    CURSOR b is 
    select
    Column_1,
    Column_2
    FROM DB1.TABLE2;
    rb b%ROWTYPE; 
    BEGIN 
    OPEN b;
    LOOP  
    FETCH b INTO rb; 
    EXIT WHEN b%NOTFOUND; 
    INSERT INTO TABLE_2
    (
     Column_1,
     Column_2
      )
      VALUES
      (
      rb.Column_1,
      rb.Column_2
      );
      END LOOP;
      COMMIT;
    END;

 DECLARE 
    CURSOR b is 
    select
    Column_1,
    Column_2
    FROM DB1.TABLE1;
    rb b%ROWTYPE; 
    BEGIN 
    OPEN b;
    LOOP  
    FETCH b INTO rb; 
    EXIT WHEN b%NOTFOUND; 
    INSERT INTO TABLE_1
    (
     Column_1,
     Column_2
      )
      VALUES
      (
      rb.Column_1,
        rb.Column_2
      );
      END LOOP;
      COMMIT;
    END;

An error appear like this:

Error starting at line 1 in command:
DECLARE 
        CURSOR b is 
        select
        Column_1,
        Column_2
        FROM DB1.TABLE2;
        rb b%ROWTYPE; 
        BEGIN 
        OPEN b;
        LOOP  
        FETCH b INTO rb; 
        EXIT WHEN b%NOTFOUND; 
        INSERT INTO TABLE_2
        (
         Column_1,
         Column_2
          )
          VALUES
          (
          rb.Column_1,
          rb.Column_2
          );
          END LOOP;
          COMMIT;
        END;

     DECLARE 
        CURSOR b is 
        select
        Column_1,
        Column_2
        FROM DB1.TABLE1;
        rb b%ROWTYPE; 
        BEGIN 
        OPEN b;
        LOOP  
        FETCH b INTO rb; 
        EXIT WHEN b%NOTFOUND; 
        INSERT INTO TABLE_1
        (
         Column_1,
         Column_2
          )
          VALUES
          (
          rb.Column_1,
            rb.Column_2
          );
          END LOOP;
          COMMIT;
        END;
Error report:
ORA-06550: line 27, column 6:
PLS-00103: Encountered the symbol "DECLARE" 
ORA-06550: line 51, column 12:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ( begin case declare end exception exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quote
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

How can i use this structerone after another?

Upvotes: 0

Views: 376

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

Just add a / after every block. Bellow the END;

declare
var number;
begin
-do something
end;
/

Upvotes: 1

Related Questions