SET ECHO         ON
SET SERVEROUTPUT ON

--
-- plsql06.txt
--

-- Simple PL/SQL demo

-- This script shows how to create a procedure with an input variable
-- which then gets passed to a WHERE clause in a cursor


-- Create a table for demonstration purposes

DROP   TABLE dropmePlsql06;
CREATE TABLE dropmePlsql06 AS
SELECT table_name,comments
FROM dictionary
WHERE table_name like '%TABLES';


CREATE OR REPLACE PROCEDURE plsql06 (aName IN VARCHAR2 DEFAULT 'ALL_TABLES') AS

   CURSOR dictCursor IS           
      SELECT table_name,comments
      FROM dropmePlsql06
      WHERE table_name = aName -- Here is where I feed input variable to WHERE clause
      FOR UPDATE OF table_name,comments;

BEGIN

  FOR aRecord IN dictCursor LOOP

     UPDATE dropmePlsql06 SET table_name  = 'Bikle',  comments = 'was here.'
     WHERE CURRENT OF dictCursor;

  END LOOP;

END;
/

-- It is a good idea to look for errors after you create a procedure
SHOW ERRORS

-- The 'EXEC' keyword is a useful shortcut
EXEC plsql06('USER_TABLES');

-- Check the result
SELECT * FROM dropmePlsql06;

-- The 'EXEC' keyword is a useful shortcut
EXEC plsql06()

-- Check the result
SELECT * FROM dropmePlsql06;

-- End of demo