Q

Error in a dynamic query getting results in single quotes

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

Dig deeper on Oracle error messages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close