Re: convert to stored procedure + NDS
Date: Tue, 06 Mar 2007 13:59:25 -0800
[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
>> (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';
execute immediate sqlstr;
-- 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 Tue Mar 06 2007 - 22:59:25 CET