ORA-21700: object does not exists or is marked for delete

[AICoder]

Hello!
Has a problem with a PLSQL_Table (in Oracle v.12.2).
The construction with a PLSQL Table (associative array) stopped working in the select Query.
ORACLE:

Code: Select all

create or replace package p_test is

    -- Purpose : test trouble
    
    type t_number_tbl is table of number(30) index by pls_integer;
  
    function fn_get
    (
        p_tab   t_number_tbl
    ) return sys_refcursor;

end p_test;
/
create or replace package body p_test is
    function fn_get
    (
        p_tab   t_number_tbl
    ) return sys_refcursor
    is
        v_result sys_refcursor;
    begin
        open v_result for
            select
                *
            from
                table(p_tab);
        return v_result;
    end;
end p_test;
/

DELPHI:
DFM

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'FTest ORA-21700'
  ClientHeight = 528
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object btn1: TButton
    Left = 200
    Top = 160
    Width = 75
    Height = 25
    Caption = 'Get normal'
    TabOrder = 0
    OnClick = btn1Click
  end
  object cxgrd1: TcxGrid
    Left = 48
    Top = 232
    Width = 505
    Height = 200
    TabOrder = 1
    object cxgrdbtblvwTest: TcxGridDBTableView
      Navigator.Buttons.CustomButtons = <>
      DataController.DataSource = ds1
      DataController.Summary.DefaultGroupSummaryItems = <>
      DataController.Summary.FooterSummaryItems = <>
      DataController.Summary.SummaryGroups = <>
    end
    object cxgrdlvlGrid1Level1: TcxGridLevel
      GridView = cxgrdbtblvwTest
    end
  end
  object btn2: TButton
    Left = 296
    Top = 160
    Width = 75
    Height = 25
    Caption = 'Get with wrap'
    TabOrder = 2
    OnClick = btn1Click
  end
  object orsn1: TOraSession
    Options.UseUnicode = True
    Options.EnableNumbers = True
    Options.UnicodeEnvironment = True
    Left = 200
    Top = 96
  end
  object q1: TOraQuery
    Session = orsn1
    SQL.Strings = (
      'begin'
      '    -- Call the function'
      '    :result := p_test.fn_get(p_tab => :p_tab);'
      'end;')
    Options.NumberRange = True
    Options.AutoPrepare = True
    Options.StatementCache = True
    Left = 248
    Top = 96
    ParamData = <
      item
        DataType = ftCursor
        Name = 'result'
        Value = 'Object'
      end
      item
        DataType = ftFloat
        Name = 'p_tab'
        ParamType = ptInput
        Value = nil
        Table = True
      end>
  end
  object ds1: TOraDataSource
    DataSet = q1
    Left = 200
    Top = 32
  end
  object q2: TOraQuery
    Session = orsn1
    SQL.Strings = (
      'declare'
      '    p_tab p_test.t_number_tbl;'
      'begin'
      '    -- Call the function'
      '    p_tab := :p_tab;'
      '    :result := p_test.fn_get(p_tab => p_tab);'
      'end;')
    Left = 296
    Top = 96
    ParamData = <
      item
        DataType = ftFloat
        Name = 'p_tab'
        ParamType = ptInputOutput
        Value = Null
        Table = True
      end
      item
        DataType = ftCursor
        Name = 'result'
        Value = 'Object'
      end>
  end
  object ConnectDialog1: TConnectDialog
    Caption = 'Connect'
    ConnectButton = 'Connect'
    CancelButton = 'Cancel'
    Server.Caption = 'Server'
    Server.Visible = True
    Server.Order = 1
    UserName.Caption = 'User Name'
    UserName.Visible = True
    UserName.Order = 2
    Password.Caption = 'Password'
    Password.Visible = True
    Password.Order = 3
    Home.Caption = 'Home Name'
    Home.Visible = False
    Home.Order = 0
    Direct.Caption = 'Direct'
    Direct.Visible = False
    Direct.Order = 6
    Schema.Caption = 'Schema'
    Schema.Visible = False
    Schema.Order = 4
    Role.Caption = 'Connect Mode'
    Role.Visible = False
    Role.Order = 5
    Left = 296
    Top = 32
  end
end

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, cxGraphics, cxControls, cxLookAndFeels, cxLookAndFeelPainters, cxStyles, cxCustomData, cxFilter, cxData,
  cxDataStorage, cxEdit, cxNavigator, cxDataControllerConditionalFormattingRulesManagerDialog, Data.DB, cxDBData, DBAccess, Ora, cxGridLevel,
  cxClasses, cxGridCustomView, cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxGrid, Vcl.StdCtrls, MemDS, OraCall, OdacVcl;

type
  TForm1 = class(TForm)
    orsn1: TOraSession;
    q1: TOraQuery;
    btn1: TButton;
    cxgrdbtblvwTest: TcxGridDBTableView;
    cxgrdlvlGrid1Level1: TcxGridLevel;
    cxgrd1: TcxGrid;
    ds1: TOraDataSource;
    btn2: TButton;
    q2: TOraQuery;
    ConnectDialog1: TConnectDialog;
    procedure btn1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btn1Click(Sender: TObject);
var
  vTab: variant;
  i: integer;
  LQuery: TOraQuery;
begin
  if Sender = btn1 then
    LQuery := q1
  else
    LQuery := q2;
  if Assigned(LQuery.Session) and not LQuery.Session.Connected then
    LQuery.Session.Connected := True;
  cxgrdbtblvwTest.ClearItems;
  try
    ds1.DataSet := LQuery;
    LQuery.DisableControls;
    LQuery.Active := False;
    vTab := VarArrayCreate([1, 100], varVariant);
    for i := 1 to 100 do
      vTab[i] := i;
    LQuery.ParamByName('p_tab').Value := vTab;
    LQuery.Active := True;
  finally
    LQuery.EnableControls;
    cxgrdbtblvwTest.DataController.CreateAllItems;
  end;
end;

end.

Program

Code: Select all

program PLSQL_Table_delphi;

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

{$R *.res}

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

After click on button "Get normal" GET Oracle exception:

---------------------------
Plsql_table_delphi
---------------------------
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "...P_TEST", line 10
ORA-06512: at line 3

---------------------------
ОК
---------------------------

[MaximG]

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

[ZloyTox]

Hello, AlCoder!
It seems to be Oracle 12.2 problem.
I have used workaround for case like this.

Code: Select all

create or replace package body p_test is
    function fn_get
    (
        p_tab   t_number_tbl
    ) return sys_refcursor
    is
        v_result sys_refcursor;
>>>     v_tab t_number_tbl := p_tab;
    begin
        open v_result for
            select
                *
            from
>>>             table(v_tab);
        return v_result;
    end;
end p_test;
/

[MaximG]

Indeed, this issue occurs when using Oracle 12c R2 and higher and does not depend on the behavior of our components. You can use the solution proposed above, or change the function call p_test.fn_get as shown in your example