Home » SQL & PL/SQL » SQL & PL/SQL » Handling single quotes in parameter passing (Oracle 10g)
Handling single quotes in parameter passing [message #351233] Tue, 30 September 2008 01:06 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I am having problem passing paramter to a sql script which is having single quotes as part of value.

SET serveroutput on size 999999;

DEFINE report_dir = '&1';

declare
  lv_par varchar2(100); 
begin
    
	select 'X' into lv_par from dual where 'ttest' = '&report_dir';
   
end;
/ 

SQL> @c:\test.sql "ttest";
old   5:        select 'X' into lv_par from dual where 'ttest' = '&report_dir';
new   5:        select 'X' into lv_par from dual where 'ttest' = 'ttest';


SQL> @c:\test.sql "ttest''";
old   5:        select 'X' into lv_par from dual where 'ttest' = '&report_dir';
new   5:        select 'X' into lv_par from dual where 'ttest' = 'ttest'';
ERROR:
ORA-01756: quoted string not properly terminated


This is just an example. In actual this test.sql is being called from shell script.

Re: Handling single quotes in parameter passing [message #351236 is a reply to message #351233] Tue, 30 September 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to double them.

Regards
Michel
Re: Handling single quotes in parameter passing [message #351238 is a reply to message #351233] Tue, 30 September 2008 01:12 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Yeah that I know.. but the input value is coming from a screen which is selected from drop down, so we don't know when it will be having quotes or not... any way it can be tracked and replaced?
Re: Handling single quotes in parameter passing [message #351246 is a reply to message #351238] Tue, 30 September 2008 01:36 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Make changes in your script by using some string functions.

You can easily do that.

Regards,
Rajat
Re: Handling single quotes in parameter passing [message #351256 is a reply to message #351238] Tue, 30 September 2008 02:07 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
himang wrote on Tue, 30 September 2008 08:12
Yeah that I know.. but the input value is coming from a screen which is selected from drop down, so we don't know when it will be having quotes or not... any way it can be tracked and replaced?

Systematically use REPLACE on the value.

Regards
Michel

[Updated on: Tue, 30 September 2008 02:08]

Report message to a moderator

Previous Topic: Reg the DDL trigger to avoid altering a table (merged)
Next Topic: How to view the status of the constraints in a single table
Goto Forum:
  


Current Time: Sat Dec 03 12:25:10 CST 2016

Total time taken to generate the page: 0.14863 seconds