Package problem when there is a dot in the tns-name

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
hclaassens
Posts: 14
Joined: Wed 23 Mar 2011 09:53

Package problem when there is a dot in the tns-name

Post by hclaassens » Wed 13 Jul 2016 11:36

We have the following line in our tnsnames.ora:
OPMSLPSR, OPMSLPSR.world=
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ourhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=opmslpsr)))


We're using driver version 6.7.11 of May 17, 2016, Delphi XE2 on Windows 7. Oracle is 11.0.2.4.

The problem is that we get a parameter not found error when trying to access a stored procedure in a Oracle package, when we use OPMSLPSR.world as a database name.

Any solution for this?

When we change the database name to OPMSLPSR, it works fine.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Package problem when there is a dot in the tns-name

Post by AlexP » Wed 13 Jul 2016 12:15

Hello,

Please provide the full error message (or a screenshot), describe the steps to reproduce the error, and provide SQL queries to create required database objects.

hclaassens
Posts: 14
Joined: Wed 23 Mar 2011 09:53

Re: Package problem when there is a dot in the tns-name

Post by hclaassens » Wed 13 Jul 2016 12:30

The error message is:
Parameter P_STUURSTRING is not found

The following Delphi code is executed:
procedure ActivateDatabaseAccess( aSQLConnection : TcpSQLConnection );
var
rRolPaswoord : String;
rProc : TcpCentricStoredProc;
begin
Assert( IsOracleAlias( aSQLConnection ) );

rProc := TcpCentricStoredProc.Create( nil );
with rProc do
try
SQLConnection := aSQLConnection;
GetMetaData := True;

PackageName := 'PIM_PAUTORISATIE_DB';
StoredProcName := 'ACTIVEER_TOEGANG_DATABASE';
MaxBlobSize := -1;

// RefreshParams( rProc );
ParamByName( 'P_STUURSTRING' ).Value := Decrypt( AutorisationPackageAccess );

try
ExecProc;
except
RaiseException( txtErrorAutorisatiePackage );
end;
rRolPaswoord := ParamByName( 'P_TOEGANG' ).AsString;
finally
Free;
end;

SetUserRole( aSQLConnection, drDBToegang, rRolPaswoord );
end;


The line
ParamByName( 'P_STUURSTRING' ).Value := Decrypt( AutorisationPackageAccess );
is generating the error.

I hope this gives enough information
Regards,
Hans

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Package problem when there is a dot in the tns-name

Post by AlexP » Thu 14 Jul 2016 12:27

Please also provide SQL queries to create required database objects.

hclaassens
Posts: 14
Joined: Wed 23 Mar 2011 09:53

Re: Package problem when there is a dot in the tns-name

Post by hclaassens » Thu 14 Jul 2016 12:42

The package is created with the following script:

create or replace PACKAGE PIM_PAUTORISATIE_DB
IS
--** ========================================================================
--**
--** Library: DATABASE
--** Naam package: PIM_PAUTORISATIE_DB (spec)
--**
--** Doel: Gebruikers rechten geven om Pims@all database te
--** kunnen benaderen
--**
--** Beschrijving:
--** - Procedures:
--** PROCEDURE ACTIVEER_TOEGANG_DATABASE
--** ( p_StuurString in VARCHAR2
--** ,p_Toegang out VARCHAR2
--** );
--**
--** PROCEDURE STANDAARD_ROL_TOEGANG
--** ( p_StuurString in VARCHAR2
--** ,p_RolNaam in VARCHAR2
--** ,p_Toegang out VARCHAR2
--** );
--**
--**
--** Opmerking: Zie ook PIM_PAUTORISATIE_DB (body).
--***
--*** Door: xxxx
--*** Datum: xx-xx-xxxx
--***
--*** =======================================================================

--
-- Benaderen Pims4U database mogelijk maken
--
PROCEDURE ACTIVEER_TOEGANG_DATABASE
( p_StuurString in VARCHAR2
,p_Toegang out VARCHAR2
);

