Corrupt stored procedure where apostrophes in comments

General discussion forum about all databases other than SqlBase.
dmorris

Corrupt stored procedure where apostrophes in comments

Post by dmorris » 26 Nov 2015, 20:40

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

Re: Corrupt stored procedure where apostrophes in comments

Post by Mike Vandine » 03 Dec 2015, 06:54

Hi Darren,

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

Thanks!

dmorris

Re: Corrupt stored procedure where apostrophes in comments

Post by dmorris » 04 Dec 2015, 19:52

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.
You do not have the required permissions to view the files attached to this post.

VEspinal

Re: Corrupt stored procedure where apostrophes in comments

Post by VEspinal » 07 Dec 2015, 20:00

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
You do not have the required permissions to view the files attached to this post.

dmorris

Re: Corrupt stored procedure where apostrophes in comments

Post by dmorris » 10 Dec 2015, 22:26

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).

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest