Home » SQL & PL/SQL » SQL & PL/SQL » calling a DDL sql script from plsql block
calling a DDL sql script from plsql block [message #253215] Mon, 23 July 2007 02:51 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I want to execute a sql script from a PL/SQL block, is it possible? A sql script (say 2.sql) has some CREATE OR REPLACE commandsc

I want to create 1.sql which calls 2.sql based on some condition in a PL/SQL block in 1.sql

Something like

BEGIN

IF condition = TRUE THEN
	@2.sql
END IF;

END;
/


It works when a DDL statement is not included in 2.sql as the contents of 2.sql gets embedded in the calling pl/sql block, but is there a way to call DDL statement script.

Regards
Himanshu
Re: calling a DDL sql script from plsql block [message #253219 is a reply to message #253215] Mon, 23 July 2007 03:07 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Basically, you should avoid creating objects dynamically. I don't know what is in your 2.sql file, but - I'd suggest you to pre-create those objects and use them when appropriate.

If you need to call the "2.sql", well, put statements you have in there into a stored procedure and, from the PL/SQL block you use call a stored procedure (instead of a file). Something like
BEGIN

IF condition = TRUE THEN
   create_something;         -- it is a stored procedure which
                             -- contains contents of the 2.sql file
END IF;

END;
/
Re: calling a DDL sql script from plsql block [message #253220 is a reply to message #253215] Mon, 23 July 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
DDL in PL/SQL procedure should be execute through EXECUTE IMMEDIATE command.

Of course, calling such a script inside a PL/SQL block only works under special conditions.

Regards
Michel
Re: calling a DDL sql script from plsql block [message #253226 is a reply to message #253215] Mon, 23 July 2007 03:27 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Yes I am aware that DDL in PL/SQL are exceuted with EXECUTE IMMEDIATE but my question is can we call a sql file which contains DDL statments (such as create or replace procedure etc.)?
Re: calling a DDL sql script from plsql block [message #253236 is a reply to message #253226] Mon, 23 July 2007 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes... if you satisfy all the conditions.

Regards
Michel

Re: calling a DDL sql script from plsql block [message #253280 is a reply to message #253215] Mon, 23 July 2007 06:57 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Quote:
Yes... if you satisfy all the conditions.

Regards
Michel


And what are those conditions? and how??

Regards
Himanshu
Re: calling a DDL sql script from plsql block [message #253295 is a reply to message #253280] Mon, 23 July 2007 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
And what are those conditions?

Many. Tell us what you finally want to achieve.

Quote:
and how??

Like you did.

Regards
Michel
Re: calling a DDL sql script from plsql block [message #253310 is a reply to message #253215] Mon, 23 July 2007 08:00 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Sorry for not being clear earlier …. So here it goes

I have 2 different database instances and I want to create a common deployment script

So script 1.sql will have something like…

Declare 
Instance varchar2(100);
Begin

Select instance_name into instance from V$instance;

If instance = ‘OBUS’ then
   @2.sql
Else
   @3.sql
End if;
end;
/


Where 2.sql contains a definition

Create or replace procedure test
As
Begin
--
--
End;
/


3.sql

Create or replace procedure test2
As
Begin
--
--
End;
/


how to achieve this?

Re: calling a DDL sql script from plsql block [message #253311 is a reply to message #253310] Mon, 23 July 2007 08:03 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You said you know that a DDL must be enclose inside an EXECUTE IMMEDIATE but your script (2.sql and 3.sql) does not contain an execute immediate, just the statement.
Conclusion...

Regards
Michel
Previous Topic: to_date
Next Topic: datatype
Goto Forum:
  


Current Time: Tue Dec 03 11:27:04 CST 2024