A simple example of using UTL_FILE in PLSQL to create a file and write into it. Plus, the common ORA errors you encounter with the use of UTL_FILE, and how to resolve them.
ORA Errors
Oracle database errors – probable causes, how to resolve them or work around them.
The Curious Case of The Missing ORA-00904
“ORA-00904: invalid identifier” is a familiar error to Oracle developers. It occurs, for example, when you refer to a table column that does not exist.
What’s worse than an error that shows up when you don’t expect it? An error that does NOT show up when you totally expect it.
Here’s a puzzle for you to solve. You’re given two tables – EMPL and DEPT – in which column EMP_DEPT_ID of table EMPL references DEPT_ID of table DEPT.
SQL> desc dept Name Null? Type ----------------- -------- ------------ DEPT_ID NUMBER(2) DEPT_NAME VARCHAR2(6) SQL> desc empl Name Null? Type ----------------- -------- ------------ EMP_ID NUMBER(2) EMP_NAME VARCHAR2(6) EMP_DEPT_ID NUMBER(2)
Note that the foreign key column names in the two tables are not identical. The column is called DEPT_ID in table DEPT, EMP_DEPT_ID in table EMPL.
Referencing User Defined Types over DBLink: Problem and Alternatives
Consider a PL/SQL call to a function which returns a user-defined type (UDT) – say, a SQL Type object. This works well as long as the called function resides in the same database as the calling PL/SQL, but if the called function is in a remote database, the PL/SQL call via DBLink end ups with the following error:
Error: PLS-00453: remote operations not permitted on object tables or user-defined type columns
This has often led to issues discovered too late for developers, who successfully tested function calls over multiple schemas simulating remote calls, but when the function databases were separated in later stages of testing, the PL/SQL call no longer worked.
ORA-55610: Invalid DDL statement on history-tracked table
If you have got Oracle Total Recall enabled on a table, and now want to truncate or drop the table, you are in for a problem.
SQL> truncate table customer;
truncate table customer
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> drop table customer;
drop table customer
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled.
SQL> -- Check history tracking SQL> select flashback_archive_name 2 from dba_flashback_archive_tables 3 where table_name = 'CUSTOMER'; FLASHBACK_ARCHIVE_NA -------------------- TOTALRECALL_FA
How do you get around this problem?
Drop and Recreate All Foreign Keys on Oracle Table
The last post showed a workaround for the error “ORA-02449: unique/primary keys in table referenced by foreign keys“, which blocks any attempt to drop an Oracle table if there are foreign keys from other tables referring to it.
The caveat: if the dropped table has to be recreated, the dropped foreign keys must be recreated as well.
How will you determine which foreign keys are to be recreated, and how will you quickly create them? Read on for an easy solution.
ORA-02449: Solving the DROP TABLE Dilemma
Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449:
SQL> drop table agreement;
drop table agreement
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this problem, and from which tables.
Luckily, there is a quick workaround to ORA-02449, provided you are ready to heed the risks of the approach along with it.
Unlock Database User Account in Oracle Express Edition
Quick tip to unlock database user account in Oracle Express Edition (XE) – this is among the first things you’d need to do when you start using Oracle XE.
The default user HR in Oracle Express Edition is locked when you first try to login after database installation.
SQL> conn hr/hr ERROR: ORA-28000: the account is locked
This is apparently due to security reasons, and needs to be unlocked by the administrator.
To unlock HR — or any other database user account in Oracle Database XE – follow the steps below.
Why the SQL WITH clause is not exactly like a function definition
The SQL WITH clause is similar in concept to a function definition in procedural code. In a function, we factor the common code, put it all together at one place and call it as many times as needed in the main program. That’s precisely how we use the WITH clause in SQL – factor out the common subquery, put it all together at one place and call it as many times as needed in the main query.
BUT there is a difference.
ORA-06592: CASE_NOT_FOUND Exception
CASE is a smarter rewrite for IF-THEN-ELSE, we said. It is for sure, but there is a difference in the way the ELSE part of it is handled.
Compare the code units below, one using CASE WHEN the other using IF-ELSIF. Both are identical in logic – two defined conditions, no ELSE path.