ORA-24324, ORA-24323 and ORA-28547 Workaround

On a Windows notebook, one of our consultants couldn't connect to his local Oracle database. We deleted the database, then when we try to create a new database using Database Configuration Wizard, the wizard would report the following errors: ORA-24324, ORA-24323 and ORA-28547. Then we remembered that we recently installed software for a USB-based 3G modem, so we plugged in the modem and now Oracle works again! Don't know why at the moment, but we guess that the absence of the new network adapter was confusing Oracle.

Later ߪ it seems that all that is required is to ensure that the modem adapter software is loaded into memory when Windows is restarted (it was originally disabled).

Find Rows With Common Property

The table below relates users to groups. Which groups do two specified users have in common?

        ID    USER_ID   GROUP_ID
---------- ---------- ----------
         1          1          1
         2          2          1
         3          1          2
         4          3          2
         5          2          2

Here's a query that works in MS-Access and Oracle Express:

select group_id
from
  user_group where group_id in (select group_id from user_group where user_id = 1) 
  and user_id = 2;

  GROUP_ID
----------
         1
         2

Note: I've used where … in because a user can belong to more than one group. If the sub-query returns more than one row, Oracle Express reports this error:

ORA-01427: single-row subquery returns more than one row

You can look at the problem as one where you're looking for the intersection of two sets. If your DBMS supports it, you can use the intersect set operator. The following query works in Oracle Express:

select group_id from user_group where user_id = 1
intersect
select group_id from user_group where user_id = 2;

  GROUP_ID
----------
         1
         2

Weird NLS_LANG JDBC Connection Error

A colleague got a new computer and when he tried to connect to an Oracle server with a JDBC client, he got this error:

ORA-12705: Cannot access NLS data files or invalid environment specified

After comparing our Java environments, he found that he had user.country = BZ (BZ is the top level domain for Belize). He changed his Regional and Language Options to English (Australia) and then he had no problems connecting to the Oracle server.

I think when JDBC client tries to connect to the Oracle server, the client specifies its locale and when the Oracle server fails to find the required locale files, the server throws the ORA-12705 error message.

Oracle Import and Export LOBs

After playing with PL/SQL, LOBs and writing files on Oracle, I wrote an article explaining how to import and export LOBs.

Review Mastering Oracle SQL 2nd Edition

Mastering Oracle SQL 2nd Edition by Sanjay Mishra and Alan Beaulieu

The goal of the authors is to explain how to write good readable SQL queries in Oracle 10g. The book starts with how to construct SELECT statements to group, filter and format result sets for dates, reports and data analysis. Then it proceeds to cover Oracle-specific queries and functions for hierarchies (data in tree structures), object-oriented types, XML documents, regular expressions and models (spreadsheet-like objects). Where relevant, there are notes about the differences between SQL for Oracle 10, Oracle 9 and the ANSI standard.

As expected from the title, the chapters using declarative programming (i.e. SQL queries) for relational data, hierarchical data and reports are the most comprehensive. Chapters on interfacing Oracle SQL with other technologies such as scripting (Oracle's PL/SQL), object-oriented types, XML and regular expressions, or on optimization, are brief but sufficient to get you started, especially if you have a existing background in those technologies.

This is the 2nd edition, so it's not surprising that the scope of the book is well-defined and that the writing is easy to read and polished. The example data and queries are just complex enough to demonstrate the issues without obscuring the main points. Minor annoyance about Chapter 15, "SQL Best Practices", which does not explain how to use the query analyzer and bind variables.

I was already familiar with basic Oracle SQL but didn't really understand the language; this book blew away many of the fuzzy concepts in my mind and provided me the framework to tackle more complex problems.

SQLDeveloper Paste Multiple Rows

If you're using SQLDeveloper, you can copy and paste multiple rows into a table in one operation, which makes it easy to load small amounts of data into a database for testing.

1. Create your tabular data in Excel.
2. Copy your data into the clipboard.
3. In SQLDeveloper, open the Data tab of the required table.
4. Add as many rows as required by repeatedly pressing the Insert Row button.
5. Paste your data into SQLDeveloper.

If you look in the SQLDeveloper console page, you can see a sequence of INSERT statements followed by a COMMIT statement.

It seems that you can only insert a row if your database server supports transactions, so this trick works for Oracle but not for MySQL.

Windows Cmd Oracle Export

A quickie: I had to dump all the data from an Oracle database and e-mail them to a colleague. Not being familiar with backup and restore for Oracle, I decided to just run exp for each of the tables. To get a list of tables in a schema, I executed this query in DbVisualizer, SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '<schema>', and saved the output to a file, table.txt. Then in Windows cmd, I wrote this script into a file and ran it:

for /f %%i in (table.txt) do exp <connection string> TABLES=%%i FILE=%%i

Oracle Insert Multiple Rows

We wanted a quick hack to initialize a table with a set of rows. The computer we were using had a free version DBVisualizer which only supported running one SQL statement at a time and we didn't want to install SQLPlus or SQL Loader yet. This Wikipedia Insert (SQL) entry shows how to do multi-row insert in various flavours of SQL. The Oracle solution uses a system table called DUAL which seems to act as a placeholder in the SQL statement and return the result of any query.

Software: Oracle SQL Loader NL_DATE_FORMAT

I had to load some data into our Oracle test database server. The data was created in Excel, exported as CSV files, then read into Oracle using sqlldr.exe (SQL Loader). Pretty straightforward, but sqlldr.exe complained about the date format of some entries and some constraints being broken. It turns out that the date field in the CSV files were "dd/mon/YY" while sqlldr.exe expected "DD-MON-YY" by default. Also, Excel was writing two digit year values, so some start dates were occurring after end dates. For example, if the start and end dates were "1-Jan-2006" and "31-Dec-9999", then they were being written as "1-Jan-06" and "31-Dec-99". Solution was to set the NL_DATE_FORMAT environment variable to YYYY/MM/DD and ensure that the date fields in Excel match them.

I had to be a little careful with setting NL_DATE_FORMAT. In Windows shell, double quotes are treated literally in statements, so use SET NL_DATE_FORMAT=YYYY/MM/DD instead of SET NL_DATE_FORMAT="YYYY/MM/DD" to define the environment variable's value. I guess this will bite you if you come from a Unix environment where the shell treats double-quotes as a string delimiter.

Software: Oracle Shutdown

I was trying to shutdown an Oracle server on my Windows test computer. Whenever I try to enter my credentials in the Oracle Enterprise Manager console, I get this message: RemoteOperationException: ERROR: Wrong password for user. Oracle's online help was utterly useless because it just describes exactly what I could see with no explanation or tips. I checked on Google and I found this thread. Message 173817 explains what to do, which is to allow my credentials to be used for running a batch job on Windows.

Software: Oracle ORA-12638 Credential retrieval failed

Sometimes the Oracle server throws an ORA-12638 error after I drop tables from a database with active connections and subsequently try to run SqlPlus on the server computer. My previous solution was to reboot the Windows server computer. Hey, when in Windows, do what the locals do.

A better solution can be found here. Since Oracle administration is not my game, I'll just accept that Martti's solution works and not ask any more questions.