Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> column with quoted value, insert a column with a quoted value into a table
jmurphy
post Oct 12 2017, 02:08 PM
Post #1


Newbie
*

Group: Members
Posts: 6
Joined: 30-August 17
Member No.: 52,138



I have the below sql I want to generate sql statements from it. However the column line2 contains the value = vick's bar. When I run the script it splits it into 2 values.
How do I escape the single '

SELECT 'INSERT into test_address values ('''||'COMHSD-'||com_hsd_addr_seq.nextval||''','''||billing_system_house_number||''','''||'USA'||''','''||county||
''','''||directional_prefix||''','''||directional_suffix||''','''||line1||''','''||line2||''','''||line3||''','''||locdes1||
''','''||locdes2||''','''||locdes3||''','''||locval1||''','''||locval2||''','''||locval3||''','''||msag||''','''||ratecenter||
''','''||state||''','''||street_addr_prefix||''','''||street_addr_suffix||''','''||street_name||''','''||street_number||
''','''||street_number_suffix||''','''||street_type||''','''||towncity||''','''||zip||''','''||emergency_service_number||
''','''||null||''','''||null||''','''||msag_commumity||''','''||postal_community||''','''||timezone||''');' from comm_csg_hsd_&&1 where rownum < 300;
Thank you in advance for your assistance in this matter
Go to the top of the page
 
+Quote Post
burleson
post Oct 13 2017, 06:26 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi Murphy,

Just escape it by adding another single quote:

vick''s


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
jmurphy
post Oct 13 2017, 07:01 AM
Post #3


Newbie
*

Group: Members
Posts: 6
Joined: 30-August 17
Member No.: 52,138



QUOTE (burleson @ Oct 13 2017, 07:26 AM) *
Hi Murphy,

Just escape it by adding another single quote:

vick''s

I cant add or change the value inside the column "line2". I need to try and escape the column programmatically. Any suggestions would be helpful
Thanks
Go to the top of the page
 
+Quote Post
burleson
post Oct 13 2017, 01:05 PM
Post #4


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi,

Well you could write it in PL/SQL and assign the insert to a variable and then run it with dbms_sql:

http://www.dba-oracle.com/t_dbms_sql.htm

You can also try using the "execute immediate" approach.

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
jmurphy
post Oct 13 2017, 01:26 PM
Post #5


Newbie
*

Group: Members
Posts: 6
Joined: 30-August 17
Member No.: 52,138



thanks for your help will try
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Oct 13 2017, 11:14 PM
Post #6


Advanced Member
***

Group: Members
Posts: 85
Joined: 6-June 16
From: India
Member No.: 51,370



Hello jmurphy,

The multiple ways to get this done is shown below. The third pointer is what you need if you are into SQL.

1) You can go change all your single quotes to double single quotes, which is a tedious process as below,

select 'vick''s' result from dual;

2) Use PL/SQL in your code to achieve it as Mr. Burleson said.

3) Use the QUOTE literal.

select q'[vick's]' result from dual;


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd October 2017 - 03:41 PM