/*
* CreateUser.sql
* Chapter 3, Oracle10g PL/SQL Programming
* by Ron Hardman, Mike McLaughlin, and Scott Urman
*
* This script creates the plsql user for chapter 3
* examples. You must run this script as SYS or
* SYSTEM as SYSDBA. The script can be rerun.
*
* The plsql user is created using the USERS and TEMP tablespace.
* If these tablespaces do not exist in your environment, change
* the script to use an appropriate tablespace.
*
* Modify the conn_string value below, providing your Net Service
* Name if not using your default.
*/
DEF conn_string = plsql/oracle
DEF username = plsql
DEF default_ts = USERS
DEF temp_ts = TEMP
SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
SPOOL CreateUser.log
DECLARE
v_count INTEGER := 0;
v_statement VARCHAR2 (200);
BEGIN
SELECT COUNT (1)
INTO v_count
FROM dba_users
WHERE username = UPPER ('&username');
IF v_count != 0
THEN
EXECUTE IMMEDIATE ('DROP USER &username CASCADE');
END IF;
v_statement :=
'CREATE USER &username IDENTIFIED BY oracle'
|| ' DEFAULT TABLESPACE &default_ts'
|| ' TEMPORARY TABLESPACE &temp_ts'
|| ' QUOTA UNLIMITED ON &default_ts'
|| ' ACCOUNT UNLOCK';
EXECUTE IMMEDIATE (v_statement);
-- Grant permissions
EXECUTE IMMEDIATE ('GRANT create session TO &username');
EXECUTE IMMEDIATE ('GRANT connect TO &username');
EXECUTE IMMEDIATE ('GRANT create procedure TO &username');
EXECUTE IMMEDIATE ('GRANT execute ON utl_ref TO &username');
EXECUTE IMMEDIATE ('GRANT create trigger TO &username');
EXECUTE IMMEDIATE ('GRANT resource TO &username');
DBMS_OUTPUT.put_line (' ');
DBMS_OUTPUT.put_line ('User &username created successfully');
DBMS_OUTPUT.put_line (' ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
DBMS_OUTPUT.put_line (' ');
END;
/
SET FEEDBACK ON TERMOUT ON
PROMPT
PROMPT ******** Connecting to user &username!! ********
PROMPT
CONN &conn_string
CREATE OR REPLACE PACKAGE clean_schema IS
PROCEDURE trigs;
PROCEDURE tables;
PROCEDURE procs;
END;
/
CREATE OR REPLACE PACKAGE BODY clean_schema AS
PROCEDURE trigs
IS
v_string VARCHAR2(50);
v_tab_error EXCEPTION;
PRAGMA EXCEPTION_INIT(v_tab_error, -4080);
BEGIN
BEGIN
v_string := 'DROP TRIGGER author_trig';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
END trigs;
PROCEDURE tables
IS
v_string VARCHAR2(50);
v_tab_error EXCEPTION;
PRAGMA EXCEPTION_INIT(v_tab_error, -942);
BEGIN
BEGIN
v_string := 'DROP TABLE precision';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
BEGIN
v_string := 'DROP TABLE inventory';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
BEGIN
v_string := 'DROP TABLE books';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
BEGIN
v_string := 'DROP TABLE authors';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
END TABLES;
PROCEDURE procs
IS
v_string VARCHAR2(50);
v_proc_error EXCEPTION;
PRAGMA EXCEPTION_INIT(v_proc_error, -4043);
BEGIN
BEGIN
v_string := 'DROP PROCEDURE compile_error';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := 'DROP PROCEDURE compile_warning';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := 'DROP PROCEDURE named_block';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := 'DROP PROCEDURE book_ins';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := 'DROP PROCEDURE authors_sel';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := 'DROP PROCEDURE bind_test';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := 'DROP PROCEDURE author_book_count';
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
END procs;
END;
/
spool off