Forum Stats

  • 3,781,168 Users
  • 2,254,486 Discussions
  • 7,879,602 Comments

Discussions

Procedure - In out Parameter

2621063
2621063 Member Posts: 36
edited Mar 1, 2015 1:53AM in SQL & PL/SQL

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

Tagged:
2621063JSM5683

Best Answer

  • Unknown
    edited Feb 28, 2015 11:28AM Accepted 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
    
    JSM5683

Answers

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Feb 28, 2015 10:53AM

    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

    2621063JSM56832621063
  • Unknown
    edited Feb 28, 2015 11:28AM Accepted 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
    
    JSM5683
  • 2621063
    2621063 Member Posts: 36
    edited Feb 28, 2015 10:01PM

    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
    2621063 Member Posts: 36
    edited Mar 1, 2015 12:10AM

    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
    Etbin Member Posts: 8,968 Gold Crown
    edited Mar 1, 2015 1:53AM

    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

This discussion has been closed.