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,386 other followers

Archive for the ‘OracleDB’ Category

List views in Oracle database – Oracle Query Toolbox

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 »

Some of the Oracle database errors I encountered

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:

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 denied

There 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 Interface

a

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 »

How to do tnsping? | Oracle Community

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:

EdStevensGrand Titan

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@larry

tns 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 »

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 Chocolatey, CommandLine, Database Development, Development, DVCS - Distributed Version Control, git, OracleDB, Power User, PowerShell, Software Development, Source Code Management, Windows, XML, XML/XSD | Leave a Comment »

 
%d bloggers like this: