Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

questions on running sample schema scripts for Oracle on Windows 7

Posted on 2014-08-01
12
Medium Priority
?
1,249 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
0
Comment
Question by:Gadsden Consulting
  • 6
  • 3
  • 3
12 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 40235269
>>- 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
 
LVL 23

Accepted Solution

by:
Steve Wales earned 800 total points
ID: 40235280
>>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
 

Author Comment

by:Gadsden Consulting
ID: 40235316
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Gadsden Consulting
ID: 40235393
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40235407
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
 

Author Comment

by:Gadsden Consulting
ID: 40235422
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
 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 800 total points
ID: 40235424
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
 

Author Comment

by:Gadsden Consulting
ID: 40235429
Steve Wales, ok, didn't know that about the dots, I'm used to Unix, I assume this is a DOS/Windows thing.
0
 

Author Comment

by:Gadsden Consulting
ID: 40235470
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40235490
>>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
 
LVL 23

Expert Comment

by:Steve Wales
ID: 40235502
Correct on the double dots ... Sqlplus / variable substitution thing.
0
 

Author Comment

by:Gadsden Consulting
ID: 40235539
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Assume you have an outside contractor who comes in seasonally or once a week to do some work in your office, but you only want to give him access to the programs and files he needs and keep all other documents and programs private. Can you do this o…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month12 days, 14 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question