Corrupt stored procedure where apostrophes in comments

[dmorris]

The following code (using TD 6.3 sp1) will create a corrupt Oracle stored procedure\function because the apostrophe within the comment line is causing carriage returns (0x0D) to be added to subsequent lines:

Code: Select all

Return SqlPrepareAndExecute(hSql, 
"create or replace
function DoNothing
   return varchar2 
is    
begin 
-- The cat's apostrophe
-- will cause the following lines
-- to have carriage return
   return 'nothing'; 
end;" 
)

The oracle sqlnet net trace shows that some carriage returns are not being stripped out by the TD sqlnet router:

Code: Select all

nspsend: 00 00 00 00 FE 40 63 72  |.....@cr|
nspsend: 65 61 74 65 20 6F 72 20  |eate.or.|
nspsend: 72 65 70 6C 61 63 65 20  |replace.|
nspsend: 0A 66 75 6E 63 74 69 6F  |.functio|
nspsend: 6E 20 44 6F 4E 6F 74 68  |n.DoNoth|
nspsend: 69 6E 67 20 0A 09 72 65  |ing...re|
nspsend: 74 75 72 6E 20 76 61 72  |turn.var|
nspsend: 63 68 61 72 32 20 20 0A  |char2...|
nspsend: 69 73 20 09 20 0A 40 62  |is....@b|
nspsend: 65 67 69 6E 20 20 0A 2D  |egin...-|
nspsend: 2D 20 54 68 65 20 63 61  |-.The.ca|
nspsend: 74 27 73 20 61 70 6F 73  |t's.apos|
nspsend: 74 72 6F 70 68 65 0D 0A  |trophe..|
nspsend: 2D 2D 20 77 69 6C 6C 20  |--.will.|
nspsend: 63 61 75 73 65 20 74 68  |cause.th|
nspsend: 65 20 66 6F 6C 6C 6F 77  |e.follow|
nspsend: 69 6E 67 20 6C 69 6E 3A  |ing.lin:|
nspsend: 65 73 0D 0A 2D 2D 20 74  |es..--.t|
nspsend: 6F 20 68 61 76 65 20 63  |o.have.c|
nspsend: 61 72 72 69 61 67 65 20  |arriage.|
nspsend: 72 65 74 75 72 6E 0D 0A  |return..|
nspsend: 09 72 65 74 75 72 6E 20  |.return.|
nspsend: 27 6E 6F 74 68 69 6E 67  |'nothing|
nspsend: 27 3B 20 0D 0A 65 6E 64  |';...end|

The same code works correctly with TD 4.2.

Compare this with the following code (also TD 6.3 sp1) which generates a valid Oracle function because I have replaced the apostrophe in the comment with a space. Please note that the TD sqlnet router has stripped out all carriage returns.

Code: Select all

Return SqlPrepareAndExecute(hSql, 
"create or replace
function DoNothing
   return varchar2 
is    
begin 
-- The cat s apostrophe
-- will cause the following lines
-- to have carriage return
   return 'nothing'; 
end;" 
)

Resulting in line feeds only:

Code: Select all

nspsend: 00 00 00 00 FE 40 63 72  |.....@cr|
nspsend: 65 61 74 65 20 6F 72 20  |eate.or.|
nspsend: 72 65 70 6C 61 63 65 20  |replace.|
nspsend: 0A 66 75 6E 63 74 69 6F  |.functio|
nspsend: 6E 20 44 6F 4E 6F 74 68  |n.DoNoth|
nspsend: 69 6E 67 20 0A 09 72 65  |ing...re|
nspsend: 74 75 72 6E 20 76 61 72  |turn.var|
nspsend: 63 68 61 72 32 20 20 0A  |char2...|
nspsend: 69 73 20 09 20 0A 40 62  |is....@b|
nspsend: 65 67 69 6E 20 20 0A 2D  |egin...-|
nspsend: 2D 20 54 68 65 20 63 61  |-.The.ca|
nspsend: 74 20 73 20 61 70 6F 73  |t.s.apos|
nspsend: 74 72 6F 70 68 65 20 0A  |trophe..|
nspsend: 2D 2D 20 77 69 6C 6C 20  |--.will.|
nspsend: 63 61 75 73 65 20 74 68  |cause.th|
nspsend: 65 20 66 6F 6C 6C 6F 77  |e.follow|
nspsend: 69 6E 67 20 6C 69 6E 3A  |ing.lin:|
nspsend: 65 73 20 0A 2D 2D 20 74  |es..--.t|
nspsend: 6F 20 68 61 76 65 20 63  |o.have.c|
nspsend: 61 72 72 69 61 67 65 20  |arriage.|
nspsend: 72 65 74 75 72 6E 20 0A  |return..|
nspsend: 09 72 65 74 75 72 6E 20  |.return.|
nspsend: 27 6E 6F 74 68 69 6E 67  |'nothing|
nspsend: 27 3B 20 20 0A 65 6E 64  |';...end|

Obrigado

Darren

[Mike Vandine]

Hi Darren,

Please give us a small TD case that shows this problem.

Thanks!

[dmorris]

I have attached a test application and sample sql.ini fle (as attachment 2015.12.03_corrupt_function.zip).

I tested using the following Oracle Clients:

Client with 10.2.0 Oracle xe server .
11_2 instant client.

[VEspinal]

Hi:

First at all, please be aware that TD 6.3 does not support Oracle 10

Secondly, I have tested you reprocase usando instant client Oracle 11.2 agains Oracle 12c and I was able to run the SP with and without apostrophe (see attached pictures).

There is no error nor corrupted stored procedure.

Thanks.

Best Regards,
Victor Espinal

[dmorris]

Thanks for you investigation. I tried the same test with an Oracle 11g server and have verified that the resultant SP is valid and runs.

However, please see the following:


I selected the text for DONOTHING with the line endings displayed explicitly. See below:

1* SELECT REPLACE(REPLACE(TEXT, CHR(13),'\r'),CHR(10),'\n') AS TEXT FROM ALL_S
OURCE where name = 'DONOTHING'
SQL> /

TEXT
--------------------------------------------------------------------------------

function DoNothing \n
return varchar2 \n
is \n
begin \n
-- The cat's apostrophe\r\n
-- will cause the following lines\r\n
-- to have carriage return\r\n
return 'nothing'; \r\n
end;

9 rows selected.


-----------------------------------------

I then selected did the same for DONOTHING1. See below:

1* SELECT REPLACE(REPLACE(TEXT, CHR(13),'\r'),CHR(10),'\n') AS TEXT FROM ALL_S
OURCE where name = 'DONOTHING1'
SQL> /

TEXT
--------------------------------------------------------------------------------

function DoNothing1 \n
return varchar2 \n
is \n
begin \n
-- The cats apostrophe \n
-- will cause the following lines \n
-- to have carriage return \n
return 'nothing'; \n
end;

9 rows selected.

-----------------
The presence of the apostrophe is causing line ending differences. However Oracle 11g does not care about this whereas 10 does.

If the issue is isolated to apostrophes within comments I don't think this warrants a change to TD 6.3; it should just be noted as a potential conflict between TD 6.3 and <= Oracle 10 (which I understand is not supported anyway).