Solved

Setting an Oracle Rollback Segment from Delphi

Posted on 1998-02-23
12
359 Views
Last Modified: 2010-08-05
I am working with Oracle from Delphi.
I need to set a specific "Oracle Rollback Segment" before the transaction start.
How can I do it?
0
Comment
Question by:jpussacq
  • 7
  • 5
12 Comments
 

Author Comment

by:jpussacq
ID: 1359700
Edited text of question
0
 

Author Comment

by:jpussacq
ID: 1359701
Edited text of question
0
 

Expert Comment

by:bijoyn
ID: 1359702
Do u need Sample code ?.

Bijoy.
0
 

Author Comment

by:jpussacq
ID: 1359703
I need sample code or an explanation of how can I do this operation.

0
 

Expert Comment

by:bijoyn
ID: 1359704
Hi Again,

I am sending u a sample code for this. I have dropped two buttons on a form and on the click of one, i create the rollback segment and on the click of the other i drop the rollback segment. U also can create a private rollback segment for the tablespace that u are working on in the same way as demonstrated in the program just by putting the keyword 'PRIVATE' instead of 'PUBLIC'. A Public rollback segment is available to everyone using the tablespace.

----------

unit Testing;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, StdCtrls, ExtCtrls, Buttons, DBTables, comctrls;


type
  TForm1 = class(TForm)
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    procedure FormShow(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
      FQuery         :  TQuery;
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.FormShow(Sender: TObject);
begin
   FQuery := TQuery.Create;
end;

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  FQuery.DatabaseName  := 'ObjMaint'; { You Can give your database name/ Alias name over here }
   with FQuery do
   begin
      SQL.Clear;
      SQL.Add('CREATE PUBLIC ROLLBACK SEGMENT BIJOY1'); { You can give any name as u wish instead of 'Bijoy1' }
      Prepare; { Does not matter if u do not prepare it }
      ExecSQL;
   end;
end;

procedure TForm1.BitBtn2Click(Sender: TObject);
begin
   FQuery.DatabaseName := 'ObjMaint'; { You Can give your database name/ Alias name over here }
   with FQuery do
   begin
      SQL.Clear;
      SQL.Add('DROP ROLLBACK SEGMENT BIJOY1'); { You can give any name as u wish instead of 'Bijoy1' }
      Prepare; { Does not matter if u do not prepare it }
      ExecSQL;
   end;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
   FQuery.Free;
end;

end.
-----------------

I hope it solves the problem.
0
 

Author Comment

by:jpussacq
ID: 1359705
Thank you four your code.
But the problem is not resolved yet.

Supose that my database have five Rollback Segments, named ROLL_SEGM_1, ROLL_SEGM_2, ROLL_SEGM_3, ROLL_SEGM_4 and ROLL_SEGM_5, and I want to use one of this five to process and specific transaction.

How can I do it from Delphi.

In oracle PL-SQL, the solution woul be:

  SET TRANSACTION USE ROLLBACK SEGMENT ROLL_SEGM_5;
  INSERT INTO Table VALUES ('xxx','yyy');
  COMMIT;

How can I write this code in Delphi???
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Expert Comment

by:bijoyn
ID: 1359706
Hi There,

It is no different if u have existing rollback segments. If u know the rollback segments in prior then the following code should work. I am assuming that the rollback segment already exists. The table name and the fields will be what u want.

-----------
procedure TForm1.BitBtn3Click(Sender: TObject);
var
   str_Field1, str_Field2  :  String;
begin
   Qry_Temp.DatabaseName := 'ObjMaint'; { You Can give your database name/ Alias name over here }
   str_Field1  := 'TDefaultDBEdit';
   str_Field2  := 'XXX';
   with Qry_Temp do
   begin
      SQL.Clear;
      SQL.Add('SET TRANSACTION USE ROLLBACK SEGMENT BIJOY1'); { You can give any name as u wish instead of 'Bijoy1' }
      ExecSQL;
      SQL.Clear;
      SQL.Add('INSERT INTO OBJ_COMPPROPS(COMPONENTNAME,PROPERTYNAME) VALUES');
      SQL.Add('(');
      SQL.Add('''' + str_Field1 + '''' + ',' + '''' + str_Field2 + '''');
      SQL.Add(')');
      ExecSQL;
   end;
   Qry_Temp.SQL.Clear;
   Qry_Temp.SQL.Add('COMMIT');
   Qry_Temp.ExecSQL;
end;

-----------------

U can define a query statement and pass parameter values to it if u do not want to construct the query at runtime. That way saying post will commit the transaction on the database.

If u don't know which rollback segment exists then the code will change slightly to find out all the available rollback segments.

Bijoy.
0
 

Author Comment

by:jpussacq
ID: 1359707
All my programs are like this:

  try
   Database1.StartTransaction
   Query1.SQL.Clear;
   Query1.SQL.Add ('INSERT INTO table VALUES ('xxx','yyy');
   Query1.ExecSQL;
   Database1.Commit
  except
   Database1.Rollback;
  end;

Can I add in this code your solution (the following):

  Query1.SQL.Clear;
  Query1.SQL.Add('SET TRANSACTION USE ROLLBACK SEGMENT BIJOY1');
  Query1.ExecSQL;


0
 

Expert Comment

by:bijoyn
ID: 1359708
Yes U definitely can add my code into your programs. In fact instead of adding the whole thing into all programs, put this code into a library procedure and call it from the programs that need it.  Tell me if it solves the problem.

Bijoy.

P.S Do it after u start the transaction. i.e. Database.StartTransaction statement.
0
 

Author Comment

by:jpussacq
ID: 1359709
Tomorrow, I will try to do it.
Thank U.
0
 

Author Comment

by:jpussacq
ID: 1359710
bijoyn:

Your solution is correct.
Thank you.
0
 

Accepted Solution

by:
bijoyn earned 100 total points
ID: 1359711
I Have answered the question.

Bijoy
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
delphi prevent click fast 2 191
Delphi Form ownership 4 73
Need Help Delphi 2010 CheckBox1 Stored value in memo 13 57
Installshield for Embarcadero EX 10.1 Berlin 4 20
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now