Devart Forums

Code: Select all

-- Create table
create table TABLESIS
(
  filial               VARCHAR2(3) not null,
  registro             VARCHAR2(20) not null,
  modulo               CHAR(1) not null,
  moeda                VARCHAR2(3) not null,
  status               VARCHAR2(10),
  dataregistro         DATE,
  dataretificado       DATE,
  cnpj_fil             VARCHAR2(14),
  nome_fil             VARCHAR2(80),
  pais                 VARCHAR2(3),
  pessoaext            VARCHAR2(20),
  nomepessoaext        VARCHAR2(150),
  endpessoaext         VARCHAR2(150),
  nif                  VARCHAR2(40),
  exportacao           CHAR(1),
  importacao           CHAR(1),
  infocomplementar     CLOB,
  old_pais             VARCHAR2(3),
  old_nomepessoaext    VARCHAR2(150),
  old_endpessoaext     VARCHAR2(150),
  old_nif              VARCHAR2(40),
  old_infocomplementar CLOB,
  old_moeda            VARCHAR2(3),
  numlote              NUMBER(5),
  numloteretificacao   NUMBER(7),
  operadorincl         VARCHAR2(20),
  manutencaoincl       DATE,
  operador             VARCHAR2(20),
  manutencao           DATE,
  cliente              VARCHAR2(14),
  filialcliente        VARCHAR2(1),
  docto                VARCHAR2(44),
  liberadofat          VARCHAR2(1),
  liberadoregistro     VARCHAR2(1),
  liberadoretifica     VARCHAR2(1),
  dataliberadofat      DATE,
  dataliberadoregistro DATE,
  dataliberadoretifica DATE,
  diretorio            VARCHAR2(255),
  numlotecarga         NUMBER(7),
  operadorlibreg       VARCHAR2(20),
  operadorlibret       VARCHAR2(20),
  protocolo            VARCHAR2(100),
  numerorvsras         VARCHAR2(20),
  dataconferido        DATE,
  conferente           VARCHAR2(5),
  protocoloret         VARCHAR2(100),
  dataconferidoret     DATE,
  conferenteret        VARCHAR2(5),
  retificado           VARCHAR2(1),
  datasolicitacao      DATE,
  solicitante          VARCHAR2(20),
  refcliente           VARCHAR2(40),
  ordemservico         NUMBER(10),
  codempresa           VARCHAR2(20) not null
)
tablespace DBF01
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TABLESIS
  add constraint TABLESIS_PK primary key (CODEMPRESA, FILIAL, REGISTRO, MODULO)
  using index 
  tablespace DBF01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

Code: Select all

select a.*, b.descricao descricaostatus, a.rowid 
from  tablesis a, servstatus b
where
a.codempresa = :codempresa 
and a.modulo = 'V'
and b.status = a.status
and ((a.filial in (select filial from operadorperfil where codempresa = :codempresa and operador = :operador))
     or (nvl(:administrador,'N') = 'S'))
order by a.registro

Code: Select all

-- Create table
create table SERVSTATUS
(
  status    VARCHAR2(2) not null,
  descricao VARCHAR2(50)
)
tablespace DBF01
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SERVSTATUS
  add constraint SERVSTATUS_PK primary key (STATUS)
  using index 
  tablespace DBF01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete, alter on SERVSTATUS to IMPMANAGER;
grant select, insert, update, delete, alter on SERVSTATUS to IMPSELECT;

Code: Select all

-- Create table
create table OPERADORPERFIL
(
  operador   VARCHAR2(20) not null,
  sequencia  NUMBER,
  filial     VARCHAR2(3) not null,
  codempresa VARCHAR2(20) not null
)
tablespace DBF01
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate indexes 
create index OPERADORPERFIL on OPERADORPERFIL (OPERADOR)
  tablespace DBF01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table OPERADORPERFIL
  add constraint OPERADORPERFIL_PK primary key (CODEMPRESA, OPERADOR, FILIAL)
  using index 
  tablespace DBF01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
