The Wiert Corner – irregular stream of stuff

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

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

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

    Join 1,860 other subscribers

Archive for the ‘OracleDB’ Category

« Previous Entries
Next Entries »

Oracle Instant Client – no chocolatey package for automatic install on Windows (yet?)

Posted by jpluimers on 2021/05/20

I tried finding a chocolatey package for the [WayBack] Oracle Instant Client – Free tools and libraries for connecting to Oracle Database.

The [WayBack] Instant Client editions support light-weight installations of the Oracle database client.

I was especially interested in automating the install of the basic or basic basic light varieties:

Instant Client Package

Description

References

Basic

All files required to run OCI, OCCI, and JDBC-OCI applications for Oracle Database

OCI
OCCI
JDBC-OCI

Basic Light

Smaller version of the Basic package, with only English error messages and Unicode, ASCII, and Western European character set support

OCI
OCCI
JDBC-OCI

Having this is a prerequisite of many DAC layers, including ODBC, although for the latter, you also need this one:

Instant Client Package

Description

References

ODBC

Additional libraries providing ODBC

ODBC

Since there is no chocolatey maintained package for any of these, these are the steps to download them:

  1. Ensure you have a Oracle account
  2. Sign in on www.oracle.com/webapps/redirect/signon?nexturl=https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads
  3. Accept cookies if asked for
  4. Click on the “Downloads” button to go to [WayBack] Oracle Instant Client Downloads
  5. Click on either [WayBack] Instant Client for Microsoft Windows (32-bit) (note that [WayBack] Instant Client for Microsoft Windows (x64) is specific for Win64 applications; many are 32-bit only, especially end-user ones, for instance many supporting Microsoft Office (including Outlook addins, or document storage systems)
  6. Click on the “Accept License Agreement” radio button
  7. Select the version you need.
  8. Download and install the required files.
  9. Unzip and add the directories to the PATH

TNSPING

Note that these downloads not include TNSPING; for some reason, Oracle only adds that to the fat client. So you need to use alternatives:

You can also use sqlplus with sscripting, but that is much harder on Windows than on Linux:

Note there are very distinctive differences between tnsping and sqlplus, see How to do tnsping? | Oracle Community  which I will quote large parts of soon.

For sqlplus: it works with TNSNAMES.ORA, but you can also do without it.

For all the other tnsping substitutes: ensure your TNS_ADMIN points to a directory with a correct TNSNAMES.ORA file.

What I usually do is this:

  1. Create directory a directory ORA under %LOCALAPPDATA%
  2. Store TNSNAMES.ORA inside %LOCALAPPDATA%\ORA
  3. Point TNS_ADMIN environment variable to %LOCALAPPDATA%\ORA
  4. Unzip any of the instantclient-basiclite-nt-*.zip into %LOCALAPPDATA%\ORA\InstantClient
  5. Unzip any of the instantclient-sqlplus-nt-*.zip into %LOCALAPPDATA%\ORA\SqlPlus
  6. Add %LOCALAPPDATA%\ORA\InstantClient;%LOCALAPPDATA%\ORA\SqlPlus to the user PATH

Related

My blog post Chocolatey: installing Oracle SQL Developer and updating the chocolatey package (which works without the instantclient).

[WayBack] Install Oracle Instant Client on Linux and Windows | HelloDog:  Install Oracle instantclient basic and instantclient sqlplus on Linux and Windows (and some tnsping examples).

[Archive.is] oracle – How to set tnsnames.ora location for SQL Developer in Windows 10 – Stack Overflow

–jeroen

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

Chocolatey: installing Oracle SQL Developer and updating the chocolatey package

Posted by jpluimers on 2021/05/13

Sometimes an install is not just as simple as C:\>choco install --yes oracle-sql-developer.

Edit 20210514:

Note that most of the below pain will be moot in the future as per [Archive.is] Jeff Smith 🍻 on Twitter: “we’re working on removing the SSO requirement, it’s already done for @oraclesqlcl – see here … “ referring to [Wayback] SQLcl now under the Oracle Free Use Terms and Conditions license | Oracle Database Insider Blog

SQLcl, the modern command-line interface for the Oracle Database, can now be downloaded directly from the web without any click-through license agreement.

It means the Oracle acount restriction will be lifted, and downloads will be a lot simpler.

I started with the below failing command, tried a lot of things, then finally almost gave up: Oracle stuff does not want to be automated, which means I should try to less of their stuff.

First of all you need an Oracle account (I dislike companies doing that for free product installs; I’m looking at Embarcadero too) by going to profile.oracle.com:

[WayBack] Chocolatey Gallery | Oracle SQL Developer 18.4.0 (also: gist.github.com/search?l=XML&q=oracle-sql-developer)

Notes

  • This version supports both 32bit and 64bit and subsequently does not have a JDK bundled with it. It has a
    dependency on the jdk8 package to meet the application’s JDK requirement.
  • An Oracle account is required to download this package. See the “Package Parameters” section below for
    details on how to provide your Oracle credentials to the installer. If you don’t have an existing account, you can
    create one for free here: https://profile.oracle.com/myprofile/account/create-account.jspx

Package Parameters

The following package parameters are required:

* /Username: – Oracle username
* /Password: – Oracle password

(e.g. choco install oracle-sql-developer --params "'/Username:MyUsername /Password:MyPassword'")

To have choco remember parameters on upgrade, be sure to set choco feature enable -n=useRememberedArgumentsForUpgrades.

Then the installation failed fail again: ERROR: The response content cannot be parsed because the Internet Explorer engine is not available, or Internet Explorer's first-launch configuration is not complete. Specify the UseBasicParsing parameter and try again.

The trick is to RUN IEXPLORE.EXE AS ADMINISTRATOR ONCE BEFORE INSTALLING FROM CHOCOLATEY. Who would believe that.

The reason is that the package uses Invoke-WebRequest which requires Internet Explorer and PowerShell 3. Chocolatey packages however need to be able to run on just PowerShell 2 without Invoke-WebRequest.

Maybe using cURL can remedy that; adding a dependency to is is possible, as cURL can be installed via chocolatey: [WayBack] How to Install cURL on Windows – I Don’t Know, Read The Manual. Another alternative might be [WayBack] Replace Invoke-RestMethod in PowerShell 2.0 to use [WayBack] WebRequest Class (System.Net) | Microsoft Docs.

Read the rest of this entry »

Posted in CertUtil, Chocolatey, CommandLine, Database Development, Development, DVCS - Distributed Version Control, git, Hashing, OracleDB, Power User, PowerShell, Security, SHA, SHA-1, Software Development, Source Code Management, Windows, XML, XML/XSD | Leave a Comment »

Public database servers

Posted by jpluimers on 2019/10/30

I could not find any vendors/architectures have public database servers.

So there is no good way to go beyond SQLFiddle (of which I wrote before in SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions and David Rodriguez: a few nice posts on SQL (via: Google+)), that does not provide database access, but allows you to fire SQL statements onto these architectures:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2017

I get the thing (it is very hard to secure an “over the internet” connection to a database server; do NOT do this: [WayBack] connectivity – Connect to SQL Server over Internet – Database Administrators Stack Exchange), so the alternative is to run locally.

If you run locally, there are plenty of example/demo database, like:

–jeroen

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

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

Posted by jpluimers on 2019/08/29

Via [WayBack] SQL select only rows with max value on a column, I bumped into http://sqlfiddle.com/#!9/a6c585/1:

Application for testing and sharing SQL queries.

Source: [WayBack] SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

It is a cool site, currently supporting these SQL back-ends:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2014

You can host it yourself using [WayBack] GitHub – zzzprojects/sqlfiddle2: New version of SQL Fiddle based on OpenIDM (in the past it was [WayBack] GitHub – zzzprojects/sqlfiddle)

Other resources for learning and playing around with SQL:

–jeroen

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

Generating a million sequential numbers on the fly in a Firebird query – some solutions and speed measurements

Posted by jpluimers on 2018/07/19

The testing was done with Firebird 2.5.x x86 on Windows 7 x64.

Where other relational database platforms have plenty of opportunities to generate sequences (see for instance the below links on Oracle and SQL Server), with Firebird you can use a WITH RECURSIVE query construct that normally is being used to manage tree structures ([WayBack] PkLab – Firebird: Tree data mangement with recursive CTE).

However, that uses query stack which has a depth limit of 1024 levels. When you reach the limit, Firebird gives you an error like this:

with 
  recursive 
  sequence(n) as (        
    -- When you select more than 1024 values, this error occurs:
    -- Error while fetching data:  Too many concurrent executions of the same request    
    select 0 -- start
    from rdb$database
    union all
    select sequence.n + 1
    from sequence
    where sequence.n < 1023 -- finish
  )
select sequence.n 
from sequence
--where sequence.n in (24, 38) 
order by sequence.n

It however is a pretty quick and CU bound solution: on my system ([WayBack] AMD A8-7600 @ 3.1 Ghz), it runs 1000 records within ~0.1 seconds.

In such a short time, it’s hard to see how the speed is bound, so I wanted to go for some orders of magnitude more. In ~0.1 seconds, the processor executes about 0.3 * 10^9 cycles generating 1000 numbers which is ~ 300-thousand cycles per number. That sounds like a lot of cycles for so few numbers. Would this become a better ratio for more numbers?

Read the rest of this entry »

Posted in Database Development, Development, Firebird, IKEA hacks, OracleDB, SQL, SQL Server | Leave a Comment »

Visual Representation of SQL Joins – CodeProject

Posted by jpluimers on 2017/08/02

I thought I posted a reference to this a long time ago, but didn’t.

It’s one of the things I show when explaining joins to people. Sometimes I need it myself too (:

The article explains these in greater detail:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • OUTER JOIN
  • LEFT JOIN EXCLUDING INNER JOIN
  • RIGHT JOIN EXCLUDING INNER JOIN
  • OUTER JOIN EXCLUDING INNER JOIN

Note:

  • the opposite of INNER JOIN is not OUTER JOIN. It’s OUTERJOIN EXCLUDING INNER JOIN
  • the opposite of OUTER JOIN is empty set.

But the diagram is usually speaks for itself.

–jeroen

Source: Visual Representation of SQL Joins – CodeProject

Read the rest of this entry »

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

Common Table Expressions: no nesting, but consecutively usage – via Stack Overflow

Posted by jpluimers on 2016/09/28

Common table expressions are awesome. They work in at least Oracle and SQL Server.

You cannot nest them, but you can use them consecutively. Thanks spender for explaining that:

WITH
x AS
(
SELECT * FROM MyTable
),
y AS
(
SELECT * FROM x
)
SELECT * FROM y

–jeroen

via: sql – Can you create nested WITH clauses for Common Table Expressions? – Stack Overflow.

Posted in Database Development, Development, OracleDB, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »

Oracle Cross database queries: you need a “database link” for that

Posted by jpluimers on 2016/09/27

I recently learned that you can do cross database queries in Oracle using database links.

You need to prefix your objects with the right schema/owner (for instance dbo.) and suffix with an @ sign followed by the database link name.

This query finds all configured database links:

select * from all_db_links;

–jeroen

via: Ask Tom “database link”.

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

sql – Oracle to_date() to parse input to datetime values – via Stack Overflow

Posted by jpluimers on 2016/09/07

The Oracle to_date() function has saved me numerous times parsing date time strings into datetime values like this:

to_date('29/07/2015 13:18:00', 'DD/MM/YYYY HH24:MI:SS')

One gotcha is that when you query back the resulting date-time values, they might be formatted differently because of NLS_DATE_FORMAT settings

In addition to the query for ‘nls_date_format’ mentioned in sql – Oracle to_date() incorrect output – Stack Overflow, I usually use this one:

select v.*
from v$parameter v
where name like 'nls_%'
order by lower(v.name)
;

–jeroen

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

ORACLE-BASE – DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1

Posted by jpluimers on 2016/08/23

To work around this error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ensure your DDL does wait a bit longer than the default of NOWAIT seconds:

alter session set ddl_lock_timeout=30
;
alter table TABLE_NAME
modify constraint CONSTRAINT_NAME enable
;
commit
;

via ORACLE-BASE – DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1.

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

« Previous Entries
Next Entries »