We help IT Professionals succeed at work.

questions on running sample schema scripts for Oracle on Windows 7

1,412 Views
Last Modified: 2014-08-01
I want to run the sample schemas but have some questions. (Note: I have a good install of Oracle on Windows 7 [OraDB11g_home1, 11.2.0.1.0, but I didn't have all the scripts for the schema, I eventually found them here, so that's what I'm using. They look legit and it was the only place I could find them, despite some help here and on OTN Forums).

So I'm reviewing mksample.sql (attached), which should run everything else (hr_main and oe_main also attached). But some questions:

- the mksample.sql's last comment was 11 years ago . . . so that seems kind of ancient in computer terms . . .
- do I need to specify the default and temp tablespace ? If so, how do I find these ? They are submitted as a parameter for the main scripts (e.g., hr_main.sql)
PROMPT specify default tablespace as parameter 9:
DEFINE default_ts          = &9
PROMPT
PROMPT specify temporary tablespace as parameter 10:
DEFINE temp_ts             = &10

Open in new window


- for logfile directory, do I just need (something like) this: c:\oracle\schemas\demo\
- In the sql command below, how is the "?" used ?
- can this be run in Windows with the forward slashes (Unix) as opposed to backward slashes (Windows) ? If not, there's too many to change in the called scripts
@?/demo/schema/human_resources/hr_main.sql &&password_hr &&default_ts &&temp_ts &&password_sys &&logfile_dir

Open in new window


In this sql command, the "&&[variable_name]" makes sense, but then it only has one "&" for "&vrs" (at the end), why is that ?
- also, the parameter "%s_oePath% - how is that translated ?
PROMPT 
PROMPT specify password for SYSTEM as parameter 1:
DEFINE password_system     = &1
...
PROMPT
DEFINE vrs = v3
...
@?/demo/schema/order_entry/oe_main.sql &&password_oe &&default_ts &&temp_ts &&password_hr &&password_sys %s_oePath% &&logfile_dir &vrs

Open in new window


Finally, in Sql Developer, it doesn't look like a "command line" interface is available (as it is in Pl*Sql Developer), so then must I run this in Sql Plus (instead of Sql Developer) ?
mksample.sql
hr-main.sql
oe-main.sql
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Senior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gadsden ConsultingIT Specialist

Author

Commented:
slightwv,
>> select tablespace_name from dba_tablespaces; for a fresh install I would use USERS as the 'default'.  TEMP is the temp.
-- ok, got it.

>>In sqlplus [ "?" ] is a shortcut to ORACLE_HOME.
-- ok, that helps

-----
Steve Wales

-- ok on
  - tablespace info,
  - "?" = ORACLE_HOME,
  - forward slashes ok

>> >>it only has one "&" for "&vrs" (at the end), why is that ?
>> It has to do with the scope of variable substitution in SQLPLUS.
-- ok, sounds good

>>For these kinds of provided scripts, I would personally tend to run them in SQLPLUS
-- ok, makes sense

==========
so basically it sounds like I'm ok, I'm going to study this some more and then might let 'er rip . . . .
Gadsden ConsultingIT Specialist

Author

Commented:
I also see this in the mksample.sql, at the end:

DEFINE veri_spool = &&logfile_dir.mkverify_&vrs..log

Open in new window

do you think that's a typo --> "&vers..log", why are there two dots (..) ?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
If those scripts are the ones provided by Oracle, you should be able to just run them...

Granted, you will need to copy them to the correct folders because of the '?' and the main ones call others.
Gadsden ConsultingIT Specialist

Author

Commented:
slightwv - I ran this for hr_main.sql, and everything looks good :-)

However, it didn't spool the log file, which I hard-coded
PROMPT 
PROMPT specify log file directory (including trailing delimiter) as parameter 11:
DEFINE logfile_dir         = E:\app\Stephen\product\11.2.0\dbhome_1\demo\schema\

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/human_resources/hr_main.sql &&password_hr &&default_ts &&temp_ts &&password_sys &&logfile_dir

Open in new window


In the hr_main, it tried to spool thus:
DEFINE spool_file = &log_path.hr_main.log
SPOOL &spool_file

Open in new window


but the hr_script looked clean as a whistle, so I'm verifying everything now.
Steve WalesSenior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gadsden ConsultingIT Specialist

Author

Commented:
Steve Wales, ok, didn't know that about the dots, I'm used to Unix, I assume this is a DOS/Windows thing.
Gadsden ConsultingIT Specialist

Author

Commented:
Ok, I got "order_entry" set up properly, there were a couple of errors, but my spooling is not working. I'll try one more and see about the spooling, but so far so good, other than the spooling not working.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>which I hard-coded

Why did you alter the scripts at all?  They are meant to run as-is.

>> I assume this is a DOS/Windows thing.

I think it is a sqlplus thing.
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
Correct on the double dots ... Sqlplus / variable substitution thing.
Gadsden ConsultingIT Specialist

Author

Commented:
slightwv, I hard-coded the log path because it didn't get created first time through. I got that fixed (it was a permissions issue on my "E:\" drive).

So everything went mostly smoothly, but sql*loader didn't work at all. Now that I have the spooling of the log file figured out, I may just re-do the whole thing, although I need to figure out sql*loader, I'll post that separately.

So for the purposes of this question, I'm good, thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.