Path: news.f.de.plusline.net!news-fra1.dfn.de!npeer.de.kpn-eurorings.net!ecngs!feeder.ecngs.de!nx01.iad01.newshosting.com!newshosting.com!post01.iad01!not-for-mail
Message-ID: <45EDE43D.9030905@psoug.org>
Date: Tue, 06 Mar 2007 13:59:25 -0800
From: DA Morgan <damorgan@psoug.org>
Organization: Puget Sound Oracle Users Group
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.tools
To:  yadasiri@gmail.com
Subject: Re: convert to stored procedure + NDS
References: <1173207667.008340.115030@64g2000cwx.googlegroups.com>   <1173207953.214487@bubbleator.drizzle.com> <1173208513.468832.188220@h3g2000cwc.googlegroups.com>
In-Reply-To: <1173208513.468832.188220@h3g2000cwc.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Cache-Post-Path: bubbleator.drizzle.com!unknown@oracle.advtechserv.com
X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/)
Lines: 60
X-Complaints-To: abuse@csolutions.net
Xref: news.f.de.plusline.net comp.databases.oracle.tools:35566

yadasiri@gmail.com wrote:
> On Mar 6, 2:05 pm, DA Morgan <damor...@psoug.org> wrote:
>> yadas...@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...@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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org