Oracle/PLSQL: ORA-06550 error and PLS-00221:is not a procedure or is undefined
1. Problem: The user gets a ORA-06550 error along with PLS-00221while trying to insert a row into a table called Employee using Dynamic SQL. He runs the following PL/SQL code in SQL Developer:
Executing the Function: (INSERT_RECORD)
SET SERVEROUTPUT ON
DECLARE
BEGIN
EMPLOYEE_OPERATIONS.INSERT_RECORD('EMPLOYEE','ABC','DEF');
END;
BEGIN
EMPLOYEE_OPERATIONS.INSERT_RECORD('EMPLOYEE','ABC','DEF');
END;
Package Specification:
create or replace
PACKAGE EMPLOYEE_OPERATIONS
IS FUNCTION INSERT_RECORD(
P_TABLE_NAME VARCHAR2,
P_FIRST_NAME EMPLOYE.FIRST_NAME%TYPE,
P_LAST_NAME EMPLOYE.LAST_NAME%TYPE)
RETURN NUMBER;
END EMPLOYEE_OPERATIONS;
IS FUNCTION INSERT_RECORD(
P_TABLE_NAME VARCHAR2,
P_FIRST_NAME EMPLOYE.FIRST_NAME%TYPE,
P_LAST_NAME EMPLOYE.LAST_NAME%TYPE)
RETURN NUMBER;
END EMPLOYEE_OPERATIONS;
Package Body:
create or replace
PACKAGE BODY EMPLOYEE_OPERATIONS
IS FUNCTION INSERT_RECORD(
P_TABLE_NAME VARCHAR2,
PACKAGE BODY EMPLOYEE_OPERATIONS
IS FUNCTION INSERT_RECORD(
P_TABLE_NAME VARCHAR2,
P_FIRST_NAME EMPLOYE.FIRST_NAME%TYPE,
P_LAST_NAME EMPLOYE.LAST_NAME%TYPE)
RETURN NUMBER IS
BEGIN
EXECUTE IMMEDIATE ' INSERT INTO ' || P_TABLE_NAME || ' VALUES (:1,:2) ' USING P_FIRST_NAME,P_LAST_NAME;
RETURN SQL%ROWCOUNT;
END INSERT_RECORD;
END EMPLOYEE_OPERATIONS;
P_LAST_NAME EMPLOYE.LAST_NAME%TYPE)
RETURN NUMBER IS
BEGIN
EXECUTE IMMEDIATE ' INSERT INTO ' || P_TABLE_NAME || ' VALUES (:1,:2) ' USING P_FIRST_NAME,P_LAST_NAME;
RETURN SQL%ROWCOUNT;
END INSERT_RECORD;
END EMPLOYEE_OPERATIONS;
User gets the following Error:
Error starting at line 5 in command:
DECLARE
BEGIN
EMPLOYEE_OPERATIONS.INSERT_RECORD('EMPLOYEE','ABC','DEF');
END;
Error report:
ORA-06550: line 3, column 1:
PLS-00221: 'INSERT_RECORD' is not a procedure or is undefined
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
2. Impact: The impact is high as user cannot insert values in the table using Dynamic SQL.DECLARE
BEGIN
EMPLOYEE_OPERATIONS.INSERT_RECORD('EMPLOYEE','ABC','DEF');
END;
Error report:
ORA-06550: line 3, column 1:
PLS-00221: 'INSERT_RECORD' is not a procedure or is undefined
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
3. Solution: The user gets the error because he is executing a function and not a procedure and hence has to declare a variable in the anonymous block while executing the function. When the user executes the function in the above manner then the error message says "is not a procedure or is undefined" because the compiler tells the user that he is trying to execute a function as a procedure. This is because a function is returning a value or the result and the value/result that is being returned has to be stored in a variable before it can be displayed. After the value is stored in the variable the user can use DBMS_OUTPUT.PUT_LINE to display the same. The execution code should be written like mentioned below:
SET SERVEROUTPUT ON
DECLARE
VINS_RESULT NUMBER;
BEGIN
VINS_RESULT := EMPLOYEE_OPERATIONS.INSERT_RECORD('EMPLOYEE','ABC','DEF');
DBMS_OUTPUT.PUT_LINE(VINS_RESULT);
END;
DECLARE
VINS_RESULT NUMBER;
BEGIN
VINS_RESULT := EMPLOYEE_OPERATIONS.INSERT_RECORD('EMPLOYEE','ABC','DEF');
DBMS_OUTPUT.PUT_LINE(VINS_RESULT);
END;
anonymous block completed
1 row inserted.
1 row inserted.
No comments:
Post a Comment