By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,159 Members | 1,941 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,159 IT Pros & Developers. It's quick & easy.

SQL Batch Script from MSSQL to Oracle 8i

P: n/a
We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.

What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints, so no editorials please.

Thanks in advance. Jeff.

-- SQL batch script

SET NOCOUNT ON
SET XACT_ABORT ON --this is especially important
BEGIN TRANSACTION INDX_ADD_ADMIN

/* many stored procedure calls in a loop */
/* ie Str := 'BEGIN INDX_DELSRC ('''+DOS_NAME+'''); END;'; */

COMMIT TRANSACTION INDX_ADD_ADMIN
SET XACT_ABORT OFF
SET NOCOUNT OFF
Jul 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Jeff Lambert wrote:
We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.

What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints, so no editorials please.

Thanks in advance. Jeff.

-- SQL batch script

SET NOCOUNT ON
SET XACT_ABORT ON --this is especially important
BEGIN TRANSACTION INDX_ADD_ADMIN

/* many stored procedure calls in a loop */
/* ie Str := 'BEGIN INDX_DELSRC ('''+DOS_NAME+'''); END;'; */

COMMIT TRANSACTION INDX_ADD_ADMIN
SET XACT_ABORT OFF
SET NOCOUNT OFF


Now, if you would at least supply what the SS2K statements do.
I can only guess: set transaction_abort off? As in "I don't care
that my transaction has errors, just go on"?
Not possible.
Begin tran/end tran: should probably become a stored procedure
with pragma autonomous_transaction
set nocount off (or on): don't bother - oracle will always be
able to tell you how many rows were affected - you normally
don't ask (in PL/SQL)
--
Regards, Frank van Bortel

Jul 19 '05 #2

P: n/a
Jeff Lambert wrote:
We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.

What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints, so no editorials please.

Thanks in advance. Jeff.

-- SQL batch script

SET NOCOUNT ON
SET XACT_ABORT ON --this is especially important
BEGIN TRANSACTION INDX_ADD_ADMIN

/* many stored procedure calls in a loop */
/* ie Str := 'BEGIN INDX_DELSRC ('''+DOS_NAME+'''); END;'; */

COMMIT TRANSACTION INDX_ADD_ADMIN
SET XACT_ABORT OFF
SET NOCOUNT OFF


Now, if you would at least supply what the SS2K statements do.
I can only guess: set transaction_abort off? As in "I don't care
that my transaction has errors, just go on"?
Not possible.
Begin tran/end tran: should probably become a stored procedure
with pragma autonomous_transaction
set nocount off (or on): don't bother - oracle will always be
able to tell you how many rows were affected - you normally
don't ask (in PL/SQL)
--
Regards, Frank van Bortel

Jul 19 '05 #3

P: n/a
Frank <fb*****@nescape.net> wrote in message news:<br**********@news1.tilbu1.nb.home.nl>...
Jeff Lambert wrote:
We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.

What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints, so no editorials please.

Thanks in advance. Jeff.

-- SQL batch script

SET NOCOUNT ON
SET XACT_ABORT ON --this is especially important
BEGIN TRANSACTION INDX_ADD_ADMIN

/* many stored procedure calls in a loop */
/* ie Str := 'BEGIN INDX_DELSRC ('''+DOS_NAME+'''); END;'; */

COMMIT TRANSACTION INDX_ADD_ADMIN
SET XACT_ABORT OFF
SET NOCOUNT OFF


Now, if you would at least supply what the SS2K statements do.
I can only guess: set transaction_abort off? As in "I don't care
that my transaction has errors, just go on"?
Not possible.
Begin tran/end tran: should probably become a stored procedure
with pragma autonomous_transaction
set nocount off (or on): don't bother - oracle will always be
able to tell you how many rows were affected - you normally
don't ask (in PL/SQL)

I'm sorry Frank, you are right. The set nocount is used to tell SQL
NOT to return row count everytime, it is used out of performance. Here
is the help for XACT_ABORT

SET XACT_ABORT (T-SQL)
Specifies whether Microsoft® SQL Server™ automatically rolls back the
current transaction if a Transact-SQL statement raises a run-time
error.

