 | Mike Verhagen on geerlingguy/my-backup-plan: Ho… |
 | Attila Kovacs on docs.embarcadero.com unreachab… |
 | David Blue on MacOS: converting a man page t… |
 | jpluimers on How do I pretty-print JSON in… |
| jpluimers on Delphi 10.2 Tokyo Godzilla ISO… |
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 »
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):
select a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by bselect a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b| 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.
select a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by bselect a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b| 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:
Posted in Conventions, Database Development, Development, OracleDB, PL/SQL, SQL, SQL Server, T-SQL | Leave a Comment »
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:
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 »
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 »
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
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.
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'
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 »
The Wiert Corner - irregular stream of stuff 