INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to create a database object using SQLPlus from the operating system command line

How to create a database object using SQLPlus from the operating system command line

How to create a database object using SQLPlus from the operating system command line

(OP)
New question please. I'd like to run a script that creates a database object from the command line. Simple.

Now this is from the operating system command line (which happens to be Windows), not SQL Developer, not TOAD, not PL/SQL.

And before you ask yup I've done the usual (google, resident database guru, books, forums).

Under SQL Server it's something like (and no I don't remember the exact syntax but its close and no I'm an old dude so I haven't learned the more recent sqlcmd syntax):

osql -b -S <server\instance> -d <database> -E < myscript.sql

I've tried something similar using SQLPlus...

sqlplus <my schema>/<my password>@<database> @C:\Projects\Database\Reporting\vw_ash_analysis_analytes.sql

This is the result...(note that the '<' and '>' chars in the call below are delimiters, I didn't enter them!)

I'm thinking that maybe I've missed a termination character at the end of the script? Not sure.

It runs under PL/SQL so its valid SQL, just doesn't run under SQLPlus.

C:\Users\my.name>sqlplus <my.name>/<my_password>@<the_database> @C:\Projects\Database\Reporting\vw_ash_analysis_analytes.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 22 09:04:12 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

SP2-0734: unknown command beginning "Descriptio..." - rest of line ignored.
SP2-0734: unknown command beginning "Returns: A..." - rest of line ignored.
SP2-0734: unknown command beginning "analyte_co..." - rest of line ignored.
SP2-0734: unknown command beginning "is part of..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "Example us..." - rest of line ignored.
SP2-0734: unknown command beginning "Author: Ro..." - rest of line ignored.
SP2-0734: unknown command beginning "Created: 2..." - rest of line ignored.
SP2-0734: unknown command beginning "History: 2..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0042: unknown command "*/" - rest of line ignored.

ANALY
-----
SiO2
Al2O3
Fe2O3
CaO
MgO
Na2O
K2O
TiO2
Mn3O4
P2O5
SO3

ANALY
-----
BaO
SrO

This is the script def:

create or replace force view REPORTING.vw_ash_analysis_analytes
(
analyte_code
) as
/*
-----------------------------------------------------------------------------------------------------------------------------------------
View name: REPORTING.vw_ash_analysis_analytes

Description: Returns a result set containing those analytes used for ash analysis

Returns: A result set containing the analytes used for ash analysis
analyte_code - code of the ash analysis analyte, when joined from a sample it indicates that the sample
is part of an ash analysis

Example usage: select * from REPORTING.vw_ash_analysis_analytes

Author: me

Created: 20 May 2013

History: 20.MAY.2013 my initials v1.00 Initial
------------------------------------------------------------------------------------------------------------------------------------------
*/
select 'SiO2' as analyte_code from dual union all
select 'Al2O3' from dual union all
select 'Fe2O3' from dual union all
select 'CaO' from dual union all
select 'MgO' from dual union all
select 'Na2O' from dual union all
select 'K2O' from dual union all
select 'TiO2' from dual union all
select 'Mn3O4' from dual union all
select 'P2O5' from dual union all
select 'SO3' from dual union all
select 'BaO' from dual union all
select 'SrO' from dual union all
select 'ZnO' from dual;

GRANT SELECT ON REPORTING.vw_ash_analysis_analytes TO <db account>;

RE: How to create a database object using SQLPlus from the operating system command line

SQLPlus is a little....finicky.

Your script has largely worked. You have the basices of it down. You just need to deal with SQLPlus', er...oddities. One thing that SQLPlus really hates is blank lines. If you ran a create table script like this:

CODE

create table owner.tablename

(col1 number,
 col2 varchar2(20)); 
SQLPlus will error out two ways. The create table is not "finished", and SQLPlus can not figure out what command starts with "(col1". I know. You would think that Oracle would have some way of dealing with whitespace, but it just has not happened, yet.

So, while it looks messy as hell, try deleting the blank lines, and let's see what you get.

RE: How to create a database object using SQLPlus from the operating system command line

(OP)
Q. Who da man ?
A. YOU da man.

Spot on. My script now looks like this and I keep to keep my beloved whitespace...

This is great as I use makefiles to deploy my scripts (locally) and if I couldn't get it to work then I'd be in deep doodoo.

echo exit | sqlplus <my.name>/<my_password>@<the_database> @C:\Projects\Database\Reporting\vw_ash_analysis_analytes.sql

set sqlblanklines on;
create or replace force view REPORTING.vw_ash_analysis_analytes
(
analyte_code
) as
/*
-----------------------------------------------------------------------------------------------------------------------------------------
View name: REPORTING.vw_ash_analysis_analytes

Description: Returns a result set containing those analytes used for ash analysis

Returns: A result set containing the analytes used for ash analysis
analyte_code - code of the ash analysis analyte, when joined from a sample it indicates that the sample
is part of an ash analysis

Example usage: select * from REPORTING.vw_ash_analysis_analytes

Author: me

Created: 20 May 2013

History: 20.MAY.2013 my initials v1.00 Initial
------------------------------------------------------------------------------------------------------------------------------------------
*/
select 'SiO2' as analyte_code from dual union all
select 'Al2O3' from dual union all
select 'Fe2O3' from dual union all
select 'CaO' from dual union all
select 'MgO' from dual union all
select 'Na2O' from dual union all
select 'K2O' from dual union all
select 'TiO2' from dual union all
select 'Mn3O4' from dual union all
select 'P2O5' from dual union all
select 'SO3' from dual union all
select 'BaO' from dual union all
select 'SrO' from dual union all
select 'ZnO' from dual;

GRANT SELECT ON REPORTING.vw_ash_analysis_analytes TO <db account>;

RE: How to create a database object using SQLPlus from the operating system command line

Q. Who da man ?
A. YOU da man.

cantor001, the proper phrasing is "You da man, I da fan!"

====================================
Sometimes the grass is greener on the other side because there is more manure there - original.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close