CODEWITHSUNDEEP
oracle-databasestored-procedures
user28468804
user28468804

Reputation: 1

Oracle stored procedure: String length constraints must be in range (1 .. 32767)

CREATE OR REPLACE PROCEDURE TASK_INSERT_PROC (PV_PROCESSID VARCHAR2)
AS
   lv_taskid_t     ftdmaster.statuscode%TYPE;
   lv_txrefno_t    ftdmaster.txrefno%TYPE;
   lv_processid_t  ftdmaster.processid%TYPE;
   cnt             NUMBER := 0;
BEGIN
   FOR cnt IN (SELECT statuscode, TXREFNO, PROCESSID
                 FROM ftdmaster
                WHERE     PROCESSID = PV_PROCESSID
                      AND statuscode = '7')
   LOOP
      SELECT statuscode, TXREFNO, PROCESSID
        INTO lv_taskid_t, lv_txrefno_t, lv_processid_t
        FROM ftdmaster
       WHERE     PROCESSID = PV_PROCESSID
             AND statuscode = '7';

      INSERT INTO TASK_TEMP (TASK_temp.TASKID, TASK_temp.TXREFNO, TASK_temp.PROCESSID)
           VALUES (lv_taskid_t, lv_txrefno_t, lv_processid_t);
   END LOOP COMMIT;
END TASK_INSERT_PROC;

----Execution--- 

DECLARE
   v_processid  VARCHAR2 := 'ET06';
BEGIN
   TASK_INSERT_PROC (v_processid);
END;

I'm getting this error for my code:

Error starting at line : 22 in command -

declare v_processid varchar2:='ET06'; begin TASK_INSERT_PROC(v_processid); end;

Error report - ORA-06550: line 2, column 13: PLS-00215: String length constraints must be in range (1 .. 32767) 06550. 00000 - "line %s, column %s:\n%s"

*Cause: Usually a PL/SQL compilation error.
*Action:

Upvotes: 0

Views: 28

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

That code fails before it even has a chance to be executed because procedure can't be compiled - it misses a semi-colon after END LOOP.

Furthermore:

  • you don't need any local variables because everything is already contained in a cursor variable (cnt), so - don't declare it as a number
  • the 2nd select is superfluous; as I said, cursor does it all

So:

CREATE OR REPLACE PROCEDURE TASK_INSERT_PROC (PV_PROCESSID ftdmaster.processid%TYPE)
AS
BEGIN
   FOR cnt IN (SELECT statuscode, TXREFNO, PROCESSID
                 FROM ftdmaster
                WHERE     PROCESSID = PV_PROCESSID
                      AND statuscode = '7')
   LOOP
      INSERT INTO TASK_TEMP (TASKID, TXREFNO, PROCESSID)
           VALUES (cnt.statuscode, cnt.txrefno, cnt.processid);
   END LOOP;

   COMMIT;
END TASK_INSERT_PROC;

Once fixed, and presuming that everything else is OK (can't test it as we don't have your tables nor data), another error - you're complaining about - is the fact that you didn't specify varchar2 variable's size:

DECLARE
   --   v_processid  VARCHAR2:= 'ET06'; --> should've been e.g. VARCHAR2(20), but better
   --                                       option is to use ...
   v_processid  ftdmaster.processid%TYPE := 'ET06';
BEGIN
   TASK_INSERT_PROC (v_processid);
END;

On the other hand, why are you using a cursor FOR loop? A simpler - and better - option would be

CREATE OR REPLACE PROCEDURE TASK_INSERT_PROC (PV_PROCESSID ftdmaster.processid%TYPE)
AS
BEGIN
   INSERT INTO TASK_TEMP (TASKID, TXREFNO, PROCESSID)
      SELECT statuscode, TXREFNO, PROCESSID
        FROM ftdmaster
       WHERE     PROCESSID = PV_PROCESSID
             AND statuscode = '7';
END;

As you can see, I didn't commit within the procedure. Usually, it is better to let that decision to the caller.

Upvotes: 1

Related Questions