Re: convert to stored procedure + NDS

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 06 Mar 2007 15:32:02 -0800
Message-ID: <1173223920.237952@bubbleator.drizzle.com>

yadasiri_at_gmail.com wrote:
> 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

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

No.

But truly the only thing required is to turn them into an anonymous block.

BEGIN
   EXECUTE IMMEDIATE 'stuff here';
END;
/

A procedure ws only demonstrated because you asked for one.

Seriously consider revising your methodology to FTP a script to the server and then executing it using SQL*Plus.

-- 
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 - 17:32:02 CST