alter table OPERADORPERFIL
  add constraint R_143 foreign key (CODEMPRESA)
  references EMPRESA (CODEMPRESA);

Code: Select all

CREATE TABLE ODACTESTROBEFOREEDIT (
  ID        INTEGER,
  TEXT      VARCHAR2(10),
  LONGTEXT  CLOB
);

ALTER TABLE ODACTESTROBEFOREEDIT ADD CONSTRAINT PK_ODACTESTROBEFOREEDIT PRIMARY KEY (ID);

INSERT INTO ODACTESTROBEFOREEDIT VALUES(1, '10', 'CLOB');

Code: Select all

program ODACEditCLOBWithroBeforeEdit;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  DB,
  Ora,
  OraSmart,
  DBAccess;

{
CREATE TABLE ODACTESTROBEFOREEDIT (
  ID        INTEGER,
  TEXT      VARCHAR2(10),
  LONGTEXT  CLOB
);

ALTER TABLE ODACTESTROBEFOREEDIT ADD CONSTRAINT PK_ODACTESTROBEFOREEDIT PRIMARY KEY (ID);

INSERT INTO ODACTESTROBEFOREEDIT VALUES(1, '10', 'CLOB');
}

const
  cServer = 'YourServer';
  cUsername = 'YourUser';
  cPassword = 'YourPassword';

function TestEditCLOBWithroBeforeEdit: Boolean;
var
  se: TOraSession;
  Counter: Integer;

  procedure UpdateTEXTField(AID: Integer; AText: string);
  var
    qr: TOraQuery;
  begin
    qr := TOraQuery.Create(nil);
    try
      with qr do
      begin
        AutoCommit := True;
        SQL.Add('UPDATE ODACTESTROBEFOREEDIT SET TEXT = :TEXT WHERE ID = :ID');
        ParamByName('ID').AsInteger := AID;
        ParamByName('TEXT').AsString := AText;
        ExecSQL;
      end;
    finally
      qr.Free;
    end;
  end;

  procedure CheckFieldValue(ATest: Integer; AField: TField; AExpectedValue: string);
  var
    ActualValue: string;
  begin
    ActualValue := AField.AsString;
    if ActualValue = AExpectedValue then
      Inc(Counter)
    else
      WriteLn('FAIL #', ATest, ' Expected: ', AExpectedValue, ' Actual: ', ActualValue);
  end;

var
  qr: TSmartQuery;
  ID: Integer;
  F: TField;
begin
  Counter := 0;
  se := TOraSession.Create(nil);
  qr := TSmartQuery.Create(nil);
  try
    se.Server := cServer;
    se.Username := cUserName;
    se.Password := cPassword;
    with qr do
    begin
      Session := se;
      KeyFields := 'ID';
      RefreshOptions := RefreshOptions + [roBeforeEdit];
      SQL.Add('SELECT * FROM ODACTESTROBEFOREEDIT');

      Open;
      ID := FieldByName('ID').AsInteger;
      UpdateTEXTField(ID, '10');
      Close;

      Open;
      F := FieldByName('TEXT');

      CheckFieldValue(1, F, '10');

      UpdateTEXTField(ID, '11');
      Edit;
      CheckFieldValue(2, F, '11');

      F.AsString := '12';
      Post;
      CheckFieldValue(3, F, '12');

      Close;
    end;
  finally
    se.Free;
  end;
  Result := Counter = 3;
end;

begin
  try
    if TestEditCLOBWithroBeforeEdit then
      WriteLn('PASS')
    else
      WriteLn('FAIL');
  except
    on E: Exception do
    begin
      WriteLn('FAIL - Exception Error');
      WriteLn('  E.ClassName = ', E.ClassName);
      WriteLn('    E.Message = ', E.Message);
    end;
  end;
  ReadLn;
end.