I create one PL/SQL procedure i want simple show two messages?

Question

The PL/SQL procedure below:

'DECLARE

V_EMPNO NUMBER(10):=&EMPNO;

V_EMPNO2 NUMBER(10):= 0;

CURSOR C1 IS SELECT EMPNO FROM EMP;

BEGIN

FOR I IN C1 LOOP

FETCH C1 INTO V_EMPNO2;

EXIT WHEN C1%FOUND;

END LOOP;

IF (LENGTH(V_EMPNO)) > 4 THEN

DBMS_OUTPUT.PUT_LINE ('LENGTH OF EMPNO GREATER THAN 4 NUMBER');

ELSIF (V_EMPNO = V_EMPNO2) THEN

DBMS_OUTPUT.PUT_LINE ('THIS EMPLOYEE NUMBER ALREADY EXIST');

END IF;

END;

/'

In this procedure I want show two messages one is if lenght greater than number 4 than show message and second is if v_empno = v_empno2 then show second message

empno = v_empno then show message:

DBMS_OUTPUT.PUT_LINE ('THIS EMPLOYEE NUMBER ALREADY EXIST')

this is error 

Enter value for empno: 4444

DECLARE

*

ERROR at line 1:

ORA-01001: invalid cursor

ORA-06512: at line 7

btw it's not a procedure, it's a block. And why is in in quotes? — William Robertson, Sep 12 '16 at 21:41

score 1 · Answer 1 · answered Sep 12 '16 at 19:16

1

FOR I IN C1 LOOP

already implicitly opens c1 and handles the fetching, so your explicit fetch after it is invalid.

btw i is normally used for numeric indexes rather than records.

Also your caps lock was on when you wrote that code ;)

answered Sep 12 '16 at 19:16

William Robertson

15,273
4
38
44

score 0 · Accepted Answer · answered Sep 13 '16 at 15:19

I think there are a couple of problems with your code.

C1 does not restrict on employee number (meaning the loop will return a single, largely random row from table emp
You are mixing FOR LOOP and FETCH syntax
Variable v_empno is a NUMBER and you need to be careful when checking the length - you need to explicitly TO_CHAR and control the format - often TO_CHAR will end up including space characters (an alternative would be to check the value of a number is < 10000)

I've not tested this code but this might be closer to what you're after :

DECLARE

  l_empno        NUMBER := &empno ;

  CURSOR C_get_emp
  IS
    SELECT e.empno
    FROM emp         e
    WHERE e.empno = l_empno
  ;
  R_emp          C_get_emp%ROWTYPE ;

BEGIN

  IF LENGTH(TRIM(TO_CHAR(l_empno))) > 4 THEN

    DBMS_OUTPUT.put_line('Length of empno > 4') ;

  ELSE

    OPEN C_get_emp ;
    FETCH C_get_emp INTO R_emp ;
    IF C_get_emp%FOUND THEN
      DBMS_OUTPUT.put_line('Employee number already exists') ;  
    END IF ;
    CLOSE C_get_emp ;


  END IF ;  

END ;

Your example is probably hypothetical, but I cannot think of a good reason why you would restrict the length of a numeric ID field in this way - the ID is likely generated from a sequence and why would you restrict to 9999 IDs? — Christian Palmer, Sep 13 '16 at 15:23

I create one PL/SQL procedure i want simple show two messages?

2 Answers2