Archive for June, 2014
I left to chance where students would attempt to place their external files in a Linux or Unix implementation. As frequently occurs, they choose a location in their
student user’s home directory. Any attempt to read an external table based on a file in this type of directory fails because it’s not accessible by the Oracle user because the directory within the
student user’s account isn’t reachable. You can’t simply
chown a directory and the files in a directory.
The failure returns the following result:
SELECT COUNT(*) FROM transaction_upload * ERROR AT line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening FILE /home/student/upload/transaction_upload.LOG
The reason isn’t readily visible to all, but a virtual directory must reference a physical directory owned by the
oracle user and
dba group. That also means the
oracle user must own all directories from the logical mount point to the physical directory name.
Assuming you’re working in an Oracle Database 11g XE instance, you can create a valid
upload directory by navigating to this directory:
Then, issue this command as the
root user to create a new
Now you have the following directory:
Assuming you’ve created the
upload directory as the
root user, the
root user should issue the following two commands from the
chown -R oracle:dba upload chmod -R 755 upload
Having made that change you should now be able to query the external file source, like a *.csv (comma-separated values) file. Hope this helps those trying to use external tables, which I subsequently wrote about for Toad World – External Tables.
A call to a
PRICE_S1 sequence in a query with an
ORDER BY clause is disallowed, which typically occurs in an
INSERT statement. Any attempt raises the following exception:
SELECT price_s1.NEXTVAL AS price_id * ERROR AT line 1: ORA-02287: SEQUENCE NUMBER NOT allowed here
You need to remove the
ORDER BY clause to eliminate the error.
While working with an error that my students surfaced in the Oracle Database 12c, I blogged about the limit of using a subquery in an Oracle
INSERT statement, and I discovered something when retesting it in MySQL. It was a different limitation. I was also surprised when I didn’t find any mention of it through a Google search, but then I may just not have the right keywords.
The original test case uses a subquery to create a multiple row result set for an
INSERT statement. Unfortunately, I discovered that you can’t always embed a
UNION ALL inside a subquery. At least, you can’t when you call the subquery inside an
INSERT statement. For example, let’s create a
DESTINATION table and then we’ll try to insert records with a query that fabricates a result set.
-- Drop the destination table. DROP TABLE IF EXISTS destination; -- Create the destination table. CREATE TABLE destination ( destination_id int unsigned , destination_name varchar(30));
Now let’s try the subquery:
1 2 3 4
INSERT INTO destination ( SELECT 1,'Sample1' UNION ALL SELECT 2,'Sample2' );
It raises the following error message:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL SELECT 2,'Sample2' )' at line 3
I wasn’t sure if I missed something. It turned out that I did. While you can put a subquery inside an
INSERT statement, you can’t embed it inside a set of parentheses when it contains a
UNION ALL set operator. Other statements work with or without parentheses.
1 2 3 4
INSERT INTO destination SELECT 1,'Sample1' UNION ALL SELECT 2,'Sample2';
If somebody has any more to add, I look forward to reading it.
Sometimes my students find new errors that I’ve never seen. One student did that this week by including an
ORDER BY clause in a subquery that feeds an
INSERT statement. It raises an
ORA-00907 exception, like:
ORA-00907: missing right parenthesis
You can’t include a subquery with an
ORDER BY clause because it generates an error. The reason is simple. A subquery can’t perform a sort operation inside a subquery. Here’s a quick demonstration:
DROP TABLE destination; CREATE TABLE destination ( destination_id NUMBER , destination_name VARCHAR2(20)); INSERT INTO destination ( SELECT 1,'Sample1' FROM dual UNION ALL SELECT 2,'Sample2' FROM dual ORDER BY 1 DESC );
If you remove the
ORDER BY clause, the statement works without a problem. For example, here’s the working version:
INSERT INTO destination ( SELECT 1,'Sample1' FROM dual UNION ALL SELECT 2,'Sample2' FROM dual );
Alternatively, you can include an
ORDER BY clause when you remove the parentheses from around the subquery. This is an example:
INSERT INTO destination SELECT 1,'Sample1' FROM dual UNION ALL SELECT 2,'Sample2' FROM dual ORDER BY 1 DESC;
I hope this helps anybody who runs into the problem.