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_at_psoug.org>


[Quoted] 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
> 
> 
[Quoted] > Thanks for your reply, Daniel.
> 
[Quoted] > 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.

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

[Quoted] That said the answer to your question is very simply.

[Quoted] CREATE OR REPLACE PROCEDURE bad_practice IS

[Quoted] 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 - 22:59:25 CET

Original text of this message