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
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>- do I need to specify the default and temp tablespace ? If so, how do I find these ?

select tablespace_name from dba_tablespaces;

for a fresh install I would use USERS as the 'default'.  TEMP is the temp.

The script will prompt you for them (see the PROMPT command?).

>>In the sql command below, how is the "?" used ?

In sqlplus that is a shortcut to ORACLE_HOME.
0
Steve WalesSenior Database AdministratorCommented:
>>do I need to specify the default and temp tablespace ? If so, how do I find these ?

TEMP is usually the default tablespace created in an Oracle instance by default.  
You can query dba_tablespaces (select * from dba_tablespaces) to see what you have.

You should probably create a tablespace to store your demo data (Basic create tablespace statement below).  

create tablespace samples datafile 'd:\oracle\schemas\demo\samples01.dbf' size 500M;


>>In the sql command below, how is the "?" used ?

When used in that context in sqlplus it is replaced with the directory path for ORACLE_HOME

>>can this be run in Windows with the forward slashes (Unix) as opposed to backward slashes (Windows)

Yes, that should work.

>>In this sql command, the "&&[variable_name]" makes sense, but then it only has one "&" for "&vrs" (at the end), why is that ?

It has to do with the scope of variable substitution in SQLPLUS.

>>also, the parameter "%s_oePath% - how is that translated ?

Looking at how it's called, it's the 6th positional parameter.  Looking at the code:

PROMPT specify directory path for the data files as parameter 6:
DEFINE data_path = &6

I would think it will accept it and try to use that as an environment variable, but I'm not 100% sure on that.  It's a valid construct for windows e-var but you have talked about *nix elsewhere in the question

& will prompt you for the variable each time.
&& Will prompt you once and then use it each time it sees it, see here: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:818426831007

>>SQLPLus or SQL developer ?
For these kinds of provided scripts, I would personally tend to run them in SQLPLUS - the oracle provided scripts, for example, often make calls to other SQL scripts in the same (or other) directories and moving the code into SQL developer isn't going to find those scripts.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gadsden ConsultingIT SpecialistAuthor 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 . . . .
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gadsden ConsultingIT SpecialistAuthor 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 (..) ?
0
slightwv (䄆 Netminder) 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.
0
Gadsden ConsultingIT SpecialistAuthor 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.
0
Steve WalesSenior Database AdministratorCommented:
The second dot ?

Since what precedes the two dots is a variable, it is the syntax used to determine the end of the variable name.

When you run it, you will see the lot produced with only one dot.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
Steve Wales, ok, didn't know that about the dots, I'm used to Unix, I assume this is a DOS/Windows thing.
0
Gadsden ConsultingIT SpecialistAuthor 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.
0
slightwv (䄆 Netminder) 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.
0
Steve WalesSenior Database AdministratorCommented:
Correct on the double dots ... Sqlplus / variable substitution thing.
0
Gadsden ConsultingIT SpecialistAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.