Home » SQL & PL/SQL » SQL & PL/SQL » double quotes in query (merged) (ORACLE10G)
double quotes in query (merged) [message #424777] Tue, 06 October 2009 00:16 Go to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
Hi

I get the last query executed using the ora_sql_txt (sql_text) function for some auditing.
Now on some server I get the last query as

select columnname from "schemaname"."tablename";
i.e I get double quotes as extra.

while on some I get without double quotes
select columnname from schemaname.tablename;

Is there some setting in oracle for this.

I am using oracle 10g

Sapan
Re: double quotes in query (merged) [message #424781 is a reply to message #424777] Tue, 06 October 2009 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you get the query as you send it.

Regards
Michel
Re: double quotes in query (merged) [message #424787 is a reply to message #424781] Tue, 06 October 2009 00:54 Go to previous messageGo to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
Hi Michel

If I send the query as
select * from "schema"."tablename";

then I get table does not exists error

but when I do

select * from schema.tablename;

then In the result for sql_text I get

select * from "schema"."tablename";
Re: double quotes in query (merged) [message #424789 is a reply to message #424787] Tue, 06 October 2009 01:07 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
If you pass object name in double quote ("), it will become case sensitive.
And without double quotes, Oracle will make it in upper case.
And when you will see it in SQL_TEXT, it will be auto-formated by Oracle.
Hope this makes your doubts clear.

regards,
Delna
Re: double quotes in query (merged) [message #424800 is a reply to message #424787] Tue, 06 October 2009 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but when I do

select * from schema.tablename;

then In the result for sql_text I get

select * from "schema"."tablename";

Prove it and show us.

Regards
Michel
Re: double quotes in query (merged) [message #424811 is a reply to message #424800] Tue, 06 October 2009 02:04 Go to previous messageGo to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
This is what goes to my table when i do

delete from auth_group where ......;

action || code || message || object_name || other stuff.......

DEL || 09E || "ORA-02292: integrity constraint (PROG.AUTHGR_FK) violated - child record found" ||"PROG"."AUTH_GROUP" || PROG 10.18.25.99 36405 RATING 19fb97 06/10/09....

I use when server error trigger to populate the above data which has sql_txt procedure
and the object_name is extracted from the sql query that caused the error.

note that the message is also enclosed in double quotes
Re: double quotes in query (merged) [message #424818 is a reply to message #424811] Tue, 06 October 2009 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SHOW US.
Do not explain, SHOW US.
Use SQL and COPY AND PASTE what you get.

Regards
Michel
Re: double quotes in query (merged) [message #424822 is a reply to message #424818] Tue, 06 October 2009 02:36 Go to previous messageGo to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
this is copy paste only
This is what I do all day
Wink

some clauses I had to remove (....).
Re: double quotes in query (merged) [message #424825 is a reply to message #424822] Tue, 06 October 2009 02:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you're not showing us is a case where you have issued a query like
SELECT * FROM schema.table
and this has appeared in your SQL log as
SELECT * FROM "SCHEMA"."TABLE"


Generally inside the database queries with "" surrounding table names are generated by Oracle itself - if you do deletions on tables which have foreign keys pointing to them you will find SQL in that format in v$sql as Oracle checks that there are no foreign key records pointing to the deleted rows.
Re: double quotes in query (merged) [message #424827 is a reply to message #424822] Tue, 06 October 2009 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
this is copy paste only

So
Quote:
delete from auth_group where ......;

is not a valid statement.

Regards
Michel
Re: double quotes in query (merged) [message #424830 is a reply to message #424827] Tue, 06 October 2009 03:22 Go to previous messageGo to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
Really?
This is a valid statement.
could we get over truth finding, Mr. Sherlock Holmes Smile
and try to help me out
Wink


Re: double quotes in query (merged) [message #424833 is a reply to message #424830] Tue, 06 October 2009 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> delete from auth_group where ......;
delete from auth_group where ......
                             *
ERROR at line 1:
ORA-00936: missing expression

Regards
Michel
Re: double quotes in query (merged) [message #424844 is a reply to message #424833] Tue, 06 October 2009 03:55 Go to previous messageGo to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
I told you I removed the where clause from my statement due to some reasons

Re: double quotes in query (merged) [message #424847 is a reply to message #424844] Tue, 06 October 2009 04:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
What you're not showing us is a case where you have issued a query like
SELECT * FROM schema.table

and this has appeared in your SQL log as
SELECT * FROM "SCHEMA"."TABLE"



Generally inside the database queries with "" surrounding table names are generated by Oracle itself - if you do deletions on tables which have foreign keys pointing to them you will find SQL in that format in v$sql as Oracle checks that there are no foreign key records pointing to the deleted rows.
Previous Topic: Updateing distinct values using another table (merged 6)
Next Topic: how to merge two lines into one in one field
Goto Forum:
  


Current Time: Wed Sep 28 09:15:19 CDT 2016

Total time taken to generate the page: 0.14154 seconds