Re: Question regarding batch, sqlplus and pl/sql

On Feb 8, 10:20 am, xylem <me_at_..._at_yahoo.co.uk> wrote:
> On Feb 8, 12:13 am, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
>
>
> > xylem wrote:
> > >> You don't need SQL*Plus commands you need to solve a business problem.
>
> > > 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
>
> > >> Daniel A. Morgan
> > >> Oracle Ace Director & Instructor
> > >> University of Washington
> > >> damor..._at_x.washington.edu (replace x with u to respond)
> > >> Puget Sound Oracle Users Groupwww.psoug.org-Hidequoted text -
>
> > >> - Show quoted text -
>
> > If you need to supply input you have three choices:
>
> > 1. Hard code in the values
> > 2. Run using a stored procedure and supply input parameters
> > 3. Build a front-end
> > --
> > Daniel A. Morgan
> > Oracle Ace Director & Instructor
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> > - Show quoted text -
>
> Thanks Daniel.
>
> Input values will be supplied and are already hard coded. See, for
> example, the content of the DM_Up.bat file below. My problem is how to
> pack this into a pl/sql block (I'm still learning pl/sql). Otherwise
> the batch file is working fine.
>
> --------------------------------------
> 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%
>
> Thanks
> --
> Mark- Hide quoted text -
>
> - Show quoted text -

The answer is quite simple.
You CAN'T 'Package' a sqlplus script in PL/SQL, you need to CONVERT it to PL/SQL
Apart from that there is NO GAIN in running DDL using Pl/sql. Pl/sql is NOT designed to run DDL!

--
Sybrand Bakker
Senior Oracle DBA