PLS-00103: Encountered the symbol “PROCEDURE” when expecting one of the following:

Question

Im a learning plsql and im trying to create procedure and calling procedure inside another procedure and i can get desired output. But when i tried to create procedure inside another procedure instead of calling another procedure, im getting the below error

"23/1 PLS-00103: Encountered the symbol "PROCEDURE" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol "declare" was substituted for "PROCEDURE" to continue. 23/18 PLS-00103: Encountered the symbol "." when expecting one of the following: in out ... long double ref char time timestamp interval date binary national character nchar Errors: check compiler log "

CREATE OR REPLACE PROCEDURE pro (
    empn   NUMBER,
    emp    OUT    emp5%rowtype

) IS
 salar number;
BEGIN
    SELECT
        a.*
    INTO emp
    FROM
        emp5 a
    WHERE
        a.empno = empn;

    dbms_output.put_line('The hire date is'
                         || '    '
                         || emp.hiredate);
    dbms_output.put_line('Name is'
                         || '   '
                         || emp.ename);

procedure p44(emp.hiredate in date,emp.ename varchar,sal out number) IS
    salar   NUMBER;
BEGIN
    SELECT
        e.sal
    INTO salar
    FROM
        emp5 e
    WHERE
        e.hiredate = hire
        AND e.ename = enamee;

    dbms_output.put_line('salary of the employee'
                         || ' '
                         || enamee
                         || 'is  '
                         || salar);

END p43;
/

If i give empno number as input to procedure pro, i need output with employees hiredate,employee's name and employee's salary like the below one

The hire date is 20-FEB-81
Name is ALLEN
salary of the employee ALLEN is 1600

Answer 1

A procedure is a single program unit with the structure of:

create or replace procedure p43 () is
  ...
begin
  …
end;
/

Your code has two instances of PROCEDURE and that's what the compiler is complaining about.

If you want two separate procedures you need two separate CREATE statements:

create or replace procedure p43 () is
  ...
begin
  …
end p43;
/
create or replace procedure p44 () is
  ...
begin
  …
end p44;
/

But if what you want is P44 as a private procedure only accessible within the context of P43 you can do that, by defining the procedure in the declaration section after any variable declarations:

create or replace procedure p43 () is
  …
   procedure p44 () is
    ...
   begin
     …
   end p44;
begin
  …
  p44(…);
end p43;
/

Also this is not how we declare parameters.

procedure p44(emp.hiredate in date,emp.ename varchar,sal out number) IS

Give them unique names, say by prefixing them with p_, and if you like use the table column datatype. Something like this:

procedure p44(p_hiredate in emp.hiredate%type
              ,p_ename   in emp.ename%type
              ,p_sal     out emp.sal%type) IS

Giving parameters a distinct name prevents scope confusion when using parameters in SQL statements:

Answer 2

You can not create procedure within procedure.

Procedure is a single object and must be created alone. You can call one procedure from another.

You can use package to create multiple procedures inside single package but in that case also, procedure must be created standalone.

-- procedure must be created standalone
Create or replace procedure p44
As
Begin
-- code
End p44;
/

Create or replace procedure pro
As
Begin
P44; -- call to existing procedure
-- code
End pro;
/

So your case will go like this:

procedure p44(hiredate in date,ename varchar,sal out number) IS
    salar   NUMBER;
BEGIN
    SELECT
        e.sal
    INTO salar
    FROM
        emp5 e
    WHERE
        e.hiredate = hire
        AND e.ename = ename;

    dbms_output.put_line('salary of the employee'
                         || ' '
                         || ename
                         || 'is  '
                         || salar);
END p44;
/

CREATE OR REPLACE PROCEDURE pro (
    empn   NUMBER,
    emp    OUT    emp5%rowtype

) IS
 salar number;
BEGIN
    SELECT
        a.*
    INTO emp
    FROM
        emp5 a
    WHERE
        a.empno = empn;

    dbms_output.put_line('The hire date is'
                         || '    '
                         || emp.hiredate);
    dbms_output.put_line('Name is'
                         || '   '
                         || emp.ename);
P44(emp.hiredate, emp.ename, salr):
END pro;
/

Cheers!!