We don't want to (and cannot) make a whole stored procedure out of
this because the loop goes and fetch data from different sources. my
script on the Delphi side now looks like:

BEGIN
/* call lots of oracle stored procedures with various parameters */
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;

Fairly simple huh? Is there anything I should consider adding to it to
make it foolproof?

Thank you
jeff lambert
Jul 19 '05 #4

P: n/a
Frank <fb*****@nescape.net> wrote in message news:<br**********@news1.tilbu1.nb.home.nl>...
Jeff Lambert wrote:
We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.

What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints, so no editorials please.

Thanks in advance. Jeff.

-- SQL batch script

SET NOCOUNT ON
SET XACT_ABORT ON --this is especially important
BEGIN TRANSACTION INDX_ADD_ADMIN

/* many stored procedure calls in a loop */
/* ie Str := 'BEGIN INDX_DELSRC ('''+DOS_NAME+'''); END;'; */

COMMIT TRANSACTION INDX_ADD_ADMIN
SET XACT_ABORT OFF
SET NOCOUNT OFF


Now, if you would at least supply what the SS2K statements do.
I can only guess: set transaction_abort off? As in "I don't care
that my transaction has errors, just go on"?
Not possible.
Begin tran/end tran: should probably become a stored procedure
with pragma autonomous_transaction
set nocount off (or on): don't bother - oracle will always be
able to tell you how many rows were affected - you normally
don't ask (in PL/SQL)

I'm sorry Frank, you are right. The set nocount is used to tell SQL
NOT to return row count everytime, it is used out of performance. Here
is the help for XACT_ABORT

SET XACT_ABORT (T-SQL)
Specifies whether Microsoft® SQL Server™ automatically rolls back the
current transaction if a Transact-SQL statement raises a run-time
error.

We don't want to (and cannot) make a whole stored procedure out of
this because the loop goes and fetch data from different sources. my
script on the Delphi side now looks like:

BEGIN
/* call lots of oracle stored procedures with various parameters */
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;

Fairly simple huh? Is there anything I should consider adding to it to
make it foolproof?

Thank you
jeff lambert
Jul 19 '05 #5

P: n/a
Jeff Lambert wrote:
Frank <fb*****@nescape.net> wrote in message news:<br**********@news1.tilbu1.nb.home.nl>...
Jeff Lambert wrote:

We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.

What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints, so no editorials please.

Thanks in advance. Jeff.

-- SQL batch script

SET NOCOUNT ON
SET XACT_ABORT ON --this is especially important
BEGIN TRANSACTION INDX_ADD_ADMIN

/* many stored procedure calls in a loop */
/* ie Str := 'BEGIN INDX_DELSRC ('''+DOS_NAME+'''); END;'; */

COMMIT TRANSACTION INDX_ADD_ADMIN
SET XACT_ABORT OFF
SET NOCOUNT OFF


Now, if you would at least supply what the SS2K statements do.
I can only guess: set transaction_abort off? As in "I don't care
that my transaction has errors, just go on"?
Not possible.
Begin tran/end tran: should probably become a stored procedure
with pragma autonomous_transaction
set nocount off (or on): don't bother - oracle will always be
able to tell you how many rows were affected - you normally
don't ask (in PL/SQL)


I'm sorry Frank, you are right. The set nocount is used to tell SQL
NOT to return row count everytime, it is used out of performance. Here
is the help for XACT_ABORT

SET XACT_ABORT (T-SQL)
Specifies whether Microsoft® SQL Server™ automatically rolls back the
current transaction if a Transact-SQL statement raises a run-time
error.

We don't want to (and cannot) make a whole stored procedure out of
this because the loop goes and fetch data from different sources. my
script on the Delphi side now looks like:

BEGIN
/* call lots of oracle stored procedures with various parameters */
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;

Fairly simple huh? Is there anything I should consider adding to it to
make it foolproof?

Thank you
jeff lambert


Yup - can do it. The other end of the scale would be
WHEN OTHERS THEN NULL - ignore every error, just continue.
Yours would rollback every statement since the last commit;
even the ones correctly executed
--
Regards, Frank van Bortel

Jul 19 '05 #6

