How to connect without a TNS name entry

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
wnielsenbb
Posts: 3
Joined: Mon 01 May 2017 17:31

How to connect without a TNS name entry

Post by wnielsenbb » Tue 02 May 2017 18:41

I am using the dbExpress Driver for Oracle version 6.9.13
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:
Could not parse the GetProcedureParameters metadata command. Problem found near: -. Original query: GetProcedureParameters wwn-bvt-3134:1521/bbts . envision . CustomerGet .
Open Package Proc
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.

Am I connecting incorrectly?
I tried setting UseQuoteChar='True' and got
Exception: ORA-06550: line 2, column 4:
PLS-00201: identifier 'CustomerGet' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored
-- run with tnsname Database='wwn3134'
  • Open Connection
    Open Query
    query firstname = Herman
    Open StoredProc
    Proc firstname = Herman
    Open Package Proc
    Package Proc firstname = Herman
-- run with connection string Database='wwn-bvt-3134:1521/bbts' or Database='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wwn-bvt-3134)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=bbts)))'
  • 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 .
Form:

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:

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.
Stored proc code

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;
/
Package Code:

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;
/

MaximG
Devart Team
Posts: 943
Joined: Mon 06 Jul 2015 11:34

Re: How to connect without a TNS name entry

Post by MaximG » Wed 10 May 2017 15:01

We are investigating this behavior causes and will inform you about the results soon

Post Reply