ORA-64219 on 18c

jdorlon · Post by **jdorlon** » Mon 01 Oct 2018 20:21

Hello,

This works fine on Oracle 12cR2 and older. I am only getting an error on 18c.

I have a statement like this:

Insert into CLOB_COPY_TEST
( CATEGORY, DESCRIPTION, SCRIPT)
values
( :v0, :v1, Empty_Clob())
returning  SCRIPT into  :v2

which, when run from a TSmartQuery, is throwing ORA-64219: Invalid LOB locator encountered.

I am using a 32 bit 12cR2 client in both cases. ODAC version 10.2.6, Rad Studio 10.1 Berlin.

Thanks,

John Dorlon

MaximG · Post by **MaximG** » Tue 02 Oct 2018 07:34

Please check the behavior you describe using the latest versions of 10.2.7 (17-Sep-18). If the problem occurs in this version as well, please provide an example of code that causes error ORA-64219 in your environment.

jdorlon · Post by **jdorlon** » Tue 02 Oct 2018 16:30

I can reproduce the problem with 10.2.7. Code for demo project is below.

Unit1.dfm:

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 259
  ClientWidth = 486
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object GroupBox1: TGroupBox
    Left = 8
    Top = 8
    Width = 142
    Height = 243
    Caption = 'Source'
    TabOrder = 0
    object lbSource: TLabel
      Left = 6
      Top = 198
      Width = 72
      Height = 13
      Caption = 'Not Connected'
    end
    object eSourceUser: TLabeledEdit
      Left = 6
      Top = 36
      Width = 123
      Height = 21
      EditLabel.Width = 22
      EditLabel.Height = 13
      EditLabel.Caption = 'User'
      TabOrder = 0
      Text = 'jdorlon'
    end
    object eSourcePassword: TLabeledEdit
      Left = 6
      Top = 78
      Width = 121
      Height = 21
      EditLabel.Width = 46
      EditLabel.Height = 13
      EditLabel.Caption = 'Password'
      TabOrder = 1
      Text = 'jdorlon'
    end
    object eSourceDB: TLabeledEdit
      Left = 6
      Top = 121
      Width = 121
      Height = 21
      EditLabel.Width = 46
      EditLabel.Height = 13
      EditLabel.Caption = 'Database'
      TabOrder = 2
      Text = 'Azure_12CR2_Plug'
    end
    object Button1: TButton
      Left = 6
      Top = 158
      Width = 75
      Height = 25
      Caption = 'Connect'
      TabOrder = 3
      OnClick = Button1Click
    end
  end
  object GroupBox2: TGroupBox
    Left = 174
    Top = 8
    Width = 142
    Height = 243
    Caption = 'Target'
    TabOrder = 1
    object lbTarget: TLabel
      Left = 6
      Top = 198
      Width = 72
      Height = 13
      Caption = 'Not Connected'
    end
    object eTargetUser: TLabeledEdit
      Left = 6
      Top = 36
      Width = 121
      Height = 21
      EditLabel.Width = 22
      EditLabel.Height = 13
      EditLabel.Caption = 'User'
      TabOrder = 0
      Text = 'jdorlon'
    end
    object eTargetPassword: TLabeledEdit
      Left = 6
      Top = 78
      Width = 121
      Height = 21
      EditLabel.Width = 46
      EditLabel.Height = 13
      EditLabel.Caption = 'Password'
      TabOrder = 1
      Text = 'jdorlon'
    end
    object eTargetDB: TLabeledEdit
      Left = 6
      Top = 121
      Width = 121
      Height = 21
      EditLabel.Width = 46
      EditLabel.Height = 13
      EditLabel.Caption = 'Database'
      TabOrder = 2
      Text = 'Azure_18c_Plug'
    end
    object Button2: TButton
      Left = 6
      Top = 158
      Width = 75
      Height = 25
      Caption = 'Connect'
      TabOrder = 3
      OnClick = Button2Click
    end
  end
  object btnCreate: TButton
    Left = 331
    Top = 147
    Width = 137
    Height = 25
    Caption = 'Create Objects'
    Enabled = False
    TabOrder = 2
    OnClick = btnCreateClick
  end
  object btnCopy: TButton
    Left = 331
    Top = 178
    Width = 137
    Height = 25
    Caption = 'Copy Data'
    Enabled = False
    TabOrder = 3
    OnClick = btnCopyClick
  end
  object btnDrop: TButton
    Left = 331
    Top = 116
    Width = 137
    Height = 25
    Caption = 'Drop Objects'
    Enabled = False
    TabOrder = 4
    OnClick = btnDropClick
  end
  object Memo1: TMemo
    Left = 322
    Top = 8
    Width = 161
    Height = 89
    Lines.Strings = (
      'If target is 12cR2 or older, '
      'The "Copy Data" button '
      'works.'
      ''
      'If target is 18c, ORA-64219 '
      'is thrown')
    TabOrder = 5
  end
  object srcSession: TOraSession
    Options.UseUnicode = True
    Options.EnableNumbers = True
    Options.UnicodeEnvironment = True
    HomeName = 'OraClient12Home2_32bit'
    Left = 326
    Top = 142
  end
  object TargetSession: TOraSession
    Options.UseUnicode = True
    Options.EnableNumbers = True
    Options.UnicodeEnvironment = True
    HomeName = 'OraClient12Home2_32bit'
    Left = 357
    Top = 144
  end
  object srcQry: TSmartQuery
    Session = srcSession
    Options.TemporaryLobUpdate = True
    ObjectView = True
    Left = 330
    Top = 183
  end
  object TargetQry: TSmartQuery
    Session = TargetSession
    Options.UpdateAllFields = True
    ObjectView = True
    Left = 358
    Top = 179
  end
  object scrCreateObjects: TOraScript
    SQL.Strings = (
      'CREATE TABLE CLOB_COPY_TEST'
      '('
      '  CATEGORY     VARCHAR2(30 CHAR),'
      '  DESCRIPTION  VARCHAR2(200 BYTE),'
      '  SCRIPT       CLOB'
      ');'
      ''
      ''
      '')
    Session = srcSession
    Left = 329
    Top = 219
  end
  object scrInsertRows: TOraScript
    SQL.Strings = (
      'Insert into CLOB_COPY_TEST'
      '   (CATEGORY, DESCRIPTION, SCRIPT)'
      ' Values'
      
        '   ('#39'z'#39', '#39'a'#39', '#39'dsfhdsfhdshfdsfhdsfhkioyusdfb dsgfhfg hfgdfh sdh ' +
        'jfh '#39');'
      'Insert into CLOB_COPY_TEST'
      '   (CATEGORY, DESCRIPTION, SCRIPT)'
      ' Values'
      '   ('#39'z'#39', '#39'a'#39', '#39'dh'#39');'
      'Insert into CLOB_COPY_TEST'
      '   (CATEGORY, DESCRIPTION, SCRIPT)'
      ' Values'
      '   ('#39'clob'#39', '#39'clob'#39', '#39'hdhfdgh'#39');'
      'Insert into CLOB_COPY_TEST'
      '   (CATEGORY, DESCRIPTION, SCRIPT)'
      ' Values'
      '   ('#39'clob'#39', '#39'clob'#39', '#39'sdhsdfh'#39');'
      'Insert into CLOB_COPY_TEST'
      '   (CATEGORY, DESCRIPTION, SCRIPT)'
      ' Values'
      '   ('#39'clob'#39', '#39'clob'#39', '#39'4545ysdhfdgjfhgyrtjfhdfhsdf'#39');'
      'Insert into CLOB_COPY_TEST'
      '   (CATEGORY, DESCRIPTION, SCRIPT)'
      ' Values'
      '   ('#39'clob'#39', '#39'clob'#39', '#39'sdfhdsfhh'#39');'
      'COMMIT;')
    Session = srcSession
    Left = 367
    Top = 218
  end
  object scrDropObjects: TOraScript
    SQL.Strings = (
      'drop table CLOB_COPY_TEST purge;')
    Session = srcSession
    Left = 413
    Top = 215
  end
