The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,975 other subscribers

Archive for the ‘OracleDB’ Category

Securely Connecting to Autonomous DB Without a Wallet (Using TLS)

Posted by jpluimers on 2022/12/20

[Wayback/Archive] Securely Connecting to Autonomous DB Without a Wallet (Using TLS)

It is about moving from mTLS to TLS on Oracle Autonomous DB and at the same time IP-whitelisting the client IP addresses.

[Archive] Chris Bensen on Twitter: “This is extremely useful so I figured I’d share in the hopes it helps someone else “

–jeroen

Posted in Cloud, Cloud Development, Database Development, Development, Infrastructure, OracleDB, Software Development | Leave a Comment »

SQL comma bullet point formatting: because AS is optional

Posted by jpluimers on 2022/11/03

Do you see the error below?

(note: OCR via [Wayback/Archive.is] Best Free OCR API, Online OCR, Searchable PDF – Fresh 2021 OCR Software)

SELECT
  license date
  expiration_date,
  renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'
expiration_date renewal_ due date
1 2016-04-08 09:50:00 [NULL]
2 2013-11-14 11:15:00 [NULL]
3 2014-11-20 14:51:00 [NULL]
4 2017-07-21 16:00:00 [NULL]
5 2018-12-17 14:37:46 2020-12-17 14:37:46

All of the expiration_date columns have values, which is contrary to the WHERE clause. This is because the table itself contains an expiration_date column, and the SELECT part aliases license_date into expiration_date.

The result is that you see rows that have expiration_date being NULL, but license_date having a value.

So I totally agree with [Archive.is] Mathias Magnusson on Twitter: “That is one reason I’m a form believer in comma bullet point in my SQL. Problems like that has bit me far too often due to som issue with commas.… “

This is how the SQL should have looked:

SELECT
  license date
, expiration_date
, renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'

Yes indeed: an alias of a column without the AS keyword is allowed in quite a few SQL dialects (they differ even more widely in SQL extensions like SQL/PSM, T-SQL, PL/SQL, SQL_PL, or ABAB).

Aliases are for output, cannot be used in WHERE (but can in ORDER BY).

You can see what happens (and how hard this can become on one line) with these two dbfiddle queries running on Microsoft SQL Server 2019 dialect (though it works similar in other dialects):

  1. select a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    I saved it as [Wayback/Archive.is] SQL Server 2019 | dbfiddle: select a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    Resulting in

    b c
    7 6
    8 4
    9 2

    It shows only the column names a and b, but note the table itself is aliased to t above as well.

  2. select a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    I saved it as [Wayback/Archive.is] SQL Server 2019 | dbfiddle: select a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    Resulting in

    a b c
    9 1 2
    8 3 4
    7 5 6

    It shows only the column names a, b and c, but note the table itself is aliased to t above as well.

I really wish various SQL dialects would force the SQL syntax to be (together with a hint that the alias would overwrite an existing field):

SELECT
  license date AS expiration_date
, renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'

That is not going to happen, so the second best is to wish for tooling to hint/warn about it, and provide better syntax highlighting for it. That seems work in progress by now:

Read the rest of this entry »

Posted in Conventions, Database Development, Development, OracleDB, PL/SQL, SQL, SQL Server, T-SQL | Leave a Comment »

Database fiddle sites

Posted by jpluimers on 2022/10/27

I knew there was JSFiddle for live playing around with JavaScript and more in your browser, so I wondered if there was a similar site for databases and SQL queries.

There are, so here are a few database fiddle sites: SQL playgrounds where you can live play with SQL queries (sometimes even without an underlying example database).

All via [Wayback/Archive.is] database fiddle – Google Search:

Read the rest of this entry »

Posted in Conference Topics, Conferences, Database Development, DB2, Development, Event, Firebird, JavaScript/ECMAScript, JSFiddle, MariaDB, MySQL, OracleDB, PL/SQL, PostgreSQL, Scripting, Software Development, SQL, SQL Server, SQLite, T-SQL | Leave a Comment »

Case sensitivity for SQL identifiers · ontop/ontop Wiki · GitHub

Posted by jpluimers on 2021/07/08

For my link archive: [WayBack] Case sensitivity for SQL identifiers · ontop/ontop Wiki · GitHub:

  • Oracle and H2 changes unquoted identifiers to uppercase.
    Although technically possible, Oracle explicitly recommends to not use lowercase identifers. We do not support H2 with the setting DATABASE_TO_UPPER=FALSE, if this setting is enabled all queries with names and tables in lowercase must be quoted.
  • DB2 Names are not case sensitive.
    For example, the table names CUSTOMER and Customer are the same, but object names are converted to uppercase when they are entered. If a name is enclosed in quotation marks, the name becomes case sensitive. The schema name is case-sensitive, and must be specified in uppercase characters.
  • Postgres changes unquoted identifiers (both columns and alias names) to lowercase.
  • Mysql does not change the case of unquoted tables and schemas.
    It changes in lowercase the unquoted columns. Mysql tables are stored as files in the operating system the server runs on. This means that database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix or Linux. The backtick ` is used for enclosing identifiers such as table and column names.
  • Mssqlserver All connection string property names are case-insensitive.
    For example, Password is the same as password. Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database. For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation. Default SQL Server is not case sensitive. SELECT * FROM SomeTable is the same as SeLeCT * frOM soMetaBLe. Delimited identifiers are enclosed in double quotation marks (“) or brackets ([]). Identifiers that comply with the rules for the format of identifiers may or may not be delimited.

–jeroen

Posted in Database Development, DB2, Development, MySQL, OracleDB, PostgreSQL, SQL Server | Leave a Comment »

ODAC: calling SYS.DBMS_SESSION.SET_NLS failing with ORA-06550/PLS-00103

Posted by jpluimers on 2021/06/16

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

Posted in Database Development, Delphi, Development, OracleDB, Software Development | Leave a Comment »

 
%d bloggers like this: