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