Stored procedure - PL/SQL - Insertion error

Question

I'm trying to create a stored procedure that inserts a single row into a table. The parameters passed to this procedure are to be inserted into the table.

CREATE OR REPLACE PROCEDURE proc_sal(
    ename IN VARCHAR2, basic IN NUMBER,
    hra IN NUMBER, doj IN VARCHAR2, gender IN VARCHAR2,
    deptcode IN VARCHAR2, err OUT NUMBER, emsg OUT VARCHAR2)
IS
    eid VARCHAR2(30);
    no NUMBER;
BEGIN
   SELECT salseq.NEXTVAL INTO no FROM dual;

   eid := CONCAT(deptcode,LPAD(no,5,'0'));

   INSERT INTO salary_det
   VALUES('eid', 'ename', basic, hra, 'doj', 'gender');

   err := 1;
EXCEPTION
  WHEN OTHERS THEN
    err := SQLCODE;
    emsg := SQLERRM;
    DBMS_OUTPUT.PUT_LINE(emsg);
END;

Calling this procedure through an anonymous block:

DECLARE
   errcd NUMBER;
   errmsg VARCHAR2(200);
BEGIN
   proc_sal('&empname',&basic, &hra,'&date','&gender','&deptcode', errcd, errmsg);
   DBMS_OUTPUT.PUT_LINE(errcd||' '||errmsg);
END;

And following is the output I receive:

   Enter value for empname: Mohan
   Enter value for basic: 56000
   Enter value for hra: 560
   Enter value for date: 12-JUL-12
   Enter value for gender: M
   Enter value for deptcode: EA
   old   5:  proc_sal('&empname',&basic, &hra, '&date','&gender','&deptcode', errcd,  errmsg);
   new   5:  proc_sal('Mohan',56000, 560, '12-JUL-12','M','EA', errcd, errmsg);

   ORA-01858: a non-numeric character was found where a numeric was expected       
   -1858 ORA-01858: a non-numeric character was found where a numeric was expected 

   PL/SQL procedure successfully completed.

Everything else is working in procedure when tried to run separately. Insertion happens when tried in sql. No mismatch of column names or values, still can't figure out what is wrong.

The insert command is trying to insert string values here: VALUES('eid', 'ename', basic, hra, 'doj', 'gender'); - 'eid', 'ename', 'doj' and 'gender' are strings literals because they are enclosed in apostrophes. — krokodilko
– krokodilko, Commented Nov 1, 2013 at 19:26

Przemyslaw Kruglej · Accepted Answer · 2013-11-01 19:26:38Z

1

The problem is that you enclosed variable's names in apostrophes, try this:

CREATE OR REPLACE PROCEDURE proc_sal(
    ename IN VARCHAR2, basic IN NUMBER,
    hra IN NUMBER, doj IN VARCHAR2, gender IN VARCHAR2,
    deptcode IN VARCHAR2, err OUT NUMBER, emsg OUT VARCHAR2)
IS
  eid VARCHAR2(30);
  no NUMBER;
BEGIN
  SELECT salseq.NEXTVAL INTO no FROM dual;

  eid := CONCAT(deptcode,LPAD(no,5,'0'));

  INSERT INTO salary_det
    VALUES(eid, ename, basic, hra, doj, gender);

  err := 1;
EXCEPTION
  WHEN OTHERS THEN
    err := SQLCODE;
    emsg := SQLERRM;
    DBMS_OUTPUT.PUT_LINE(emsg);
END;

answered Nov 1, 2013 at 19:26

Przemyslaw Kruglej

8,1412 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored procedure - PL/SQL - Insertion error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related