Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!in03.usenetserver.com!news.usenetserver.com!news.glorb.com!postnews.google.com!i12g2000prf.googlegroups.com!not-for-mail From: "fitzjarrell@cox.net" Newsgroups: comp.databases.oracle.misc Subject: Re: Question regarding batch, sqlplus and pl/sql Date: Fri, 8 Feb 2008 07:28:21 -0800 (PST) Organization: http://groups.google.com Lines: 105 Message-ID: References: NNTP-Posting-Host: 138.32.32.166 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1202484501 11405 127.0.0.1 (8 Feb 2008 15:28:21 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 8 Feb 2008 15:28:21 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: i12g2000prf.googlegroups.com; posting-host=138.32.32.166; posting-account=kBJLegoAAACGAHMa2jhKq7psWYVxkNwe User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; Embedded Web Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.misc:251771 X-Received-Date: Fri, 08 Feb 2008 10:28:21 EST (text.usenetserver.com) Comments embedded. On Feb 7, 9:52=A0am, 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. 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 =3D &1 =A0-- Name (wildcard) > > @VPD_tabell_View_p &table > > @spool_start > spool VPD_add_policies1.sql > > select '@VPD_add_policy '||TABLE_NAME > =A0 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=3DDatamart) > -------------------------------------- > SET instance=3D%1 > SET mart=3D%2 > > SET password=3Dmart > > 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=3D%1 > SET mart=3D%2 > > SET password=3Dmart > > 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