Carriage return from ASP page has problem with Oracle stored procedure

[lydiattc]

Hi,

I have some text boxes on an ASP page and there may be carriage returns in the text boxes. I tested them with regular in-line SQL statements and they work fine. However, if I turn the SQL statements into Oracle stored procedures, it will give me an error saying "Invalid SQL statement." whenever I put carriage return in the text box. Has anybody have the same experience? Can anybody help?

I'm working on Windows 2000 server, and the client is Windows 2000 or XP. Oracle is 9.2.0.1 on Windows.

Thanks,
Lydia

 

[sem]

Carriage return is string terminator in Oracle, so for sql engine your statement is finished abruptly. Try to replace carriage returns (accompanied by linefeed on Windows) with blank spaces.

Regards, Dima

 

[lydiattc]

Hi Dima,

Thanks for the reply. That's exactly what I was thinking. However, when I tried to use plain SQL statement instead of stored procedure, it worked fine. Wouldn't the sql engine interpret the SQL statement and the stored procedure the same way? (unfortuantely, I'll have to user stored procedure instead of sql statement)

To explain the problem more clearly, I'm posting the stored procedure for your reference. This is one of the procedures that were originally created in SQL Server 2000 and migrated into Oracle9i by using Oracle Migration Bench.

/********stored procedure to update Aspect_Table*****/
(
EMS_ID IN NUMBER DEFAULT NULL,
EMS_Area_ID IN NUMBER DEFAULT NULL,
Active_Status IN NUMBER DEFAULT NULL,
Aspect_Name IN VARCHAR2 DEFAULT NULL,
Personnel_ID IN NUMBER DEFAULT NULL,
Op_Area IN VARCHAR2 DEFAULT NULL,
Activity IN VARCHAR2 DEFAULT NULL,
Aspect_Desc IN VARCHAR2 DEFAULT NULL,
Object_Desc IN VARCHAR2 DEFAULT NULL,
Target_Desc IN VARCHAR2 DEFAULT NULL,
M_M_Desc IN VARCHAR2 DEFAULT NULL,
Indicator_1 IN VARCHAR2 DEFAULT NULL,
Rev_Date IN DATE DEFAULT NULL,
Rev_ID IN NUMBER DEFAULT NULL,
Aspect_ID IN NUMBER DEFAULT NULL)
AS
EMS_ID_ NUMBER(10,0) := EMS_ID;
EMS_Area_ID_ NUMBER(10,0) := EMS_Area_ID;
Active_Status_ NUMBER(10,0) := Active_Status;
Aspect_Name_ VARCHAR2(128) := Aspect_Name;
Personnel_ID_ NUMBER(10,0) := Personnel_ID;
Op_Area_ VARCHAR2(128) := Op_Area;
Activity_ VARCHAR2(128) := Activity;
Aspect_Desc_ VARCHAR2(4000) := Aspect_Desc;
Object_Desc_ VARCHAR2(4000) := Object_Desc;
Target_Desc_ VARCHAR2(4000) := Target_Desc;
M_M_Desc_ VARCHAR2(4000) := M_M_Desc;
Indicator_1_ VARCHAR2(150) := Indicator_1;
Rev_Date_ DATE := Rev_Date;
Rev_ID_ NUMBER(10,0) := Rev_ID;
Aspect_ID_ NUMBER(10,0) := Aspect_ID;
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
UPDATE Aspect_Table
SET EMS_ID = U_ASPECT.EMS_ID_,
EMS_Area_ID = U_ASPECT.EMS_Area_ID_,
Active_Status = U_ASPECT.Active_Status_,
Aspect_Name = U_ASPECT.Aspect_Name_,
Personnel_ID = U_ASPECT.Personnel_ID_,
Op_Area = U_ASPECT.Op_Area_,
Activity = U_ASPECT.Activity_,
Aspect_Desc = U_ASPECT.Aspect_Desc_,
Object_Desc = U_ASPECT.Object_Desc_,
Target_Desc = U_ASPECT.Target_Desc_,
M_M_Desc = U_ASPECT.M_M_Desc_,
Indicator_1 = U_ASPECT.Indicator_1_,
Rev_Date = U_ASPECT.Rev_Date_,
Rev_ID = U_ASPECT.Rev_ID_
WHERE Aspect_ID = U_ASPECT.Aspect_ID_;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
END U_ASPECT;
/*******************************************************/

The problem exits in any description fields, such as Aspect_Desc, Object_Desc, and Target_Desc. If I didn't call this stored procedure but used "UPDATE Aspect_Table SET .....", everthing would work fine, meaning any carriage return in any text field would be stored properly in the database and displayed properly later.

According to your suggestion, if I replace the carriage returns with blank spaces, when the text field is displayed, there will be another problem. I'd have to find the blank space or any character I replaced with earlier and replace it back to carriage return. Right?

Please help.

Thanks,
Lydia

 

[sem]

This error is raised by SQL engine, not PL/SQL one, so I can hardly imagine how it may work with stored procedure at all Does it really works without carriage returns?

Regards, Dima

 

[lydiattc]

Yes, it works fine without carriage returns. May I ask why you asked this question?