end

Unit1.pas

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, MemDS, DBAccess, Ora, OraSmart, OraCall, Vcl.StdCtrls, Vcl.ExtCtrls,
  DAScript, OraScript;

type
  TForm1 = class(TForm)
    GroupBox1: TGroupBox;
    eSourceUser: TLabeledEdit;
    eSourcePassword: TLabeledEdit;
    eSourceDB: TLabeledEdit;
    Button1: TButton;
    GroupBox2: TGroupBox;
    eTargetUser: TLabeledEdit;
    eTargetPassword: TLabeledEdit;
    eTargetDB: TLabeledEdit;
    Button2: TButton;
    btnCreate: TButton;
    btnCopy: TButton;
    srcSession: TOraSession;
    TargetSession: TOraSession;
    srcQry: TSmartQuery;
    TargetQry: TSmartQuery;
    scrCreateObjects: TOraScript;
    scrInsertRows: TOraScript;
    lbSource: TLabel;
    lbTarget: TLabel;
    btnDrop: TButton;
    scrDropObjects: TOraScript;
    Memo1: TMemo;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure btnCreateClick(Sender: TObject);
    procedure btnCopyClick(Sender: TObject);
    procedure btnDropClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  srcSession.Username := eSourceUser.Text;
  srcSession.Password := eSourcePassword.Text;
  srcSession.Server := eSourceDB.Text;
  srcSession.Connect;
  lbSource.Caption := 'Connected';
  btnDrop.Enabled := (lbSource.Caption = 'Connected') and (lbTarget.Caption = 'Connected');
  btnCreate.Enabled := btnDrop.Enabled;
  btnCopy.Enabled := btnDrop.Enabled;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  TargetSession.Username := eTargetUser.Text;
  TargetSession.Password := eTargetPassword.Text;
  TargetSession.Server := eTargetDB.Text;
  TargetSession.Connect;
  lbTarget.Caption := 'Connected';
  btnDrop.Enabled := (lbSource.Caption = 'Connected') and (lbTarget.Caption = 'Connected');
  btnCreate.Enabled := btnDrop.Enabled;
  btnCopy.Enabled := btnDrop.Enabled;
