 | Lars Fosdal on Security alarm provider Woonve… |
 | Thomas Mueller on Question got closed in May 202… |
 | Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
Posted by jpluimers on 2025/07/02
Since I keep forgetting which DBMS uses which method to select just a plain value without a table. I always remember it as my search phrase [Wayback/Archive] SELECT from DUAL, but actually better titled like the question below:
[Wayback/Archive] sql – SELECT from nothing? – Stack Overflow
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MariaDB, MySQL, OracleDB, PL/SQL, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »
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 »
Posted by jpluimers on 2021/06/16
I got this when taking over maintenance of some legacy code that called a stored procedure in a :
--------------------------- Debugger Exception Notification --------------------------- Project LegacyProject.exe raised exception class Exception with message 'Need Oracle 8 Call Interface'. --------------------------- Break Continue Help ---------------------------
The problem with finding more information about this, is that there are virtual no hits on Oracle 8 Call Interface, OCI 8 or Oracle Call Interface 8.
A good document is [WayBack] OCI: Introduction and Upgrading (found via [Archive.is] “OCI 7” – Google Search), but it never mentions 8, only these terms occur:
The error happens on the first instance of at least an [Archive.is] TOraStoredProc Class or [Archive.is] TOraPackage Class that prepares or executes a stored procedure.
One problem is that this is an instance of a plain [WayBack] Exception Class , not a [WayBack] EDatabaseError Class or [Archive.is] EDAError Class.
What it means is that the UseOCI7 sub-property was set to True in your TOraSession property Options:
- [Archive.is] TOraSession Class
- [Archive.is] TOraSession.Options Property
- [Archive.is] TOraSessionOptions.UseOCI7 Property
var MainOraSession: TOraSession; begin MainOraSession := TOraSession.Create(Self); MainOraSession.Name := 'MainOraSession'; MainOraSession.Username := 'ExammpleUser'; MainOraSession.Server := 'ExampleTnsName'; MainOraSession.LoginPrompt := False; MainOraSession.Options.UseOCI7 := True;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 error at [WayBack] Not connected to oracle error – Devart Forums and old code at [WayBack] OraClasses.pas … | source code search engine and [WayBack] Ora.pas in … | source code search engine; UniDac is relatively similar [WayBack] UniDAC v3.7 …:OraClassesUni.pas (another explanation is in [WayBack] Setting the Client Info for the TOraAlerter extra session – Devart Forums).This is because the underlying connection can be both
OCIandDirectdepending on theTOraSession.Options.Directvalue: [WayBack] About Connection.Ping method when Direct Mode – Devart Forums.
After the first occurrence, you will not get this error again, you get way more spurious errors, especially on stored procedures in packages, where the stored procedure has 2 or more parameters. There you get errors like this one for TOraStoredProc:
ORA-06550: line 2, column 36: PLS-00103: Encountered the symbol ":" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set speci.
When logging using a [Archive.is] TOraSQLMonitor Class instance (use the [Archive.is] TCustomDASQLMonitor.OnSQL Event with the [Archive.is] TOnSQLEvent Procedure Reference signature) you can get the underlying information of the TOraStoredProc (see the example code below that raises 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)=''.,''
It fails equally when doing any DBMS_APPLICATION_INFO calls, for which the .NET version of ODAC has a special [WayBack] SetDbmsApplicationInfo Method in the [WayBack] OracleConnection Class. Too bad the Delphi version omits that.
When using TOraPackage, you can get different errors, like for instance this EDatabaseError:
Exception class EDatabaseError with message 'Parameter 'VALUE' not found'.
It basically means that through OCI, the parameter list could not be obtained, so the error is caught on the Delphi side instead of the Oracle side.
Using a TOraPackage is easier at design time, as it allows you to select the [Archive.is] TOraPackage.PackageName Property property using the underlying TOraSession. Somehow a TOraStoredProc does not allow you to select the [Archive.is] TOraStoredProc.StoredProcName Property at design time.
Related:
–jeroen
Posted in Database Development, Delphi, Development, OracleDB, Software Development | Leave a Comment »
Posted by jpluimers on 2021/06/03
[WayBack] List views in Oracle database – Oracle Query Toolbox
A. All views accessible to the current user
select owner as schema_name, view_name from sys.all_views order by owner, view_name;B. If you have privilege on dba_views
select owner as schema_name, view_name from sys.dba_views order by owner, view_name;
(more details in the above post)
–jeroen
Posted in Database Development, Development, OracleDB | Leave a Comment »
Posted by jpluimers on 2021/05/26
For my future self:
'Invalid Oracle Home: 'Not sure what exactly fixed this, but likely either of these:
TNS_ADMIN pointing to a directory having a [WayBack] TNSNAMES.ORAPATH.It can also have to do with Win32 versus Win64.
Related:
Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
This was using tnsping (actually the alternative McTnsPing); cause was the DBMS server VM being down..
ORA-12545: Connect failed because target host or object does not exist
It meant the VPN connection to the site having the Oracle server was down.
Related: [WayBack] ORA-12545 – Oracle FAQ
ORA-01017: invalid username/password; logon deniedThere was confusion on which test credentials to use.
Related (as there are some bugs, case sensitivity issues, and confusion around special characters like $):
Need Oracle 8 Call Interfacea
ORA-06550: line 2, column 36:Actual error:
ORA-06550: line 2, column 36: PLS-00103: Encountered the symbol ":" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set speci.
The cause was a bit of PL/SQL as per [WayBack] ORA-06550 tips.
It was odd, as I was calling
Source: “SYS.DBMS_APPLICATION_INFO.SET_CLIENT_INFO” – Google Search which is part of package “SYS.DBMS_APPLICATION_INFO”.
In the end it all turned out that the underlying library was trying to use OCI 7 in stead of more modern OCI, so the OCI 7 API did not accept the more modern data.
ORA-03135: connection lost contact Process ID: 14513 Session ID: 19 Serial number: 8319
VPN connection died; application did not have re-connect logic to restore the database connection.
ORA-12545: Connect failed because target host or object does not exist
a
ORA-12545: Connect failed because target host or object does not exist
a
ORA-12545: Connect failed because target host or object does not exist
a
ORA-12545: Connect failed because target host or object does not exist
a
ORA-12545: Connect failed because target host or object does not exist
a
–jeroen
Posted in Database Development, Development, OracleDB, Software Development | Leave a Comment »
Posted by jpluimers on 2021/05/25
Since How to do tnsping? | Oracle Community refuses to be archived in the WayBack archive and Archive.is, here a quote of the most important part in the thread:
Boopathy Vasagam wrote:
How to do tnsping?
I am new to databse.
i am using Oracle 10.2 database in windows XP. How to do ‘tnsping’ in that?Others are helping you with how to run a command. In anticipation of what your next question will be ….
Assume you have the following in your tnsnames.ora:
larry = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = curley) ) )Now, when you issue a connect, say like this:
$> sqlplus scott/tiger@larrytns will look in your tnsnames.ora for an entry called ‘larry’. Next, tns sends a request to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).
Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name ‘myhost’ will get resolved to an IP address, either via a local ‘hosts’ file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.
Next, the request arrives at port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, you’ll be connected.
A couple of important points.
First, the listener is a server side only process. It’s entire purpose in life is the receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn’t sustain the connection. One listener, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners. That would be like the telephone company building a separate switchboard for each customer.
Second, the tnsnames.ora file is a client side issue. It’s purpose is for addressess resolution – the tns equivelent of the ‘hosts’ file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.
What can go wrong?
First, there may not be an entry for ‘larry’ in your tnsnames. In that case you get “ORA-12154: TNS:could not resolve the connect identifier specified” No need to go looking for a problem on the host, with the listener, etc. If you can’t place a telephone call because you don’t know the number (can’t find your telephone directory (tnsnames.ora) or can’t find the party you are looking for listed in it (no entry for larry)) you don’t look for problems at the telephone switchboard.
Maybe the entry for larry was found, but myhost couldn’t be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in “ORA-12545: Connect failed because target host or object does not exist”
Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in “ORA-12545: Connect failed because target host or object does not exist”
Maybe the IP was good, but there is no listener running: “ORA-12541: TNS:no listener”
Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. “ORA-12560: TNS:protocol adapter error”
Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn’t know about SERVICE_NAME = curley. “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”
Also, please be aware that tnsping goes no further than to verify there is a listener at the specified host/port. It DOES NOT prove anything regarding the status of the listener’s knowledge of any particular database instance.
–jeroen
Posted in Database Development, Development, OracleDB, Software Development | Leave a Comment »
The Wiert Corner - irregular stream of stuff 