Question regarding batch, sqlplus and pl/sql

From: xylem <me_at_icq_at_yahoo.co.uk>
Date: Thu, 7 Feb 2008 07:52:25 -0800 (PST)
Message-ID: <d0a72576-8b55-499c-990a-24d4ebad8b42@m62g2000hsb.googlegroups.com>

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 Received on Thu Feb 07 2008 - 09:52:25 CST