Hi all,

I have two questions regarding PL/SQL, batch jobs, sqlplus and VPD and
I'd be glad if someone could help me clarify. NB! this is not a
college project.

1. I've read that one cannot include sqlplus commands
inside a pl/sql block. I have a task to package a
number of sql scripts in a pl/sql package. Some of
these sql scripts have a number of sqlplus commands;
for exambple:

--------------------------------------
VPD_add_policies_p.sql
--------------------------------------

define table = &1 -- Name (wildcard)

@VPD_tabell_View_p &table

@spool_start
spool VPD_add_policies1.sql

select '@VPD_add_policy '||TABLE_NAME
from VPD_TABELL_V
/

@spool_stop
@VPD_add_policies1
--------------------------------------

How does one incooperate such into a pl/sql package?

2. A developer had previously created batch jobs to
manage the data warehouse, whether it was down or up
and running. This required that if the warehouse was
down, the batch file in question needed to be run to
start the affected programme. How does one convert
this into a package so that whenever the data
warehouse was down, the procedure/function would
automatically start/run the failed data warehouse??
This is how it's currently done in batch:

--------------------------------------
DM_Up.bat (DM=Datamart)
--------------------------------------
SET instance=%1
SET mart=%2

SET password=mart

REM Start VPD
sqlplus DDD/%password%@%instance% @VPD_add_policies_x
%mart%

REM Re-generete Indexes if the loading actual
indexes failed.
sqlplus DDD/%password%@%instance% @create_IX_DM_x %mart%

REM Gives privilages to access users
sqlplus DDD/%password%@%instance% @Grant_Revoke_DM_p_x G
T * %mart%

--------------------------------------
DM_Down.bat
--------------------------------------
SET instance=%1
SET mart=%2

SET password=mart

sqlplus DDD/%password%@%instance% @Grant_Revoke_DM_p_x R
T * %mart%

Thanks in advance for the help,
Mark

xylem wrote:
You don't need SQL*Plus commands you need to solve a business problem.

If the business problem requires accepting input from an end-user
where is that end user? Standing in the server room or sitting in a
cubicle at another location?

Also, is there an Oracle version number associated with this request?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

xylem wrote:
You don't need SQL*Plus commands you need to solve a business problem.

If the business problem requires accepting input from an end-user
where is that end user? Standing in the server room or sitting in a
cubicle at another location?

Also, is there an Oracle version number associated with this request?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

xylem wrote:
You don't need SQL*Plus commands you need to solve a business problem.

If the business problem requires accepting input from an end-user
where is that end user? Standing in the server room or sitting in a
cubicle at another location?

Also, is there an Oracle version number associated with this request?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

xylem wrote:
You don't need SQL*Plus commands you need to solve a business problem.

If the business problem requires accepting input from an end-user
where is that end user? Standing in the server room or sitting in a
cubicle at another location?

Also, is there an Oracle version number associated with this request?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thanks Daniel. The Oracle version is 10.02g.

Mark

Thanks Daniel. The Oracle version is 10.02g.

Mark

Thanks Daniel. The Oracle version is 10.02g.

Mark

Thanks Daniel. The Oracle version is 10.02g.

Mark

I specified this in my request about and was therefore asking for
help. The batch jobs and sqlplus commands in sql scripts were writen
by another developer.

Mark