CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Tao
Tao

Reputation: 113

Stored procedure - PL/SQL - Insertion error

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.

Upvotes: 1

Views: 396

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

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;

Upvotes: 1

Related Questions