Create sqlplus package

Question

I am trying to create a package from the following procedure

CREATE OR REPLACE PROCEDURE insert_rows (pl_deptno dept.deptno%TYPE, pl_dname dept.dname%TYPE, pl_loc dept.loc%TYPE ) AS BEGIN INSERT INTO dept (deptno,dname,loc) values ( pl_deptno,pl_dname,pl_loc); commit; end insert_rows; / 

So far this is my package that creates small

 create or replace package fpf is procedure insert_rows (p_deptno IN dept.deptno%TYPE, p_dname IN dept.dname%TYPE, p_loc IN dept.loc%TYPE); end fpf; / 

But when I create the package body, I get compilation errors, any ideas?

 create or replace package body fpf as procedure insert_rows (p_deptno IN dept.deptno%TYPE, p_dname IN dept.dname%TYPE, p_loc IN dept.loc%TYPE) as BEGIN INSERT INTO dept (deptno,dname,loc) values ( pl_deptno,pl_dname,pl_loc); end insert_rows; end fpf; / 

Answer 1

When you receive compilation errors, you will want to know what errors you received. In SQL * Plus, you can do this using the show errors command

 SQL> create or replace package body fpf 2 as 3 procedure insert_rows 4 (p_deptno IN dept.deptno%TYPE, 5 p_dname IN dept.dname%TYPE, 6 p_loc IN dept.loc%TYPE) 7 as 8 BEGIN 9 INSERT INTO dept 10 (deptno,dname,loc) 11 values ( pl_deptno,pl_dname,pl_loc); 12 end insert_rows; 13 end fpf; 14 / Warning: Package Body created with compilation errors. SQL> sho err Errors for PACKAGE BODY FPF: LINE/COL ERROR -------- ----------------------------------------------------------------- 9/1 PL/SQL: SQL Statement ignored 11/29 PL/SQL: ORA-00984: column not allowed here 

Errors tell you that the compiler believes that the keyword on line 11, column 29 is the name of the column, and the column names at this point in your code are not resolved. Row 11 of column 29 is the identifier pl_loc . Presumably you did not assume this was a reference to a column name. Presumably, you assumed that this is the name of the parameter. But Oracle does not recognize this identifier as a parameter. This is because your parameter has the name p_loc not pl_loc (note the extra l ).

If you correct the name of all three parameters, the code compiles

 Wrote file afiedt.buf 1 create or replace package body fpf 2 as 3 procedure insert_rows 4 (p_deptno IN dept.deptno%TYPE, 5 p_dname IN dept.dname%TYPE, 6 p_loc IN dept.loc%TYPE) 7 as 8 BEGIN 9 INSERT INTO dept 10 (deptno,dname,loc) 11 values ( p_deptno,p_dname,p_loc); 12 end insert_rows; 13* end fpf; SQL> / Package body created.