Re: Question regarding batch, sqlplus and pl/sql

Comments embedded.
On Feb 7, 9:52 am, xylem <me_at_..._at_yahoo.co.uk> 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.

Who gave you this task? Has this person any knowledge of PL/SQL? I'm expecting the answer to the second question is 'No'.

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

Normally one doesn't.

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

Again, normally one doesn't. And you need to be much more clear on what you mean by 'whenever the data warehouse was down'? According to your DM_down.bat all that's being done is executing a revoke script, thus the data warehouse isn't really 'down'.

> 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

Your description of the situation is lacking much in necessary detail. No Oracle version (numbers, please, at least 4 of them), no operating system (although I expect it's Windows of some ilk). If you want anyone to provide any useful input you'll need to provide at least those two pieces of information. Absent that your question is akin to 'If a tree falls in the forest and no one is there to hear it, does it make a noise?' Put a person in that forest and possibly some useful advice will appear (other than the already useful advice offered you).

David Fitzjarrell Received on Fri Feb 08 2008 - 09:28:21 CST