Delphi 10.1 Update 2
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production. It is not using unicode.
My client is 32 bit, and my Delphi project is 32 bit. I have a tnsname entry 'wwn3134'
I put a new SQLConnection on a form and set it to non-unicode.
I put a SQLQuery and two SQLStoredProc on the form. I create a stored proc and a package with a proc.
The query and procs all just SELECT firstname FROM CUSTOMER WHERE ROWNUM = 1;
I add a memo to show results add a button to run. It connects and opens the data and presents it.
All is good.
Now I want to connect without using a TNS entry, as when I deploy my app I don't want customers to have to set up a TNS name. I will deploy the Oracle instaclient with my app.
I change my database to 'wwn-bvt-3134:1521/bbts'
It connects and the query runs fine, but the stored procs give me exceptions:
If I turn off ParamCheck at runtime on the stored procs everything works fine. But that isn't very handy as it needs to be on at design time to set up the fields.Could not parse the GetProcedureParameters metadata command. Problem found near: -. Original query: GetProcedureParameters wwn-bvt-3134:1521/bbts . envision . CustomerGet .
Open Package Proc
Am I connecting incorrectly?
I tried setting UseQuoteChar='True' and got
-- run with tnsname Database='wwn3134'Exception: ORA-06550: line 2, column 4:
PLS-00201: identifier 'CustomerGet' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored
- Open Connection
Open Query
query firstname = Herman
Open StoredProc
Proc firstname = Herman
Open Package Proc
Package Proc firstname = Herman
- Open Connection
Open Query
query firstname = Herman
Open StoredProc
Exception: Could not parse the GetProcedureParameters metadata command. Problem found near: -. Original query: GetProcedureParameters wwn-bvt-3134:1521/bbts . envision . CustomerGet .
Open Package Proc
Exception: Could not parse the GetPackageProcedureParameters metadata command. Problem found near: -. Original query: GetPackageProcedureParameters wwn-bvt-3134:1521/bbts . envision . CustomerFuncs . GetPackCustomer .
Code: Select all
object Form1: TForm1
Left = 0
Top = 0
Caption = 'Form1'
ClientHeight = 299
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 Memo1: TMemo
Left = 280
Top = 40
Width = 313
Height = 225
Lines.Strings = (
'Memo1')
TabOrder = 0
end
object Button1: TButton
Left = 136
Top = 232
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 1
OnClick = Button1Click
end
object SQLConnection1: TSQLConnection
ConnectionName = 'Devart Oracle'
DriverName = 'DevartOracle'
LoginPrompt = False
Params.Strings = (
'BlobSize=-1'
'DataBase=wwn-bvt-3134:1521/bbts'
'DriverName=DevartOracle'
'ErrorResourceFile='
'LocaleCode=0000'
'Password=password1'
'Oracle TransIsolation=ReadCommitted'
'RoleName=Normal'
'User_Name=envision'
'LongStrings=False'
'EnableBCD=False'
'InternalName='
'FetchAll=False'
'CharLength=0'
'Charset='
'UseQuoteChar=True'
'UseUnicode=False'
'UnicodeEnvironment=False')
Left = 72
Top = 40
end
object SQLQuery1: TSQLQuery
MaxBlobSize = -1
Params = <>
SQL.Strings = (
' SELECT firstname FROM CUSTOMER WHERE ROWNUM = 1')
SQLConnection = SQLConnection1
Left = 192
Top = 40
object SQLQuery1FIRSTNAME: TStringField
FieldName = 'FIRSTNAME'
Size = 30
end
end
object SQLStoredProc1: TSQLStoredProc
MaxBlobSize = -1
Params = <
item
DataType = ftCursor
Precision = 8000
Name = 'PLIST'
ParamType = ptOutput
Size = 8000
end>
SQLConnection = SQLConnection1
StoredProcName = 'CustomerGet'
Left = 192
Top = 104
object SQLStoredProc1FIRSTNAME: TStringField
FieldName = 'FIRSTNAME'
Size = 30
end
end
object SQLStoredProc2: TSQLStoredProc
MaxBlobSize = -1
Params = <
item
DataType = ftCursor
Precision = 8000
Name = 'PLIST'
ParamType = ptOutput
Size = 8000
end>
PackageName = 'CustomerFuncs'
SQLConnection = SQLConnection1
StoredProcName = 'GetPackCustomer'
Left = 192
Top = 160
object SQLStoredProc2FIRSTNAME: TStringField
FieldName = 'FIRSTNAME'
Size = 30
end
end
end
Code: Select all
unit testproc;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBXDevartOracle, Data.FMTBcd,
Vcl.StdCtrls, Data.SqlExpr, Data.DB;
type
TForm1 = class(TForm)
SQLConnection1: TSQLConnection;
Memo1: TMemo;
Button1: TButton;
SQLQuery1: TSQLQuery;
SQLStoredProc1: TSQLStoredProc;
SQLStoredProc2: TSQLStoredProc;
SQLQuery1FIRSTNAME: TStringField;
SQLStoredProc1FIRSTNAME: TStringField;
SQLStoredProc2FIRSTNAME: TStringField;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
begin
memo1.Lines.Clear;
memo1.Lines.Add('Open Connection');
SQLConnection1.Open;
memo1.Lines.Add('Open Query');
sqlQuery1.Open;
memo1.Lines.Add('query firstname = '+SQLQuery1FIRSTNAME.AsString);
memo1.Lines.Add('Open StoredProc');
try
SQLStoredProc1.Open;
memo1.Lines.Add('Proc firstname = '+SQLStoredProc1FIRSTNAME.AsString);
except
on e:exception do begin
Memo1.Lines.Add('Exception: '+e.Message);
end;
end;
memo1.Lines.Add('Open Package Proc');
try
SQLStoredProc2.Open;
memo1.Lines.Add('Package Proc firstname = '+SQLStoredProc2FIRSTNAME.AsString);
except
on e:exception do begin
Memo1.Lines.Add('Exception: '+e.Message);
end;
end;
end;
end.
Code: Select all
CREATE OR REPLACE PROCEDURE CustomerGet(pList OUT NOCOPY SYS_REFCURSOR)
AS
BEGIN
OPEN pList FOR
SELECT firstname
FROM CUSTOMER
WHERE ROWNUM = 1;
END;
/
Code: Select all
CREATE OR REPLACE PACKAGE Envision.CustomerFuncs AUTHID Definer
AS
PROCEDURE GetPackCustomer (pList OUT NOCOPY SYS_REFCURSOR);
END;
/
CREATE OR REPLACE PACKAGE BODY Envision.CustomerFuncs
AS
PROCEDURE GetPackCustomer (pList OUT NOCOPY SYS_REFCURSOR)
AS BEGIN
OPEN pList FOR
SELECT firstname
FROM CUSTOMER
WHERE ROWNUM = 1;
END;
END;
/