asked on

questions on running sample schema scripts for Oracle on Windows 7

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

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Steve Wales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

William Peck

ASKER

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 . . . .

William Peck

ASKER

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 (..) ?

slightwv (䄆 Netminder)

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.

William Peck

ASKER

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.

SOLUTION

Steve Wales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

William Peck

ASKER

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

William Peck

ASKER

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.

slightwv (䄆 Netminder)

>>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 Wales

Correct on the double dots ... Sqlplus / variable substitution thing.

William Peck

ASKER

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.