Re: convert to stored procedure + NDS
From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 06 Mar 2007 15:32:02 -0800
Message-ID: <1173223920.237952_at_bubbleator.drizzle.com>
>
>
> Thanks again for your reply, Daniel.
>
> The business case is we are maintaining a database which we are
> mirroring from another database. Occasionally, we are required to do a
> clean wipe and a recreation of all database objects and re-mirror all
> the info. Moreover, the systems are different and unfortunately, we
> depend on the ODBC to handle the process between the systems.
>
> My question actually was: is there a clever way (Oracle development
> tools, etc) or existing script (awk,sed, etc) to convert the sql
> statements into stored procedures so I don't have to manually do so.
> There is quite a large number of statements in each sql script and
> there are altogether many scripts to convert. Thank you very much for
> your help.
Date: Tue, 06 Mar 2007 15:32:02 -0800
Message-ID: <1173223920.237952_at_bubbleator.drizzle.com>
yadasiri_at_gmail.com wrote:
> On Mar 6, 4:59 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> yadas..._at_gmail.com wrote: >>> On Mar 6, 2:05 pm, DA Morgan <damor..._at_psoug.org> wrote: >>>> yadas..._at_gmail.com wrote: >>>>> Hi, >>>>> I have a multiple of large .sql scripts that I would like to convert >>>>> into stored procedures. Is there a clever way or an existing script I >>>>> can use to find/replace multiple DDL statements in each sql script >>>>> like: >>>>> CREATE SEQUENCE BILL_SEQ >>>>> START WITH 0 >>>>> INCREMENT BY 1 >>>>> NOCACHE >>>>> NOCYCLE >>>>> / >>>>> with >>>>> EXECUTE IMMEDIATE 'CREATE SEQUENCE BILL_SEQ '|| >>>>> ' START WITH 0' >>>>> ' INCREMENT BY 1 '|| >>>>> ' NOCACHE '|| >>>>> ' NOCYCLE'; >>>>> Thank you very much. >>>> Converting the above to a stored procedure, generically speaking, would >>>> be a horrible idea. In fact doing it with NDS is a horrible idea too. >>>> What is the business case? >>>> -- >>>> Daniel A. Morgan >>>> University of Washington >>>> damor..._at_x.washington.edu >>>> (replace x with u to respond) >>>> Puget Sound Oracle Users Groupwww.psoug.org >>> Thanks for your reply, Daniel. >>> I have to run the scripts through ODBC so it'll be helpful if I can >>> get it into the form of a stored procedure. >> My point was that building basic schema objects should be done with a >> script run from the operating system. Doing this in PL/SQL, doing this >> through ODBC, again I would want to know what business case demands >> this as it goes against most of what is considered "best practice." >> >> That said the answer to your question is very simply. >> >> CREATE OR REPLACE PROCEDURE bad_practice IS >> >> sqlstr VARCHAR2(200) := 'CREATE SEQUENCE BILL_SEQ START WITH 0 INCREMENT >> BY 1 NOCACHE NOCYCLE'; >> >> BEGIN >> execute immediate sqlstr; >> END bad_practice; >> / >> -- >> Daniel A. Morgan >> University of Washington >> damor..._at_x.washington.edu >> (replace x with u to respond) >> Puget Sound Oracle Users Groupwww.psoug.org
>
>
> Thanks again for your reply, Daniel.
>
> The business case is we are maintaining a database which we are
> mirroring from another database. Occasionally, we are required to do a
> clean wipe and a recreation of all database objects and re-mirror all
> the info. Moreover, the systems are different and unfortunately, we
> depend on the ODBC to handle the process between the systems.
>
> My question actually was: is there a clever way (Oracle development
> tools, etc) or existing script (awk,sed, etc) to convert the sql
> statements into stored procedures so I don't have to manually do so.
> There is quite a large number of statements in each sql script and
> there are altogether many scripts to convert. Thank you very much for
> your help.
No.
But truly the only thing required is to turn them into an anonymous block.
BEGIN
EXECUTE IMMEDIATE 'stuff here';
END;
/
A procedure ws only demonstrated because you asked for one.
Seriously consider revising your methodology to FTP a script to the server and then executing it using SQL*Plus.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Wed Mar 07 2007 - 00:32:02 CET