Re: convert to stored procedure + NDS

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 06 Mar 2007 13:59:25 -0800
Message-ID: <45EDE43D.9030905@psoug.org>

yadasiri_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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Mar 06 2007 - 15:59:25 CST