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;
/
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
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.
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.
---------------------------
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
---------------------------
ОК
---------------------------