I wrote a dynamic query (cursor). The output of this query is as follows.
Table-name constraint_type total_constraint IND$ C 3 IND$ P 1 DESTINATION C 5 DESTINATION P 2
For that I have created table 'TAB_CONS', having a structure as follows:
(table_name varchar2(30) constraint_type varchar2(1) total_cons number(12))
Here, I am submitting my cursor for your ready reference.
---> declare totcons number(12); tabname varchar2(30); constype varchar2(1); str varchar2(200); cursor test_ROW is select table_name,constraint_type from dba_constraints; BEGIN OPEN TEST_ROW; FETCH TEST_ROW INTO TABNAME,constype; LOOP exit when test_row %notfound; str := 'select count(*) from dba_constraints where table_NAME = ' || TABNAME || ' AND CONSTRAINT_TYPE = '|| CONSTYPE ; DBMS_OUTPUT.PUT_LINE( STR ) ; execute immediate str into totcons; DBMS_OUTPUT.PUT_LINE( TO_CHAR(TOTCONS) ) ; insert into tab_cons values (tabname,constype,totcons); FETCH TEST_ROW INTO TABNAME,constype; END LOOP; CLOSE TEST_ROW; END; --->
First, it will fetch table_name, constraint_type and total count for the same constraint. It is to be inserted in the temporary table which I have already created.
1) I am configuring the same setup of Oracle database version 8.1.7. I created the Oracle database, then did a full import with constraint = no (because total 500 tables and many of them have PK/FK constraint).
2) After a successful completion of import, I am altering the table for PK/FK constraints for all necessary tables.
Up to this step (1 & 2), I have succesfully completed. Now I have to compare the statics of table_name/constraint_type/count with my original Oracle database.
------ Here is the error of my cursor:
select count(*) from dba_constraints where table_NAME = IND$ AND CONSTRAINT_TYPE = C ==== = declare * ERROR at line 1: ORA-00904: invalid column name ORA-06512: at line 17 ---------
How can I get table_name = 'IND$' AND CONSTRAINT_TYPE = 'C' i.e in single quotes?
To get a single quote to concatenate properly in a string you need to put two single quotes in a row in your statement as follows:
str := 'select count(*) from dba_constraints where table_NAME = ' || '''' || TABNAME || '''' || ' AND CONSTRAINT_TYPE = '|| '''' || CONSTYPE || '''' ;
This will cause the statement to be built like this:
select count(*) from dba_constraints where table_NAME = 'IND$' AND CONSTRAINT_TYPE = 'C'
That should fix your problem.
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2002