Can you make a function in a procedure pl/sql??

[bgreen]

I want to write a function that calculates a number.


create or replace function POS (ln_pos is NUMBER)
RETURN NUMBER IS
v_pos number;
BEGIN
v_pos := ln_pos * 300;
end;

Here is a skeleton of my procedure. Where eould I put the function? Did I bui;d it correctly?

SET SERVEROUTPUT ON
SET DEFINE OFF

CREATE OR REPLACE PROCEDURE chqtest1 IS

.. variables ..

BEGIN

.. body ...

dbms_output.put_line(POS(4.32));

EXCEPTION

... exceptions ...

END;
/
SET SERVEROUTPUT OFF
SET DEFINE ON

----- output ----
1296

 

[LKBrwnDBA]

You need to put a RETURN in you function:

create or replace function POS (ln_pos is NUMBER)
RETURN NUMBER IS
BEGIN
  RETURN ln_pos * 300;
end;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[bgreen]

Great but where do I put the function in the procedure body.

 

[carp]

It would go in the declaration section:

CREATE OR REPLACE PROCEDURE chqtest1 IS
l_variable1 NUMBER;
l_variable2 VARCHAR2(20);
FUNCTION POS (ln_pos IN NUMBER)RETURN NUMBER IS
BEGIN
RETURN ln_pos * 300;
END;
BEGIN
-- the body of your procedure goes here
END;
[/code]

Note that your function must be declared AFTER your variables; otherwise, you get a compilation error.

 

[bgreen]

I am getting

Warning: Function created with compilation errors.

when I try to run

create or replace function POS (ln_pos is NUMBER)
RETURN NUMBER IS
BEGIN
RETURN ln_pos * 300;
end;

Is this written wrong?

 

[carp]

I'm not sure where you saw
create or replace function POS (ln_pos is NUMBER)
but it is wrong. "is" cannot be in your argument list. Please take a look at the code I provided - if nothing else, it compiles!~

 

[bgreen]

OK. I was able to create the function but not in the procedure. When I put it in the procedure my define off turns back on. The reason I do this is because one of my variables contains a &.

How can I call the function in the procedure?

 

[nadeu26]

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 FUNCTION POS (I_POS IN NUMBER) RETURN NUMBER IS
3 BEGIN
4 RETURN I_POS *300;
5 END;
6 BEGIN
7 dbms_output.put_line(POS(&N));
8 END;
9 /
Enter value for n: 4.32
old 7: dbms_output.put_line(POS(&N));
new 7: dbms_output.put_line(POS(4.32));
1296

PL/SQL procedure successfully completed.

 

[bgreen]

How can I do this in a procedure? I can do it outherwise. Can I call this function within a procedure??????

CREATE OR REPLACE PROCEDURE chqtest1 IS

.. variables ..

BEGIN

.. body ...

dbms_output.put_line(POS(4.32));

EXCEPTION

... exceptions ...

END;
/

 

[SantaMufasa]

BGreen,

Yes, you can certainly define as many functions (in functions in functions) in procedures (in procedures, in functions in procedures) et cetera, as you choose...as many levels deep as you please.

If you include "&N" in your code, it gets compiled into code as a literal having whatever value you supply for "&N". If you wish to supply a value at run-time, then you should pass it as an incoming argument to "chqtest1". Since you want the function "POS" defined within "chqtest1", then "POS" can "see" the value of the incoming argument. I'll illustrate in your modified code, below:

set serveroutput on
CREATE OR REPLACE PROCEDURE chqtest1 (n number) IS
-- variables ..
 function POS (ln_pos in NUMBER)
  RETURN NUMBER IS
 BEGIN
    RETURN ln_pos * 300;
 end;
BEGIN
-- body ...
dbms_output.enable(1000000);
dbms_output.put_line(POS(n));
EXCEPTION
 when others then
  null; -- exceptions ...
END;
/

Procedure created.

exec chqtest1(4.32)
1296

PL/SQL procedure successfully completed.

Let us know if this resolved your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:42 (13Aug04) UTC (aka "GMT" and "Zulu"), 15:42 (13Aug04) Mountain Time)