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

[cantor001]

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

 

[yelworcm]

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:

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.

 

[cantor001]

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

 

[johnherman]

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.