Home » SQL & PL/SQL » SQL & PL/SQL » Single quote to double quotes - please help
Single quote to double quotes - please help [message #349312] Fri, 19 September 2008 23:37 Go to next message
james_aron
Messages: 32
Registered: July 2007
Location: chennai
Member
Hi,

Please suggest me how to insert the dml_stmt into the table column..

I have the field called dml_statement field, in that user will give the dml statements.. my program
will pass this argument tO the procedure and that procedure will insert the data into the table called dml_table(in dml_stmt column)

For simple insert stmt, data is inserting into the table but for the dml whoes having the single
quote, it does not getting inserted into the table...

Let me explain this more clearly:

1)ENTER THE DML_STMT : INSERT INTO TABLE_NAME(COL1) VALUES(3434);
  ABOVE DML IS GETTING INSERTED BY THE PROCEDURE INTO THE TABLE DML_TABLE:

2)ENTER THE DML_STMT : INSERT INTO DML_TABLE(DML_STMT) VALUES('3424');
  ABOVE DML IS NOT GETTING INSERTED BY THE PROCEDURE, I HAVE FOUND THE PROBLEM TOO BUT CANT 
  OVERCOME THIS.BECAUSE OF THE SINGLE QUOTE PROCEDURE CANT INSERT THE ABOVE DML INTO THE DML_TABLE

I tried with replace function, but i cant pass the dml_stmt field
something like below :

select replace(INSERT INTO DML_TABLE(DML_STMT) VALUES('3424');,'chr(39)','"') from dual;
i know above qry wont work, but i should do something like above to solve this ....

Please suggest me how to solve this prob ..

Regards,
Jame
Re: Single quote to double quotes - please help [message #349315 is a reply to message #349312] Sat, 20 September 2008 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use bind variables you have not this kind of problem.
Otherwise you have to double the quotes.

Regards
Michel
Re: Single quote to double quotes - please help [message #349318 is a reply to message #349315] Sat, 20 September 2008 01:22 Go to previous messageGo to next message
james_aron
Messages: 32
Registered: July 2007
Location: chennai
Member
i have tried with bind variables, there tooo i am getting the same problem...

Prob in the sense, i can pass the variable into the variable but while trying to insert into the table, i am getting the erorr...

SQL> insert into dml_table values('&aa')
  2  /
Enter value for aa: insert into tab_name values('testing');
old   1: insert into dml_table values('&aa')
new   1: insert into dml_table values('insert into tab_name values('testing');')
insert into dml_table values('insert into tab_name values('testing');')
                                                           *
ERROR at line 1:
ORA-00917: missing comma


regards
Re: Single quote to double quotes - please help [message #349319 is a reply to message #349318] Sat, 20 September 2008 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you posted is not bind variable it is substitution variable.
Bind variable is:
SQL> var dml varchar2(1000)
SQL> exec :dml := 'insert into tab_name values(''testing'');'

PL/SQL procedure successfully completed.

SQL> print dml
DML
------------------------------------------
insert into tab_name values('testing');

SQL> insert into t values(:dml);

1 row created.

SQL> select * from t;
COL
---------------------------------------------
insert into tab_name values('testing');

1 row selected.

Regards
Michel
Re: Single quote to double quotes - please help [message #349320 is a reply to message #349315] Sat, 20 September 2008 01:33 Go to previous messageGo to next message
james_aron
Messages: 32
Registered: July 2007
Location: chennai
Member
Is anyway is there to replace all the singe quote(') to a double single quote('')? so that this will be solved easily...

because i am getting the actual dml as input like seen below:

insert into table_name ('test');

Regards,

[Updated on: Sat, 20 September 2008 01:39]

Report message to a moderator

Re: Single quote to double quotes - please help [message #349321 is a reply to message #349320] Sat, 20 September 2008 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
REPLACE

Regards
Michel
Re: Single quote to double quotes - please help [message #349322 is a reply to message #349321] Sat, 20 September 2008 01:45 Go to previous messageGo to next message
james_aron
Messages: 32
Registered: July 2007
Location: chennai
Member
If we are using replace() means, we should pass the dml stmt has a 1st parameter...there again its problem to pass the dml stmt..

wat u telling is something like below i hope:

replace(insert into tab_name values('test'),||chr(39)||,||chr(39)||chr(39));

Above wont work because of the single quote, please see the highlighted things....

There also i required the double quote, something like this ''test''...

regards,
Re: Single quote to double quotes - please help [message #349326 is a reply to message #349322] Sat, 20 September 2008 02:27 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The way you can do it depends on what this actual environment, how you will really insert this, from which, which program, which language, how, when, where...

Regards
Michel
Previous Topic: PL/SQL create text file from Oracle table data
Next Topic: ORA 06550 cannot execute procedure
Goto Forum:
  


Current Time: Sat Dec 03 13:40:50 CST 2016

Total time taken to generate the page: 0.06348 seconds