Home » SQL & PL/SQL » SQL & PL/SQL » insert query issue (oracle 10g)
insert query issue [message #572600] Thu, 13 December 2012 12:40 Go to next message
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
Re: insert query issue [message #572601 is a reply to message #572600] Thu, 13 December 2012 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58490
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
Re: insert query issue [message #572602 is a reply to message #572601] Thu, 13 December 2012 13:06 Go to previous messageGo to next message
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..?
Re: insert query issue [message #572603 is a reply to message #572601] Thu, 13 December 2012 13:09 Go to previous messageGo to next message
Littlefoot
Messages: 19297
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.
Re: insert query issue [message #572604 is a reply to message #572603] Thu, 13 December 2012 13:13 Go to previous messageGo to next message
rajmighty972
Messages: 11
Registered: December 2012
Junior Member
Ok Sir..
Re: insert query issue [message #572605 is a reply to message #572602] Thu, 13 December 2012 14:10 Go to previous message
Michel Cadot
Messages: 58490
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rajmighty972 wrote on Thu, 13 December 2012 20:06
Thanks Michel..will follow accordingly..Many Thanks..May i know what this q'[].will it work for all special characters too..?


http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=55941#176007

Regards
Michel
Previous Topic: How to get the file name from directory
Next Topic: Error(4,9): PLS-00103: Encountered the "," symbol
Goto Forum:
  


Current Time: Wed Jul 23 01:20:39 CDT 2014

Total time taken to generate the page: 0.06137 seconds