Use "OUT" Parameters in Oracle

Q

How To Use "OUT" Parameter Properly in Oracle?

✍: FYIcenter.com

A

Here are the rules about OUT parameters:

  • A formal OUT parameter acts like an un-initialized variable. It must be assigned with new values before the end of the procedure or function.
  • An actual OUT parameter must be a variable.
  • An actual OUT parameter will not pass any value to the formal parameter.
  • An actual OUT parameter will receive a copy of the value from the formal parameter at the end of the procedure or function.

Here is good example of a procedure with an OUT parameter:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS
  2    SITE CHAR(40) := 'FYICenter.com';
  3    MESSAGE CHAR(80);
  4    PROCEDURE WELCOME_PRINT(S IN CHAR, M OUT CHAR) AS
  5    BEGIN
  6      M := 'Welcome to ' || S;
  7    END;
  8  BEGIN
  9    WELCOME_PRINT('MySpace.com', MESSAGE);
 10    DBMS_OUTPUT.PUT_LINE(MESSAGE);
 11    WELCOME_PRINT(SITE, MESSAGE);
 12    DBMS_OUTPUT.PUT_LINE(MESSAGE);
 13  END;
 14  /

SQL> EXECUTE WELCOME;
Welcome to MySpace.com                  
Welcome to FYICenter.com

 

Creating Oracle PL/SQL Procedures and Functions

⇒⇒Oracle Database Tutorials

2016-10-17, 238👍, 0💬