Procedure - In out Parameter

2621063

Hi all,

I am creating one in out procedure. Given below is my code

create or replace procedure sqroot

(

  x in out number

)

is

begin

  x:= x*x;

end;

declare

  a number;

begin

    a:= sqroot(4);

end; 

I am getting the following error

"Error starting at line : 10 in command -

declare

  a number;

begin

 

  a:= sqroot(4);

end; 

Error report -

ORA-06550: line 5, column 7:

PLS-00222: no function with name 'SQROOT' exists in this scope

ORA-06550: line 5, column 3:

PL/SQL: Statement ignored

06550. 00000 -  "line %s, column %s:\n%s"

*Cause:    Usually a PL/SQL compilation error.

*Action"

Kindly advice

Unknown

I am creating one in out procedure. Given below is my code

create or replace procedure sqroot

PLS-00222: no function with name 'SQROOT' exists in this scope
 

The procedure does NOT exist yet so you can NOT reference it inside the procedure.

Use a 'forward declaration' to declare the non-existent function. See the example in the Oracle docs

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#CIHIAEDF

Forward Declaration

If nested subprograms in the same PL/SQL block invoke each other, then one requires a forward declaration, because a subprogram must be declared before it can be invoked.
A forward declaration declares a nested subprogram but does not define it. You must define it later in the same block. The forward declaration and the definition must have the same subprogram heading.
In Example 8-8, an anonymous block creates two procedures that invoke each other.
Example 8-8 Nested Subprograms Invoke Each Other

Etbin

create or replace procedure sqroot

(

  x in out number

)

is

begin

  x:= x*x;

end;

declare

  a number := 4;

begin

  dbms_output.put_line(to_char(a));

  sqroot(a);

  dbms_output.put_line(to_char(a));

end;

4

16

Statement processed.

Regards

Etbin

Unknown

I am creating one in out procedure. Given below is my code

create or replace procedure sqroot

PLS-00222: no function with name 'SQROOT' exists in this scope
 

The procedure does NOT exist yet so you can NOT reference it inside the procedure.

Use a 'forward declaration' to declare the non-existent function. See the example in the Oracle docs

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#CIHIAEDF

Forward Declaration

If nested subprograms in the same PL/SQL block invoke each other, then one requires a forward declaration, because a subprogram must be declared before it can be invoked.
A forward declaration declares a nested subprogram but does not define it. You must define it later in the same block. The forward declaration and the definition must have the same subprogram heading.
In Example 8-8, an anonymous block creates two procedures that invoke each other.
Example 8-8 Nested Subprograms Invoke Each Other

2621063

Hi,

Could you please explain the usage of to_char function. I have used the procedure that accepts number as a parameter hence passed the same.I googled and found that the to_char function is used to convert date or number to string. But still not getting the point w.r.t this code

2621063

Hi,

Gave the below code by implementing the forward declaration. Got compiled.

declare

     a number;

     procedure sqroot(a in out number)

     is

    begin

          a:=a*a;

    end;

begin

     a:=4;

     sqroot(a);

     dbms_output.put_line(a);

end;

Thank you. Hope it is correct.

Etbin

It's to avoid implicit conversions http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_output.htm#ARPLS67327

something you'll never be sorry sticking to - the same goes for the legacy reasons - just the other way around - avoid everything declared obsolete ASAP.

Regards

Etbin