Question regarding batch, sqlplus and pl/sql

#31

Comments embedded.
On Feb 7, 9:52*am, xylem <me_at_... (AT) yahoo (DOT) co.uk> wrote:

Quote:

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'.

Quote:

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.

Quote:

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'.

Quote:

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

#32

Comments embedded.
On Feb 7, 9:52*am, xylem <me_at_... (AT) yahoo (DOT) co.uk> wrote:

Quote:

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'.

Quote:

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.

Quote:

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'.

Quote:

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

#33

Comments embedded.
On Feb 7, 9:52*am, xylem <me_at_... (AT) yahoo (DOT) co.uk> wrote:

Quote:

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'.

Quote:

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.

Quote:

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'.

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Question regarding batch, sqlplus and pl/sql

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Question regarding batch, sqlplus and pl/sql - 02-08-2008 , 09:28 AM

Re: Question regarding batch, sqlplus and pl/sql - 02-08-2008 , 09:28 AM

Re: Question regarding batch, sqlplus and pl/sql - 02-08-2008 , 09:28 AM