Skip to Main Content

Hardware

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle’s SPARC T8 and SPARC M8 Server Reliability, Availability, and Serviceability White Paper

Melvis-OracleSep 13 2017 — edited Feb 21 2020

Comments

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-7404
Answer
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

Marked as Answer by 2621063 · Sep 27 2020
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

1 - 5

Post Details