P: n/a
Jeff Lambert wrote:
Frank <fb*****@nescape.net> wrote in message news:<br**********@news1.tilbu1.nb.home.nl>...
Jeff Lambert wrote:

We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.

What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints, so no editorials please.

Thanks in advance. Jeff.

-- SQL batch script

SET NOCOUNT ON
SET XACT_ABORT ON --this is especially important
BEGIN TRANSACTION INDX_ADD_ADMIN

/* many stored procedure calls in a loop */
/* ie Str := 'BEGIN INDX_DELSRC ('''+DOS_NAME+'''); END;'; */

COMMIT TRANSACTION INDX_ADD_ADMIN
SET XACT_ABORT OFF
SET NOCOUNT OFF


Now, if you would at least supply what the SS2K statements do.
I can only guess: set transaction_abort off? As in "I don't care
that my transaction has errors, just go on"?
Not possible.
Begin tran/end tran: should probably become a stored procedure
with pragma autonomous_transaction
set nocount off (or on): don't bother - oracle will always be
able to tell you how many rows were affected - you normally
don't ask (in PL/SQL)


I'm sorry Frank, you are right. The set nocount is used to tell SQL
NOT to return row count everytime, it is used out of performance. Here
is the help for XACT_ABORT

SET XACT_ABORT (T-SQL)
Specifies whether Microsoft® SQL Server™ automatically rolls back the
current transaction if a Transact-SQL statement raises a run-time
error.

We don't want to (and cannot) make a whole stored procedure out of
this because the loop goes and fetch data from different sources. my
script on the Delphi side now looks like:

BEGIN
/* call lots of oracle stored procedures with various parameters */
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;

Fairly simple huh? Is there anything I should consider adding to it to
make it foolproof?

Thank you
jeff lambert


Yup - can do it. The other end of the scale would be
WHEN OTHERS THEN NULL - ignore every error, just continue.
Yours would rollback every statement since the last commit;
even the ones correctly executed
--
Regards, Frank van Bortel

Jul 19 '05 #7

P: n/a
Very helpful of you Frank. Thanks again.

Jeff Lambert

Frank <fb*****@nescape.net> wrote in message news:<br**********@news4.tilbu1.nb.home.nl>...
Jeff Lambert wrote:
Frank <fb*****@nescape.net> wrote in message news:<br**********@news1.tilbu1.nb.home.nl>...
Jeff Lambert wrote:
We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.

What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints, so no editorials please.

Thanks in advance. Jeff.

-- SQL batch script

SET NOCOUNT ON
SET XACT_ABORT ON --this is especially important
BEGIN TRANSACTION INDX_ADD_ADMIN

/* many stored procedure calls in a loop */
/* ie Str := 'BEGIN INDX_DELSRC ('''+DOS_NAME+'''); END;'; */

COMMIT TRANSACTION INDX_ADD_ADMIN
SET XACT_ABORT OFF
SET NOCOUNT OFF

Now, if you would at least supply what the SS2K statements do.
I can only guess: set transaction_abort off? As in "I don't care
that my transaction has errors, just go on"?
Not possible.
Begin tran/end tran: should probably become a stored procedure
with pragma autonomous_transaction
set nocount off (or on): don't bother - oracle will always be
able to tell you how many rows were affected - you normally
don't ask (in PL/SQL)


I'm sorry Frank, you are right. The set nocount is used to tell SQL
NOT to return row count everytime, it is used out of performance. Here
is the help for XACT_ABORT

SET XACT_ABORT (T-SQL)
Specifies whether Microsoft® SQL Server™ automatically rolls back the
current transaction if a Transact-SQL statement raises a run-time
error.

We don't want to (and cannot) make a whole stored procedure out of
this because the loop goes and fetch data from different sources. my
script on the Delphi side now looks like:

BEGIN
/* call lots of oracle stored procedures with various parameters */
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;

Fairly simple huh? Is there anything I should consider adding to it to
make it foolproof?

Thank you
jeff lambert


Yup - can do it. The other end of the scale would be
WHEN OTHERS THEN NULL - ignore every error, just continue.
Yours would rollback every statement since the last commit;
even the ones correctly executed

Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.