Do not call SYS.DBMS_SESSION.SET_NLS with an instance of the [Archive.is] TOraStoredProc Class, as under the hood, it will translate the call to this:
Class:Ora.TOraStoredProc;Component:sprSetNls;Flag:tfQPrepare,Text:Prepare: begin
SYS.DBMS_SESSION.SET_NLS(:PARAM, :VALUE);
end;
:PARAM(VARCHAR[22],IN)='NLS_NUMERIC_CHARACTERS'
:VALUE(VARCHAR[4],IN)=''.,''
The above is a translation of the bold portions in this call (note it contains the an instantiation of an [Archive.is] TOraSession Class as you need one; examples further down assume this session instance to exist):
var
MainOraSession: TOraSession;
DbmsSessionSetNlsOraStoredProc: TOraStoredProc;
begin
MainOraSession := TOraSession.Create(Self);
try
MainOraSession.Name := 'MainOraSession';
MainOraSession.Username := 'FOO';
MainOraSession.Server := 'BAR';
MainOraSession.LoginPrompt := False;
MainOraSession.Options.UseOCI7 := True;
MainOraSession.Open();
DbmsSessionSetNlsOraStoredProc := TOraStoredProc.Create(Self);
try
DbmsSessionSetNlsOraStoredProc.Name := 'DbmsSessionSetNlsOraStoredProc';
DbmsSessionSetNlsOraStoredProc.StoredProcName := 'SYS.DBMS_SESSION.SET_NLS';
DbmsSessionSetNlsOraStoredProc.Session := MainOraSession;
DbmsSessionSetNlsOraStoredProc.Debug := True;
with DbmsSessionSetNlsOraStoredProc.ParamData.Add do
begin
DataType := ftString;
Name := 'PARAM';
ParamType := ptInput;
Value := nil;
end;
with DbmsSessionSetNlsOraStoredProc.ParamData.Add do
begin
DataType := ftString;
Name := 'VALUE';
ParamType := ptInput;
Value := nil;
end;
DbmsSessionSetNlsOraStoredProc.ParamByName('PARAM').AsString := sParam;
DbmsSessionSetNlsOraStoredProc.ParamByName('VALUE').AsString := sValue;
DbmsSessionSetNlsOraStoredProc.Prepare();
DbmsSessionSetNlsOraStoredProc.ExecProc();
finally
DbmsSessionSetNlsOraStoredProc.Free();
end;
finally
MainOraSession();
end;
end;
It will result in an Oracle error during the Prepare of the statement:
ORA-06550: line 2, column 36:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
( - + case mod new not null
continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date pipe
In stead, take your TOraPackage object and make a call like this:
var
DbmsSessionOraPackage: TOraPackage;
begin
DbmsSessionOraPackage := TOraPackage.Create(Self);
try
DbmsSessionOraPackage.Name := 'DbmsSessionOraPackage';
DbmsSessionOraPackage.Debug := True;
DbmsSessionOraPackage.Session := dbSession;
DbmsSessionOraPackage.PackageName := 'SYS.DBMS_SESSION';
DbmsSessionOraPackage.ExecProcEx('SET_NLS', ['PARAM', 'NLS_NUMERIC_CHARACTERS', 'VALUE', '''.,''']);
finally
DbmsSessionOraPackage.Free();
end;
end;
This then results in this in the SQL monitoring (note quoting quotes is different in SQL than Delphi):
Class:Ora.TOraSQL;Component:;Flag:tfQExecute,Text:begin
SYS.DBMS_SESSION.SET_NLS(:PARAM, :VALUE);
end;
:PARAM(VARCHAR[22],IN)='NLS_NUMERIC_CHARACTERS'
:VALUE(VARCHAR[4],IN)=''.,''
instead of this:
Class:Ora.TOraStoredProc;Component:sprSetNls;Flag:tfQPrepare,Text:Prepare: begin
SYS.DBMS_SESSION.SET_NLS(:PARAM, :VALUE);
end;
:PARAM(VARCHAR[22],IN)='NLS_NUMERIC_CHARACTERS'
:VALUE(VARCHAR[4],IN)=''.,''
I am still a sort of baffled why this is a problem. But using the TOraPackage works.
One thing to remember is that an TOraSession instance does not allow you to get to the underlying TOCIConnection instance, which does allow setting NLS information directly; see for instance the old code at [WayBack] OraClasses.pas in xinhaining-dianjianyiqi-tongxunchengxu | source code search engine.
This is because the underlying connection can be both OCI and Direct depending on the TOraSession.Options.Direct value: [WayBack] About Connection.Ping method when Direct Mode – Devart Forums.
Other calls on SYS.DBMS_SESSION succeed
The odd thing is that single-parameter calls on SYS.DBMS_SESSION.SET_ROLE (which can be tricky, see [WayBack] Introducing Database Security for Application Developers) work fine, so no alternative (like a plain [WayBack] SET ROLE) is needed:
var
DbmsSessionSetRoleOraStoredProc: TOraStoredProc;
begin
DbmsSessionSetRoleOraStoredProc := TOraStoredProc.Create(Self);
try
DbmsSessionSetRoleOraStoredProc.Name := 'DbmsSessionSetRoleOraStoredProc';
DbmsSessionSetRoleOraStoredProc.StoredProcName := 'SYS.DBMS_SESSION.SET_ROLE';
DbmsSessionSetRoleOraStoredProc.Session := dbSession;
with DbmsSessionSetRoleOraStoredProc.ParamData.Add do begin
DataType := ftString;
Name := 'ROLE_CMD';
ParamType := ptInput;
Value := 'EXAMPLE';
end;
DbmsSessionSetRoleOraStoredProc.ParamByName('ROLE_CMD').AsString := 'EXAMPLEROLE';
DbmsSessionSetRoleOraStoredProc.Prepare;
DbmsSessionSetRoleOraStoredProc.ExecProc;
finally
DbmsSessionSetRoleOraStoredProc.Free();
end;
end;
results in this log:
Class:Ora.TOraStoredProc;Component:StrdPSetRole;Flag:tfQExecute,Text:begin
SYS.DBMS_SESSION.SET_ROLE(:ROLE_CMD);
end;
:ROLE_CMD(VARCHAR[10],IN)='EXAMPLEROLE'
Similar for SYS.DBMS_APPLICATION_INFO
Calling anything on DBMS_APPLICATION_INFO gives you an exception.
On the .NET side of DevArt, you can use
–jeroen
The Wiert Corner - irregular stream of stuff 