CODEWITHSUNDEEP
sqloracle-databaseplsql
sunleo
sunleo

Reputation: 10947

Oracle Procedure Escape characters

I am stuck with escape characters which give different values.Please help me to solve this problem.Please help me why same query is giving different results.

Query :

SELECT * FROM APP_REALM_ENTRIES WHERE ID IN (SELECT ID FROM APP_ENTRIES where APP_EXT_CODE ='TTL1' AND VERSION_NUMBER='1.0.1');

Result : Single row result comes

SQL Block :

declare 
appcode varchar2(20);
version_number varchar2(20);
type rc is ref cursor;
table_cursor rc;
rec_table REALM_ENTRIES%ROWTYPE;
begin
appcode := 'TTL1';
version_number := '1.0.1';
open table_cursor for 'SELECT * FROM REALM_ENTRIES WHERE ID IN (SELECT ID FROM APP_ENTRIES where APP_EXT_CODE ='''||appcode||''||'AND VERSION_NUMBER='||version_number||''')';
LOOP
    FETCH table_cursor INTO rec_table;
    DBMS_OUTPUT.PUT_LINE('ROWCOUNT ' ||  table_cursor%ROWCOUNT ); 
    EXIT WHEN table_cursor%NOTFOUND;
  END LOOP; 
  CLOSE table_cursor;
end;

Result : ROWCOUNT 0

Upvotes: 0

Views: 1355

Answers (3)

Praveen
Praveen

Reputation: 9335

Oracle Quotes in String makes escape ' bit easier;

q'{query with "'" }'

now you can write;

open table_cursor  for q'{
  SELECT * FROM APP_REALM_ENTRIES 
  WHERE ID IN (
  SELECT ID FROM APP_ENTRIES where 
  APP_EXT_CODE ='TTL1' AND VERSION_NUMBER='1.0.1')}';

Upvotes: 1

Sentinel
Sentinel

Reputation: 6449

As Justin suggested use bind variables. You'll achieve two things by doing that, you won't need to worry about getting the number of quotes correct, and more importantly, you'll close the door to SQL Injection vulnerabilities. You can do this by changing your open statement to the following:

open table_cursor for 'SELECT * FROM REALM_ENTRIES WHERE ID IN (SELECT ID FROM APP_ENTRIES where APP_EXT_CODE =:appcode AND VERSION_NUMBER=:version_number)'
using appcode, version_number;

Upvotes: 1

sstan
sstan

Reputation: 36473

You can avoid all the headaches associated to dynamic SQL and explicit cursor manipulation by using this very simplified implicit cursor for loop (Documentation: Query Result Set Processing With Cursor FOR LOOP Statements):

for rec in (
  SELECT * 
  FROM APP_REALM_ENTRIES 
  WHERE ID IN (SELECT ID 
               FROM APP_ENTRIES 
               where APP_EXT_CODE = appcode 
               AND VERSION_NUMBER= version_number)
) loop
  -- read values from 'rec' object.
end loop;

But for what it's worth, you weren't doubling your single quotes correctly. Examples:

||''|| -- this is appending NULL, not a single quote.

You probably meant to do this instead:

||''''|| -- this appends 1 single quote.

Also...

'AND VERSION_NUMBER='||version_number -- this is not adding a single quote before appending the version_number value.

You probably meant to do this instead:

'AND VERSION_NUMBER='''||version_number

Upvotes: 1

Related Questions