Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Use "OUT" Parameters

By: FYIcenter.com

(Continued from previous topic...)

How To Use "OUT" Parameter Properly?

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

(Continued on next topic...)

  1. What Is a Procedure?
  2. What Is a Function?
  3. How To Define an Anonymous Procedure without Variables?
  4. How To Define an Anonymous Procedure with Variables?
  5. How To Create a Stored Procedure?
  6. How To Execute a Stored Procedure?
  7. How To Drop a Stored Procedure?
  8. How To Pass Parameters to Procedures?
  9. How To Create a Stored Function?
  10. How To Call a Stored Function?
  11. How To Drop a Stored Function?
  12. How To Call a Stored Function with Parameters?
  13. How To Define a Sub Procedure?
  14. How To Call a Sub Procedure?
  15. How To Define a Sub Function?
  16. Can Sub Procedure/Function Be Called Recursively?
  17. What Happens If Recursive Calls Get Out of Control?
  18. What Is the Order of Defining Local Variables and Sub Procedures/Functions?
  19. What Is the Difference between Formal Parameters and Actual Parameters?
  20. What Are the Parameter Modes Supported by PL/SQL?
  21. How To Use "IN" Parameter Properly?
  22. How To Use "OUT" Parameter Properly?
  23. How To Use "IN OUT" Parameter Properly?
  24. How To Define Default Values for Formal Parameters?
  25. What Are Named Parameters?
  26. What Is the Scope of a Local Variable?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...