Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1514
  • 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
 
SujithData ArchitectCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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