How to convert special character
I know some special character like "'" could be used in PL/SQL statement.
Is there certain function to convert "'" to "''" when I write dynamic SQL, so that it can be used correctly as character.
Thanks a lot.
Leave a comment...
- 4 Comments
- Even if you are forced to use dynamic SQL, you should still be using bind variables, which will avoid any issues with special characters in string literals.
Distributed Database Consulting, Inc.#1; Fri, 22 Feb 2008 16:32:00 GMT
- I ask this question because I write a anonymous block,which use dynamic SQL to delete rows from curtain table for DB management purpose. So,
schema,tablename,where-clause will be transferd into the script as parameters. Dynamic SQL should be used so that the script can be used in varied situation.
Since some special-char such as ' could be used in where-clause as script parameter, string literals will be broken at point. See below:
exec myproc 'schema' 'tname' 'where mytime > to_date('20040801','yyyymmdd')' ;
v_clause := '&3' ; -->string literal error here.
v_dml := 'delete from '||v_schema||'.'||v_tname||''||v_clause||' and rownum <= 200' ; --> delete 200 rows once.
v_cursornum := dbms_sql.execute(v_cursorid);
So the question is: How to deal with special-char when passing string as parameters.#2; Fri, 22 Feb 2008 16:33:00 GMT
- >> 'where mytime > to_date('20040801','yyyymmdd')'
That should be:
'where mytime > to_date(:date_bind,''yyyymmdd'')'
And, after the DBMS_SQL.PARSE and before DBMS_SQL.EXECUTE you would do a DBMS_SQL.BIND_VARIABLE to supply the value for your ":date_bind" bind variable.
If you do that, you no longer have the problem of single-quotes creating issues when building the dynamic sql string.#3; Fri, 22 Feb 2008 16:34:00 GMT
- Thank you,Kamal Kishore.
Because I'm not a programer but a DBA,so development is not in my strong.
>> exec myproc 'schema' 'tname' 'where mytime > to_date('20040801','yyyymmdd')' ; --this is the CALL statement to execute DELETE-SCRIPT. While you change it to: >>exec myproc 'schema' 'tname' 'where mytime > to_date(:date_bind,''yyyymmdd'')' ;
If I understand correctly, did you mention that "use bind veriable to pass string parameters into DELETE-SCRIPT to avoid SINGLE-QUOTES issue"? Am I right?#4; Fri, 22 Feb 2008 16:35:00 GMT