I read your article, How do I copy all views from one database to another? and tried running the code for Oracle 8i. Unfortunately when I tried to run the following code,
select ' CREATE VIEW '||view_name||'AS '||text||';' FROM user_views
I received an "ORA-00932:inconsistent datatypes" error. I'm trying to copy all of my views from an Oracle 8i database to Oracle 9i. Is there something I'm doing incorrectly?
Sorry about that. The TEXT column in USER_VIEWS is a LONG datatype and you cannot concatenate CHAR data to a LONG column as we are trying to do here. So we'll have to revise our query a little and ensure that we do not concantenate the LONG data type to the CHAR datatype. The query below should do the trick:
SELECT 'create view '||view_name||' AS ',text,';' FROM user_views;
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.