Solved

Setting an Oracle Rollback Segment from Delphi

Posted on 1998-02-23
12
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

695 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