Saturday 24 March 2012

Oracle/PLSQL: Dynamic SQL Errors.ORA-06550 and PLS-00221

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;
 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;
Package Body:
create or replace
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;
  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.
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;

anonymous block completed
1 row inserted.

No comments:

Post a Comment