CODEWITHSUNDEEP
oracle-databaseplsql
sunleo
sunleo

Reputation: 10943

Temporary and Permanent Stored Procedure

I am creating Stored Procedure in Oracle and one of them is created permanently and another one is temporarily created and vanished after serving its purpose.How it is working please give your guidance when to use and how it is created.

---- This is not created in DB, just temporarily created and vanished

DECLARE   name varchar2(10);
PROCEDURE printVal (name varchar2) IS
BEGIN
   dbms_output.put_line ('name:' || name);
END;  
BEGIN
  name := 'Joe';
  printVal(name);
END;
/

---- This is created in DB and permanently available

create PROCEDURE printVal (name varchar2) IS
BEGIN
   dbms_output.put_line ('name:' || name);
END;

Upvotes: 2

Views: 2289

Answers (2)

Tajinder
Tajinder

Reputation: 2338

To understand, Dividing your sql in two parts.

Stored Procedure:

  • Stored procedures are stored in database.
  • We can call stored procedures any time after creation.
  • Stored procedures also supports input output parameters.

Anonymous Block:

  • These are unnamed pl/sql blocks.
  • Anonymous blocks are not stored in database.
  • Cannot pass paramters
---------- Stored Procedure Start--------

DECLARE   name varchar2(10);
PROCEDURE printVal (name varchar2) IS
BEGIN
   dbms_output.put_line ('name:' || name);
END;  
--------- Stored Procedure End-----------

----------anonymous block Start----------
BEGIN
  name := 'Joe';
  printVal(name);
END;
/
----------anonymous block end ------------

Upvotes: 4

David Aldridge
David Aldridge

Reputation: 52356

Well, clearly there is different syntax -- the first one is an anonymous block, and the second creates a stored procedure. The expected behaviour is exactly what you observe, and covered by Oracle PL/SQL documentation. https://docs.oracle.com/cloud/latest/db112/LNPLS/overview.htm#LNPLS141

Upvotes: 3

Related Questions