Home » SQL & PL/SQL » SQL & PL/SQL » problem with stored procedure and asp page. (Oracle 10, windows)
problem with stored procedure and asp page. [message #349056] |
Thu, 18 September 2008 13:41  |
macrosblackd
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
I appoligize for the long post, but I'm having some problems with oracle / asp classic.
The errors:
ORA-06550: line 1, column 143:
PLS-00103: Encountered the symbol ">" when expecting one of the following: . ( ) , * @ % & = - + < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like between || The symbol "( was inserted before ">" to continue.
ORA-06550: line 1, column 252:
PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * % & = - + < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like between || The symbol ")" was substituted for ";"
The pl/sql:
CREATE OR REPLACE PROCEDURE SP_ADDSITE
(
IN_NAME in nVarChar2,
IN_SITE_CODE in nVarChar2,
IN_COUNTRY_ID in number,
IN_STREET_ADDR in Char,
IN_CITY in nVarChar2,
IN_STATE_ID in nVarChar2,
IN_ZIP_CD in nVarChar2,
IN_PO_BOX in nVarChar2,
IN_DHL_ADDR in nVarChar2,
IN_FUNDING_CODE_ID in number,
IN_DODAAC in nVarChar2,
OUT_SITE_ID out number
)
IS
v_site_id SITES.site_id%TYPE;
BEGIN
SELECT site_id into v_site_id
FROM SITES
WHERE site_cd = IN_SITE_CODE;
IF v_site_id > 0 THEN
OUT_SITE_ID := -1;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO SITES
(SITE_ID, NAME, SITE_CD, COUNTRY_ID,STREET_ADDR, CITY, STATE_ID,
ZIP_CD, PO_BOX, DHL_ADDR, FUNDING_CODE_ID, DODAAC)
VALUES
(SITE_ID_SEQ.NEXTVAL, IN_NAME, IN_SITE_CODE, IN_COUNTRY_ID, IN_STREET_ADDR,
IN_CITY, IN_STATE_ID, IN_ZIP_CD, IN_PO_BOX, IN_DHL_ADDR, IN_FUNDING_CODE_ID, IN_DODAAC);
SELECT SITE_ID_SEQ.CURRVAL INTO OUT_SITE_ID FROM dual;
END;
/
the vbscript:
Function AddSite(strSiteName, strSiteCode, intCountryId, strStreetAddr, strCity, strStateId, _
strZip, strPOBox, strTCNAddr, strDHLAddr, strMAJCOM, strDODAAC)
Dim rsData, tassDB, objComm
Set tassDB = OpenDB()
Set objComm = Server.CreateObject("ADODB.Command")
With objComm
Set .ActiveConnection = tassDB
.CommandText = "sp_addsite"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("IN_NAME", adVarWChar, adParamInput, 120, strSiteName)
response.Write("<br>strSiteName: " & strSiteName)
.Parameters.Append .CreateParameter("IN_SITE_CODE", adVarWChar, adParamInput, 6, strSiteCode)
response.Write("<br>strSiteCode: " & strSiteCode)
.Parameters.Append .CreateParameter("IN_COUNTRY_ID", adVarNumeric, adParamInput, , intCountryId)
response.Write("<br>intCountryId: " & intCountryId)
.Parameters.Append .CreateParameter("IN_STREET_ADDR", adChar, adParamInput, 200, strStreetAddr)
response.Write("<br>strStreetAddr: " & strStreetAddr)
.Parameters.Append .CreateParameter("IN_CITY", adVarWChar, adParamInput, 100, strCity)
response.Write("<br>strCity: " & strCity)
.Parameters.Append .CreateParameter("IN_STATE_ID", adVarWChar, adParamInput, 2, strStateId)
response.Write("<br>strStateId: " & strStateId)
.Parameters.Append .CreateParameter("IN_ZIP_CD", adVarWChar, adParamInput, 15, strZip)
response.Write("<br>strZip: " & strZip)
.Parameters.Append .CreateParameter("IN_PO_BOX", adVarWChar, adParamInput, 200, strPOBox)
response.Write("<br>strPOBox: " & strPOBox)
.Parameters.Append .CreateParameter("IN_DHL_ADDR", adVarWChar, adParamInput, 200, strDHLAddr)
response.Write("<br>strDHLAddr: " & strDHLAddr)
.Parameters.Append .CreateParameter("IN_FUNDING_CODE_ID", adVarNumeric, adParamInput, , CInt(strMAJCOM))
response.Write("<br>CInt(strMAJCOM): " & CInt(strMAJCOM))
.Parameters.Append .CreateParameter("IN_DODAAC", adVarWChar, adParamInput, 50, strDODAAC)
response.Write("<br>strDODAAC: " & strDODAAC)
.Parameters.Append .CreateParameter("OUT_SITE_ID", adVarNumeric, adParamOutput)
Response.Write(.CommandText)
.Execute
AddSite = .Parameters.Item("OUT_SITE_ID")
End With
End Function
the test data:
strSiteName: test site b
strSiteCode: qwerty
intCountryId: 8
strStreetAddr: null
strCity: null
strStateId: null
strZip: null
strPOBox: null
strDHLAddr: null
CInt(strMAJCOM): 1
strDODAAC: qwerty
[Updated on: Thu, 18 September 2008 13:47] Report message to a moderator
|
|
|
Re: problem with stored procedure and asp page. [message #349298 is a reply to message #349056] |
Fri, 19 September 2008 14:09   |
macrosblackd
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
An update on this error...
It only occurs with stored procedures so far. And it comes and goes as the day goes by, with no changes to either the ASP code or the oracle code.
If anyone can give any advice on how to fix or bypass this error it would be a great help. Changing to a text command in ASP isn't really an option due to the fact that this will be a public facing web page and we need to protect against sql injection attacks.
[Updated on: Fri, 19 September 2008 14:12] Report message to a moderator
|
|
|
Re: problem with stored procedure and asp page. [message #349632 is a reply to message #349298] |
Mon, 22 September 2008 07:45   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Not a clue I'm afraid.
I'm tempted to say that the error isn't happening in the proedure you've posted, as
1) The error message doesn't list the procedrue name
2) Your peocedure doesnt' have any lines that go up to column 252.
Can you find out what the command that is getting executed is - ie can you see the actual SQL that you are building up?
|
|
|
Re: problem with stored procedure and asp page. [message #349634 is a reply to message #349632] |
Mon, 22 September 2008 07:54   |
macrosblackd
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
I'm not sure how I would go about doing this, I can have ASP output the command string, but it would only be
"{call sp_addsite(?,?,?,?,?,?,?,?,etc..)}"
|
|
|
|
Re: problem with stored procedure and asp page. [message #349640 is a reply to message #349638] |
Mon, 22 September 2008 08:15   |
macrosblackd
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Here is the exact output from the ASP page.
Below are the passed parameters, with their type and value.
Name: IN_NAME
Type: 202
size: 120
value: test side d
Name: IN_SITE_CODE
Type: 202
size: 6
value: tsd
Name: IN_COUNTRY_ID
Type: 139
size: 0
value: 8
Name: IN_STREET_ADDR
Type: 202
size: 200
value: test
Name: IN_CITY
Type: 202
size: 100
value: test
Name: IN_STATE_ID
Type: 202
size: 2
value:
Name: IN_ZIP_CD
Type: 202
size: 15
value:
Name: IN_PO_BOX
Type: 202
size: 200
value: test
Name: IN_DHL_ADDR
Type: 202
size: 200
value: test
Name: IN_FUNDING_CODE_ID
Type: 139
size: 0
value: 1
Name: IN_DODAAC
Type: 202
size: 50
value: test123
Name: OUT_SITE_ID
Type: 139
size: 0
value:
Microsoft OLE DB Provider for Oracle error '80040e14'
ORA-06550: line 1, column 143:
PLS-00103: Encountered the symbol ">" when expecting one of the following: . ( ) , * @ % & = - + < / > at
in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between || The symbol "( was inserted before ">" to continue.
ORA-06550: line 1, column 252:
PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * % & = - + < / > at
in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between || The symbol ")" was substituted for ";"
/functions/util.asp, line 336
line 336 is the .Execute, the error above is what is returned from oracle.
[Updated on: Mon, 22 September 2008 08:21] Report message to a moderator
|
|
|
|
Re: problem with stored procedure and asp page. [message #349646 is a reply to message #349640] |
Mon, 22 September 2008 08:47   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
One piece of advice I found while trawling the wed was You should check that you have set PLSQLRSet = 1 in the connect string. If you want to get a result set back from a procedure, it must be changed from its default of 0 to 1
Youer procedure does have an out parameter, so it may apply.
|
|
|
Re: problem with stored procedure and asp page. [message #349647 is a reply to message #349644] |
Mon, 22 September 2008 08:51   |
macrosblackd
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Same issue. Running the procedue in the sql+ client with/without nulls works fine. It's when I try to run it from ASP that it breaks. However, occasionally, it will work for no reason what so ever. I'm thinking that it is perhaps a bug in the ADO oracle client.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Feb 10 10:03:59 CST 2025
|