PROCEDURE STANDAARD_ROL_TOEGANG
( p_StuurString in VARCHAR2
,p_RolNaam in VARCHAR2
,p_Toegang out VARCHAR2
);

END PIM_PAUTORISATIE_DB;
/
show err


create or replace PACKAGE BODY PIM_PAUTORISATIE_DB
IS
--** ========================================================================
--**
--** Library: DATABASE
--** Naam package: PIM_PAUTORISATIE_DB (body)
--**
--** Doel: Gebruikers rechten geven om Pims@all database te
--** kunnen benaderen
--**
--** Opmerking: Zie ook PIM_PAUTORISATIE_DB (spec).
--**
--***
--*** Door: xxxx
--*** Datum: xx-xx-xxxx
--***
--*** =======================================================================

-- Constante
autorisatie_paswoord CONSTANT VARCHAR2(20) := 'Something%';
rol_dbtoegang_paswoord CONSTANT VARCHAR2(20) := 'XXXX';
rol_raadplegen_paswoord CONSTANT VARCHAR2(20) := 'XXXX';
rol_wijzigen_paswoord CONSTANT VARCHAR2(20) := 'XXXX';
rol_rapportage_paswoord CONSTANT VARCHAR2(20) := 'XXXX';

-- Globale interne variabelen voor exceptions
gv_orakode number(5) := 0;
gv_oramsg varchar2(200) := '--';


PROCEDURE ACTIVEER_TOEGANG_DATABASE(
p_StuurString in VARCHAR2
,p_Toegang out VARCHAR2
)
IS
verkeerde_stuurstring EXCEPTION;
BEGIN
p_Toegang := '';

IF p_StuurString != autorisatie_paswoord THEN
RAISE verkeerde_stuurstring;
END IF;

p_Toegang := rol_dbtoegang_paswoord;

EXCEPTION
WHEN verkeerde_stuurstring THEN
raise_application_error( -20001,
'Verkeerde paswoord voor autorisatie package opgegeven' );
WHEN OTHERS THEN
gv_orakode := sqlcode;
gv_oramsg := sqlerrm;
raise_application_error( -20000, gv_oramsg );
END ACTIVEER_TOEGANG_DATABASE;

PROCEDURE STANDAARD_ROL_TOEGANG(
p_StuurString in VARCHAR2
,p_RolNaam in VARCHAR2
,p_Toegang out VARCHAR2
)
IS
verkeerde_stuurstring EXCEPTION;
rol VARCHAR2( 100 );
BEGIN
p_Toegang := '';

IF p_StuurString != autorisatie_paswoord THEN
RAISE verkeerde_stuurstring;
END IF;

rol := UPPER( p_RolNaam );
IF rol = 'RPIM_RAADPLEGEN' THEN
p_Toegang := rol_raadplegen_paswoord;
END IF;
IF rol = 'RPIM_WIJZIGEN' THEN
p_Toegang := rol_wijzigen_paswoord;
END IF;
IF rol = 'RPIM_RAPPORTAGE' THEN
p_Toegang := rol_rapportage_paswoord;
END IF;

EXCEPTION
WHEN verkeerde_stuurstring THEN
raise_application_error( -20001,
'Verkeerde paswoord voor autorisatie package opgegeven' );
WHEN OTHERS THEN
gv_orakode := sqlcode;
gv_oramsg := sqlerrm;
raise_application_error( -20000, gv_oramsg );
END STANDAARD_ROL_TOEGANG;

END PIM_PAUTORISATIE_DB;

/
show err



AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Package problem when there is a dot in the tns-name

Post by AlexP » Fri 15 Jul 2016 10:47

Hello,

To solve the problem, you should set the UseQuoteChar parameter to Truie.

hclaassens
Posts: 14
Joined: Wed 23 Mar 2011 09:53

Re: Package problem when there is a dot in the tns-name

Post by hclaassens » Fri 15 Jul 2016 11:34

Thanks Alex, that's the solution

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Package problem when there is a dot in the tns-name

Post by AlexP » Fri 15 Jul 2016 12:09

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.

Post Reply