Monday, May 21, 2012

Here's an odd one: Do not comment the line before the last at object creation... when using Quest Toad. Or Oracle SQL Developer. Possibly others.

Why not? Datapump (more specific: impdp) might create problematic sql scripts as a result.

Validation:

Create the user:

CREATE USER testuser
IDENTIFIED BY testuser
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CONNECT TO testuser;
ALTER USER testuser DEFAULT ROLE ALL;
GRANT CREATE VIEW TO testuser;
ALTER USER testuser QUOTA UNLIMITED ON USERS;

Create 2 views, the first one having a comment at the end before the line with the semicolon:

create or replace force view testuser.test (v_col) as
select object_name from user_objects
where object_type='VIEW' -- added 20120521 by Robert Hanuschke
;

create or replace force view testuser.test2 (v_col) as
select object_name from user_objects
where object_type='VIEW'
;

Export the user:

expdp system schemas=testuser directory=DATA_PUMP_DIR dumpfile=testuser.dmp

Create the sqlfile using impdp:

impdp system schemas=testuser dumpfile=testuser.dmp directory=DATA_PUMP_DIR sqlfile=test.sql

Have a look at the View creation part of the SQL script being generated in the DATA_PUMP_DIR:

-- new object type path: SCHEMA_EXPORT/VIEW/VIEW
CREATE FORCE VIEW "TESTUSER"."TEST" ("V_COL") AS
select object_name from user_objects
where object_type='VIEW' -- added 20120521 by Robert Hanuschke;
CREATE FORCE VIEW "TESTUSER"."TEST2" ("V_COL") AS
select object_name from user_objects
where object_type='VIEW'
;

See the mistake? In the first definition, the closing semicolon was moved into the comment, rendering both view definitions useless, resulting in an "ORA-00933: SQL command not properly ended".

I could reproduce this if the views were created via Quest Toad (version 11.5) and Oracle SQL Developer (version 3.1) - but not when connecting directly via sqlplus (thin client version 11.2 or directly on the server). Did not have any other means of connecting to my databases to test - well, except for DBVisualizer 7.1.5, but that one just cuts off comments at object creation making a validation impossible.

Both a 10g (10.2.0.5) and an 11g (11.2.0.3) database showed the same behavior.


No comments:

Post a Comment