OraFAQ Forum: SQL & PL/SQL » insert query issue

Home » SQL & PL/SQL » SQL & PL/SQL » insert query issue (oracle 10g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

insert query issue [message #572600]

Thu, 13 December 2012 12:40

rajmighty972
Messages: 11
Registered: December 2012

Junior Member

hi,

I am new to oracle and tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.

insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')

getting missing comma with the above query.

when i tried to give as
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')

insert is happening but saving as

"select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"

how to avoid this and get the select query to store as

select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij

Report message to a moderator

Re: insert query issue [message #572601 is a reply to message #572600]

Thu, 13 December 2012 13:02

Michel Cadot
Messages: 54195
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working [[Test case]]: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

The simplest way is to use q string syntax:

SQL> create table t (v varchar2(1000));

Table created.

SQL> insert into t values(q'[select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij]');

1 row created.

SQL> select * from t;
V
------------------------------------------------------------------------------------------------------------------------
select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hi
j=B.hij

1 row selected.

Regards
Michel

Report message to a moderator

Re: insert query issue [message #572602 is a reply to message #572601]

Thu, 13 December 2012 13:06

rajmighty972
Messages: 11
Registered: December 2012

Junior Member

Thanks Michel..will follow accordingly..Many Thanks..May i know what this q'[].will it work for all special characters too..?

Report message to a moderator

Re: insert query issue [message #572603 is a reply to message #572601]

Thu, 13 December 2012 13:09

Littlefoot
Messages: 16980
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

An oldfashion option is to use two single quotes:

SQL> create table test (col varchar2(30));

Table created.

SQL> insert into test values ('select ''abc'' from dual');

1 row created.

SQL> select * from test;

COL
------------------------------
select 'abc' from dual

SQL>

From my point of view, "q" string syntax is far simpler because - when there are many single quotes you have to deal with - it becomes more and more complex and, finally, you get lost in the wood of double - triple - multiple single quotes.

Report message to a moderator