end;

procedure TForm1.btnCreateClick(Sender: TObject);
begin
  screen.cursor := crHourglass;
  try
    scrCreateObjects.Session := srcSession;
    scrCreateObjects.Execute;
    scrCreateObjects.Session := targetSession;
    scrCreateObjects.Execute;
    scrInsertRows.Session := srcSession;
    scrInsertRows.Execute;
    // don't insert rows into target, we'll try to copy them...
  finally
    screen.cursor := crDefault;
  end;
end;

procedure TForm1.btnCopyClick(Sender: TObject);
begin
  screen.cursor := crHourglass;
  try
    SrcQry.ObjectView := true;
    SrcQry.Options.ExtendedFieldsInfo := false;
    SrcQry.Options.SetFieldsReadOnly := false;
    SrcQry.OptionsDS.RawAsString := True;

    TargetQry.ObjectView := true;
    TargetQry.Options.ExtendedFieldsInfo := false;
    TargetQry.Options.SetFieldsReadOnly := false;
    TargetQry.OptionsDS.RawAsString := True;

    SrcQry.Session := srcSession;
    srcQry.sql.Text := 'Select * from CLOB_COPY_TEST';
    srcQry.Execute;
    TargetQry.Session := TargetSession;
    TargetQry.sql.Text := 'insert into CLOB_COPY_TEST(category, description, script) values (:v0, :v1, empty_clob()) returning script into :v2';

    TargetQry.ParamByName('v0').DataType := srcQry.FieldByName('CATEGORY').DataType;
    TargetQry.ParamByName('v1').DataType := srcQry.FieldByName('DESCRIPTION').DataType;
    TargetQry.ParamByName('v2').DataType := srcQry.FieldByName('SCRIPT').DataType;
    TargetQry.ParamByName('v0').ParamType := ptInput;
    TargetQry.ParamByName('v1').ParamType := ptInput;
    TargetQry.ParamByName('v2').ParamType := ptInput;

    while not SrcQry.eof do
    begin
      TargetQry.ParamByName('v0').AsString := SrcQry.FieldByName('CATEGORY').asString;
      TargetQry.ParamByName('v1').AsString := SrcQry.FieldByName('DESCRIPTION').asString;
      TargetQry.ParamByName('v2').AsOraClob := SrcQry.GetLob('SCRIPT');
      TargetQry.Execute;
      srcQry.Next;
    end;
    TargetQry.Session.Commit;
  finally
    screen.cursor := crDefault;
  end;
