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
TOraSessioninstance does not allow you to get to the underlyingTOCIConnectioninstance, 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
OCIandDirectdepending on theTOraSession.Options.Directvalue: [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 