• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1512
  • Last Modified:

Simple query concat quotes question

Hi

I am trying a fairly simple thing in pl/sql (oracle 9i). I need to check if a table exist or not. If is does then do some stuff else email user.

The problem is with the quotes around the string. my query is

SELECT COUNT(*) into tblYesNo  FROM sys.all_objects WHERE object_type = 'TABLE' AND object_name =  || CHR(39) || MY_TABLE || CHR(39) AND owner = CHR(39) || MY_OWNER || CHR(39);


Here tblYesNo, MY_TABLE, MY_OWNER are all variables and are being passed to this statement. As you know for comparison in where clause they should be concatinated with the quote e.g.'name'. I am not sure what is the way to concatinate a quote on both left and right side of my variable. I tried using its ASSI code which is CHR(39). It show correct statement when i debut.print but my statement returns nothing i.e. 0

Also is ther a way to get the date when a table was last analyzed if at all ?

what should i do. Please suggest.
0
Ade101
Asked:
Ade101
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the MY_TABLE and MY_OWNER are variables, you don't need the quotes, as the variables are clearly of varchar data type, so no confusion possibly:

SELECT COUNT(*) into tblYesNo  FROM sys.all_objects WHERE object_type = 'TABLE' AND object_name =  MY_TABLE AND owner = MY_OWNER;

   
0
 
sujith80Commented:
Since MY_TABLE and MY_OWNER are varchar2 type variables there is no need to pad single quotes on both sides. You can directly use like:

SELECT COUNT(*) into tblYesNo  FROM sys.all_objects WHERE object_type = 'TABLE' AND object_name =  MY_TABLE AND owner = MY_OWNER ;

To find the last analyzed date use:
select LAST_ANALYZED from user_tables where table_name = 'TAB1';
0
 
cjardCommented:
incidentally, the way to pass quotes into a query is by doubling them up:

SELECT 'o''clock' FROM dual


While not related to your query, it will save you writing CHR(39) all the time
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
awking00Commented:
Since you are selecting into a variable named tblYesNo, this is apparently part of a procedure. You can circumvent the quotes issue with dynamic sql.
declare
sqlstmt  varchar2(255);
begin
...
sqlstmt := 'SELECT COUNT(*) FROM sys.all_objects WHERE object_type = ''TABLE'' AND object_name =  '
||MY_TABLE ||' AND owner = '|| MY_OWNER;
execute immediate sqlstmt into tblYesNo;

Also, note that select last_analyzed from {dba|all|user}_tables will return null if table has not been analyzed.
0
 
Ade101Author Commented:
Great !! Many Thanks...
0
 
Ade101Author Commented:
Thats what i thought in first place why it was giving error when i am using VARCHAR variable. It worked this time .. There was another problem which was causing this :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now