Running multiple CREATE..DROP statements in EST

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Running multiple CREATE..DROP statements in EST

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-09 : 12:50:45

I have a PL-SQL script which contains multiple oracle statements e.g.

DROP TABLE XYZ PURGE;

DROP TABLE PQR PURGE;

CREATE TABLE XYZ AS SELECT * FROM ABC;

CREATE TABLE PQR AS SELECT * FROM LMN;

So, the question is how do I run these statments in one EST instead of creating one EST for one statement..

I tried BEGIN..END..but it is giving me an error:

[Execute SQL Task]

Error: Executing the query "BEGIN

drop table ucsf_find_members purge;

with the following error: "ORA-06550: line 1, column 7:

PLS-00103: Encountered the symbol "

DROP" when expecting one of the following:

begin case declare exit for goto if loop mod null pragma

raise return select update while with <an identifier>

<a double-quoted delimited-identifier> <a bind variable> <<

close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe"

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

drop tab..." failed

If I dont use BEGIN..END and just keep semicolons after every statement, then I am getting following error:

[Execute SQL Task] Error: Executing the query "drop table ucsf_find_members purge;
drop table ucs..." failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thank you!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 13:12:59

we are on ms sql server here

you can try to post your question in dbforums.com/oracle for example

No, you're never too old to Yak'n'Roll if you're too young to die.

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-09 : 13:17:04

Yes..but the main quesion is related to SSIS..its not about how to write oracle code..its related to how to use Oracle code inside SSIS..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 13:19:06

oh sorry then I hope someone here has the answer...

No, you're never too old to Yak'n'Roll if you're too young to die.

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-09 : 13:21:03

Hoping for the best..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 13:49:33

Microsoft client software who is sending multiple statements to the server just uses GO to seperate the statements.

So please try this (without semikolon or whatever):
DROP TABLE XYZ PURGE
GO
DROP TABLE PQR PURGE
GO
CREATE TABLE XYZ AS SELECT * FROM ABC
GO
CREATE TABLE PQR AS SELECT * FROM LMN

No, you're never too old to Yak'n'Roll if you're too young to die.

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-09 : 14:13:49

One of the answers I got is as follows:

begin

execute immediate 'drop table ABC';

execute immediate 'drop table XYZ';

end;

The above statement is working..

Now the problem is my CREATE TABLE STATEMENT contains

or t1.proper_name_ls like 'Unassig%')

How do I write that in dynamic sql :(

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-02-09 : 14:17:40

Got the other problem solved as well..

Just use two single quotes instead of a single quote..

or t1.proper_name_ls like ''Unassig%'')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources