Export Rows Oracle

How do you export particular rows from a table?

Backup and Recovery Tips

How do you export particular rows from a table? For the purposes of this paper, assume I have the following EMP table: SQL>

SELECT ID, ENAME, MANAGER_ID, DEPT_ID, SALARY FROM EMP;

ID ---------1 2 3 4 5 6 7 8 9 10

ENAME MANAGER_ID DEPT_ID SALARY ------------------- ---------- ---------- ---------BENJAMIN BRITTEN 50 2500 WOLFGANG MOZART 1 41 1450 FELIX MENDELSSOHN 1 31 1400 LUDWIG BEETHOVEN 1 10 1450 GUSTAV MAHLER 1 50 1550 DMITRI SHOSTAKOVICH 2 41 1200 EDWARD ELGAR 2 42 1250 HENRY PURCELL 2 43 1100 AARON COPLAND 2 44 1300 ARAM KHATCHATURIAN 2 45 1307

The question is, therefore, given these 10 records in the EMP table, is it possible to export just the 5 records relating to Manager 2? The answer is that it is possible to do this, using the QUERY parameter. The basic idea would be to perform the export like this: C:\>EXP

SCOTT/TIGER TABLES=EMP QUERY=\”WHERE MANAGER_ID=2\”

When we issue this command, this is what happens: EXPORT: RELEASE 9.0.1.1.1 - PRODUCTION ON THU OCT 25 11:07:37 2001 (C) COPYRIGHT 2001 ORACLE CORPORATION. ALL RIGHTS RESERVED. ABOUT TO EXPORT SPECIFIED TABLES VIA CONVENTIONAL PATH ... EMP 5 . . EXPORTING TABLE EXPORT TERMINATED SUCCESSFULLY WITHOUT WARNINGS.

ROWS EXPORTED

…and you’ll notice that the export really did include just the 5 rows we’d expect to be selected. Note the syntax here, though: QUERY=\”WHERE MANAGER_ID=2\”. This looks almost like a ‘WHERE’ clause for an ordinary SQL statement – but why are those backslash characters there? The reason is that double quote marks are a reserved character for many Operating Systems, Unix and NT amongst them. Other such characters include “$”, “<”, “>” and single quotes, amongst others). So if you want them passed correctly to the export utility, you have to ‘escape’ those characters to get them interpreted as literals. That means including a special character just prior to the one you want interpreted literally, and this special character is the backslash. Copyright © Howard Rogers 2001

26/10/2001

Page 1 of 3

How do you export particular rows from a table?

Backup and Recovery Tips

This ‘escaping’ of characters makes for some rather messy export parameters, of course, and you should test them carefully before relying on them. It’s easy to make a mistake. The way I do it is to write the bare WHERE clause as though it was an ordinary select statement: WHERE DEPT_ID>35 AND ENAME LIKE

‘D%’

Then I stick double quotes around the whole thing: “WHERE

DEPT_ID>35 AND ENAME LIKE

‘D%’”

And then I stick an escape character in front of anything that looks vaguely suspicious (i.e., anything that isn’t the usual sort of alphanumeric text): \“WHERE

DEPT_ID\>35 AND ENAME LIKE

\‘D%\’\”

Finally, type in the rest of the export string at the start: EXP SCOTT/TIGER TABLES=EMP QUERY=\“WHERE DEPT_ID\>35 AND ENAME LIKE

\‘D%\’\”

…and that works, producing the expected 1 row export for Mr. Shostakovich. (Incidentally, and just to keep you on your toes, if you escape the ‘greater than’ symbol on NT, it does NOT work, complaining instead about ‘invalid characters’. But on Unix (well, Solaris at any rate), the thing will only work if the escape character is present at that point. It all goes to show that character escaping is O/S dependent, and rather sensitive).

Miscellaneous Points The QUERY parameter you supply is applied by Export to all objects that would come within the scope of its export acitivities. But a WHERE clause in a select statement clearly only has applicability to particular tables. So, for example, if I did this sort of export, I could expect somewhat awkward results: EXP SCOTT/TIGER TABLES=EMP,DEPT QUERY=\”WHERE SALARY\>1000\” ABOUT TO EXPORT SPECIFIED TABLES VIA CONVENTIONAL PATH ... . . EXPORTING TABLE EMP 10 ROWS EXPORTED . . EXPORTING TABLE DEPT EXP-00056: ORACLE ERROR 904 ENCOUNTERED ORA-00904: INVALID COLUMN NAME EXPORT TERMINATED SUCCESSFULLY WITH WARNINGS.

You’ll see at once what the problem is: the query for a test on the SALARY column is being applied to both the tables listed in the TABLES parameter –yet the test only makes sense for the EMP table, since departments don’t get paid salaries.

Copyright © Howard Rogers 2001

26/10/2001

Page 2 of 3

How do you export particular rows from a table?

Backup and Recovery Tips

Clearly, in some circumstances, applying the one selection test to a group of tables is exactly what you’d want to be able to do, and it’s good that the feature is there to support it. But under many (if not most) circumstances, it’s going to be an absurd thing to attempt. And for that reason, you can’t specify a QUERY parameter if you’ve selected to do a full, user or transportable tablespace export. (It would be ridiculous to spend time searching for a salary column amongst the 300 objects being exported as part of a complete SCOTT export, for example). Incidentally, I’ve just given a hint about another problem associated with specifying a QUERY parameter: it takes time to search through records. That means you can’t use such a parameter if you are also doing a direct path export (i.e., where you’ve set DIRECT=Y), since direct path exports bypass the usual SQL evaluation process altogether, and there would thus be no opportunity to perform the required record searches. If you try combining the two parameters, then, you’ll simply get an error: EXP-00071: QUERY EXP-00000: EXPORT

PARAMETER NOT COMPATIBLE WITH TERMINATED UNSUCCESSFULLY

DIRECT PATH

EXPORT

Finally, be aware that the QUERY parameter was only introduced in Oracle version 8.1 (8i). If you’ve got a version earlier than that, none of the above discussion applies to you (and you ought to think about upgrading!).

Copyright © Howard Rogers 2001

26/10/2001

Page 3 of 3