We need to modify hr_main.sql file to replace all occurrences of
__SUB__CWD__/human_resources/ to empty string. Otherwise, it throws errors saying
__SUB__CMD__ is not found.
I removed all REM statements from the script to keep it short.
Script hr_main.sql drops HR user if it is present and then creates the user, creates tables
and loads data into those tables. For this purpose,
it uses some other scripts in the same folder.
As we are referring to other scripts directly without any reference to folder,
you must be in the folder human_resources while you run hr_main.sql script.
Here is the modified version of the file - hr_main.sql.
We are referring to other scripts
directly as you can see in @hr_cre, @hr_popul etc.
SET ECHO OFF
SET VERIFY OFF
PROMPT
PROMPT specify password for HR as parameter 1:
DEFINE pass = &1
PROMPT
PROMPT specify default tablespace for HR as parameter 2:
DEFINE tbs = &2
PROMPT
PROMPT specify temporary tablespace for HR as parameter 3:
DEFINE ttbs = &3
PROMPT
PROMPT specify password for SYS as parameter 4:
DEFINE pass_sys = &4
PROMPT
PROMPT specify log path as parameter 5:
DEFINE log_path = &5
PROMPT
PROMPT specify connect string as parameter 6:
DEFINE connect_string = &6
PROMPT
-- The first dot in the spool command below is
-- the SQL*Plus concatenation character
DEFINE spool_file = &log_path.hr_main.log
SPOOL &spool_file
DROP USER hr CASCADE;
CREATE USER hr IDENTIFIED BY &pass;
ALTER USER hr DEFAULT TABLESPACE &tbs
QUOTA UNLIMITED ON &tbs;
ALTER USER hr TEMPORARY TABLESPACE &ttbs;
GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO hr;
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO hr;
CONNECT sys/&pass_sys@&connect_string AS SYSDBA;
GRANT execute ON sys.dbms_stats TO hr;
CONNECT hr/&pass@&connect_string
ALTER SESSION SET NLS_LANGUAGE=American;
ALTER SESSION SET NLS_TERRITORY=America;
--
-- create tables, sequences and constraint
--
@hr_cre
--
-- populate tables
--
@hr_popul
--
-- create indexes
--
@hr_idx
--
-- create procedural objects
--
@hr_code
--
-- add comments to tables and columns
--
@hr_comnt
--
-- gather schema statistics
--
@hr_analz
spool off