end;

procedure TForm1.btnDropClick(Sender: TObject);
begin
  screen.cursor := crHourglass;
  try
    scrDropObjects.Session := srcSession;
    scrDropObjects.Execute;
    scrDropObjects.Session := targetSession;
    scrDropObjects.Execute;
  finally
    screen.cursor := crDefault;
  end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  OraCall.OCIUnicode := True;
end;

end.

Project1.dpr

Code: Select all

program Project1;

uses
  Vcl.Forms,
  Unit1 in 'Unit1.pas' {Form1};

{$R *.res}

begin
  Application.Initialize;
  Application.MainFormOnTaskbar := True;
  Application.CreateForm(TForm1, Form1);
  Application.Run;
end.

Thank you.

MaximG · Post by **MaximG** » Mon 08 Oct 2018 09:20

Thank you for the information. We will investigate the described issue and let you know the results shortly.

jdorlon · Post by **jdorlon** » Thu 06 Dec 2018 20:53

Hello,

It has been about 2 months since I posted this. Can you reproduce this and will it be fixed soon?

Thank you,

John Dorlon

MaximG · Post by **MaximG** » Fri 07 Dec 2018 06:47

Unfortunately, we could not reproduce the issue according to your description. Try reproducing the issue with the latest version of 10.3.8 (26-Nov-2018) and let us know about the results.

jdorlon · Post by **jdorlon** » Fri 07 Dec 2018 14:33

I have just downloaded the latest ODAC version and I still get the error.

Rad Studio 10.1. Berlin
ODAC version 10.3.8
Oracle client version 12.2
Oracle server 12.2 (source), 18c target <---- this is the key

Just run the app. Make both connections, click "create objects", click "copy data".

If target database version is 18c, ORA-64219 "invalid lob locator" is thrown.

Please let me know if you can reproduce this or not.

MaximG · Post by **MaximG** » Wed 12 Dec 2018 11:18

We managed to reproduce the issue in our environment. We will investigate the causes of the ORA-64219 error and will let you know the results shortly.

MaximG · Post by **MaximG** » Wed 12 Dec 2018 15:43

We carefully investigated the sent project code after reproducing the error in our test environment and came to the following conclusion. The ORA-64219 error cause is due to
incorrect assigning a value to the CLOB type parameter :

Code: Select all

TargetQry.ParamByName('v2').AsOraClob := SrcQry.GetLob('SCRIPT');

When using different sessions, such code will lead to an error. As a LOB locator obtained for one session will be invalid when trying to use it directly in another.
To avoid this behavior, change the assignment as follows:

Code: Select all

TargetQry.ParamByName('v2').AsOraClob.AsWideString := SrcQry.GetLob('SCRIPT').AsWideString;

jdorlon · Post by **jdorlon** » Wed 12 Dec 2018 15:45

OK, thank you. And what about BLOBs?

jdorlon · Post by **jdorlon** » Wed 12 Dec 2018 20:16

Nevermind, it looks like ".AsBytes" will work for BLOB

MaximG · Post by **MaximG** » Thu 13 Dec 2018 11:54

You are absolutely right. When working with BLOB type parameters, the AsBytes property is used in your sample.

Devart Forums

ORA-64219 on 18c

ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c

Re: ORA-64219 on 18c