Setting an Oracle Rollback Segment from Delphi

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?
jpussacqAsked:
Who is Participating?
 
bijoynConnect With a Mentor Commented:
I Have answered the question.

Bijoy
0
 
jpussacqAuthor Commented:
Edited text of question
0
 
jpussacqAuthor Commented:
Edited text of question
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
bijoynCommented:
Do u need Sample code ?.

Bijoy.
0
 
jpussacqAuthor Commented:
I need sample code or an explanation of how can I do this operation.

0
 
bijoynCommented:
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
 
jpussacqAuthor Commented:
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
 
bijoynCommented:
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
 
jpussacqAuthor Commented:
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
 
bijoynCommented:
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
 
jpussacqAuthor Commented:
Tomorrow, I will try to do it.
Thank U.
0
 
jpussacqAuthor Commented:
bijoyn:

Your solution is correct.
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.