How to call a pl/sql anonymous block from an pl/sql anonymous block

Question

I have the following PL/SQL block and it's working fine. I would like to call this function (TimeToFrame) but from another PL/SQL block.

I cannot declare this function in a procedure or package that is stored in the DB. In other words how can I call a pl/sql from another pl/sql where both pl/sql are anonymous blocks??

What if I put that function in a separate .sql file. Can't I call that .sql file from my anonymous block and pass it some IN parameters and have that fct return OUT params?

Declare
nTime Number;

FUNCTION TimeToFrame(pTime IN Varchar2)
return NUMBER IS
nTime NUMBER;
BEGIN
select (SUBSTR(pTime, 1, 2) * 108000)+(SUBSTR(pTime, 4, 2) * 1800)+(SUBSTR(pTime, 7, 2)     * 30)+SUBSTR(pTime, 10, 2)
 into nTime
 from dual;
 return nTime;
END TimeToFrame;

Begin
nTime:=TimeToFrame('47:59:59:29');
DBMS_OUTPUT.PUT_LINE(nTime);
End;

Reasons to use subprograms: reusability. Not allowing subprograms (named blocks) seems like an odd restriction. — Alex Poole
– Alex Poole, Commented Aug 7, 2014 at 12:21

Frank Schmitt · Accepted Answer · 2014-08-07 12:22:43Z

2

Unfortunately, there is no way to achieve that. A nested subprogram declared in an anonymous block isn't even persisted after the anonymous block has been processed, so even when we ignore the scope limits, it doesn't exist when the other anonymous block is processed.

If you can't make it a stored function because the evil DBA doesn't allow that, you will have to copy-paste the function.

edited Aug 7, 2014 at 12:22

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

answered Aug 7, 2014 at 12:14

Erich Kitzmueller

37.1k5 gold badges85 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eetawil Over a year ago

If I put that function in a separate .sql file. Can't I call that .sql file from my anonymous block and pass it some IN parameters and have that fct return OUT params?

Erich Kitzmueller Over a year ago

No. The anonymous block is executed in the database and most likely has no access to the file (and even if it had, there is no simple way to load and execute it).

neshkeev · Accepted Answer · 2014-08-07 12:13:04Z

1

The point is if you have a PL/SQL anonymous block, you cannot identify and call it, because it is anonymous. You have to have compile this stored procedure and then call it from both your PL/SQL blocks

answered Aug 7, 2014 at 12:13

neshkeev

6,4863 gold badges32 silver badges49 bronze badges

Comments

Alex Poole · Accepted Answer · 2014-08-07 15:10:14Z

Following on from your comment and question edit, if your anonymous blocks are being run as SQL*Plus scripts then you can do what you suggested and have the function in its own file. If you create a file called, say, TimeToFrame.sql with the function :

FUNCTION TimeToFrame(pTime IN Varchar2)
return NUMBER IS
  nTime NUMBER;
BEGIN
  select (SUBSTR(pTime, 1, 2) * 108000)+(SUBSTR(pTime, 4, 2) * 1800)
    +(SUBSTR(pTime, 7, 2)     * 30)+SUBSTR(pTime, 10, 2)
  into nTime
  from dual;
  return nTime;
END TimeToFrame;

Then from SQL*Plus or another script you can do:

Declare
  nTime Number;
  @TimeToFrame
Begin
  nTime:=TimeToFrame('47:59:59:29');
  DBMS_OUTPUT.PUT_LINE(nTime);
End;
/

5183999
PL/SQL procedure successfully completed.

Of course the file containing the function has to be somewhere SQL*Plus can see it. Also note that the contents of the file are incorporated into the anonymous block as the external file is included, so if you do list then you see the whole thing - not the original reference to @. The anonymous block is executed in the database as AmmoQ said; this is about creating the block within the client before it is sent to the database to be executed.

It would still be much better to create the function as a stored subprogram or in a package, of course.

Collectives™ on Stack Overflow

How to call a pl/sql anonymous block from an pl/sql anonymous block

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related