Re: convert to stored procedure + NDS
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Tue, 06 Mar 2007 15:32:02 -0800
yadasiri@xxxxxxxxx wrote:
On Mar 6, 4:59 pm, DA Morgan <damor...@xxxxxxxxx> wrote:yadas...@xxxxxxxxx wrote:On Mar 6, 2:05 pm, DA Morgan <damor...@xxxxxxxxx> wrote:My point was that building basic schema objects should be done with ayadas...@xxxxxxxxx wrote:Thanks for your reply, Daniel.Hi,Converting the above to a stored procedure, generically speaking, would
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.
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...@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org
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.
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...@xxxxxxxxxxxxxxxx
(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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.
- References:
- convert to stored procedure + NDS
- From: yadasiri
- Re: convert to stored procedure + NDS
- From: DA Morgan
- Re: convert to stored procedure + NDS
- From: yadasiri
- Re: convert to stored procedure + NDS
- From: DA Morgan
- Re: convert to stored procedure + NDS
- From: yadasiri
- convert to stored procedure + NDS
- Prev by Date: Re: convert to stored procedure + NDS
- Next by Date: Re: Help with my RMAN setup
- Previous by thread: Re: convert to stored procedure + NDS
- Next by thread: adjust_tz help?
- Index(es):
Relevant Pages
|
