Re: Question regarding batch, sqlplus and pl/sql

xylem wrote:
> 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

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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org