Re: convert to stored procedure + NDS

From: <yadasiri_at_gmail.com>
Date: 6 Mar 2007 14:22:05 -0800
Message-ID: <1173219724.993389.314760_at_64g2000cwx.googlegroups.com>


[Quoted] 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

[Quoted] 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. Received on Tue Mar 06 2007 - 23:22:05 CET

Original text of this message