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.

yadasiri (AT) gmail (DOT) com wrote:
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
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Mar 6, 2:05 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

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.

yadasiri (AT) gmail (DOT) com wrote:
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

On Mar 6, 4:59 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

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.

yadasiri (AT) gmail (DOT) com wrote:
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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org