Solved

Simple query concat quotes question

Posted on 2006-11-06
6
1,497 Views
Last Modified: 2008-01-09
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
Comment
Question by:Ade101
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 25 total points
ID: 17887290
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
 
LVL 27

Expert Comment

by:sujith80
ID: 17887318
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
 
LVL 6

Expert Comment

by:cjard
ID: 17890212
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Assisted Solution

by:awking00
awking00 earned 25 total points
ID: 17890726
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
 

Author Comment

by:Ade101
ID: 17893108
Great !! Many Thanks...
0
 

Author Comment

by:Ade101
ID: 17893117
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now