Creating Package sqlplus

oracle11g, sqlplus

Solution

When you get compilation errors, you'll want to see what errors you received. In SQL*Plus, you can do that using the command `show errors`

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

The errors are telling you that the compiler thinks that the keyword at line 11, column 29 is a column name and that column names aren't allowed at that point in your code. Line 11 column 29 is the `pl_loc` identifier. Presumably, you didn't intend that to be a reference to a column name. Presumably, you intended that to be the name of a parameter. But Oracle doesn't recognize that identifier as the parameter. This is because your parameter is named `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.

Problem

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 sec which, creates fine ``` 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; / ```

Original source