473,325 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

SQL Batch Script from MSSQL to Oracle 8i

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
7 8968
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Philip Mette | last post by:
I am in a crunch and need to covert this Oracle statement to MSSQL. Is there any Oracle/MSSQL experts out there that can help me? I do not understand the syntax enough to modify this. Thanks so...
3
by: konsu | last post by:
hello, i would like to write a batch file with all sql commands necessary to create a database, its tables, and populate them with initial data. would the experts please help me with these...
2
by: Yak | last post by:
All, International company in Australia needs an allround DBA (development&Admin) in MSSQL and Oracle. If you have 5+ experience in development and (not OR) Administration in both MSSQL and...
8
by: Yusuf INCEKARA | last post by:
I have a stored procedure : CREATE PROCEDURE STP_GETSTORELIST @RETCUR CURSOR VARYING OUTPUT AS set @RETCUR = CURSOR FORWARD_ONLY STATIC FOR SELECT ID,STORE_NAME FROM T_INF_STORE ORDER BY...
2
by: | last post by:
Hi, I have just started using MSSQL and the DOS environment at work. I have a lot of experience with Sybase and the UNIX environment, but this is a whole new ball of wax. I'd like to use osql...
4
by: kenneth.osenbroch | last post by:
Hi, I have a MSSQL Server communicating with an Oracle database through a MSSQL linked server using a MS ODBC connection. If I query the Oracle database through the Oracle ODBC 32Bit Test, the...
4
by: Jeff Lambert | last post by:
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...
3
by: John | last post by:
Hi. I have a number of batch jobs that are ran nightly on our Windows 2000 based Oracle 8.1.7 (soon to be 9i) server. I have these designed just right, so the Windows Scheduled Tasks runs them...
8
by: santhanalakshmi | last post by:
Hi, my batch file named cpf.bat COPY C:\Progra~1\Micros~1\MSSQL\BACKUP\acaddb.bak F:\DATABACKUP\acaddb.bak Its working fine. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.