Ade101
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
SELECT 'o''clock' FROM dual
While not related to your query, it will save you writing CHR(39) all the time
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great !! Many Thanks...
ASKER
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 :)
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';