Home » SQL & PL/SQL » SQL & PL/SQL » problem with stored procedure and asp page. (Oracle 10, windows)
icon5.gif  problem with stored procedure and asp page. [message #349056] Thu, 18 September 2008 13:41 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #349638 is a reply to message #349634] Mon, 22 September 2008 08:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have you got anyway of dumping the contents of the bind variables?

DO you have a set of input data that will consistently reproduce the problem?
Re: problem with stored procedure and asp page. [message #349640 is a reply to message #349638] Mon, 22 September 2008 08:15 Go to previous messageGo to next message
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 #349644 is a reply to message #349640] Mon, 22 September 2008 08:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you get if you replace the null values with data?
Re: problem with stored procedure and asp page. [message #349646 is a reply to message #349640] Mon, 22 September 2008 08:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: problem with stored procedure and asp page. [message #349649 is a reply to message #349056] Mon, 22 September 2008 08:59 Go to previous messageGo to next message
macrosblackd
Messages: 8
Registered: September 2008
Junior Member
I added PLSQLSet=1 to my connection string and still recieved the errors.
Re: problem with stored procedure and asp page. [message #349654 is a reply to message #349649] Mon, 22 September 2008 09:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's something wrong with the SQL that is getting passed to the Oracle Db.
You might be better off looking or an ASP forum - see if anyone else has hit the same problem
Re: problem with stored procedure and asp page. [message #349677 is a reply to message #349056] Mon, 22 September 2008 11:05 Go to previous messageGo to next message
macrosblackd
Messages: 8
Registered: September 2008
Junior Member
Yea, I was afraid of that. I've been scouring the net for anything I can find on this error with no luck though.
Re: problem with stored procedure and asp page. [message #349784 is a reply to message #349677] Tue, 23 September 2008 02:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is it just this procedure you're having trouble with, or all calls to stored procedures?
Re: problem with stored procedure and asp page. [message #349894 is a reply to message #349056] Tue, 23 September 2008 07:20 Go to previous message
macrosblackd
Messages: 8
Registered: September 2008
Junior Member
It is just 2 stored procedures i've had trouble with, sp_addsite and sp_updatesite. both use the sites table.
Previous Topic: Needs sum of multiple columns in a table
Next Topic: fetch insert and delete record + stored procedure
Goto Forum:
  


Current Time: Sun Dec 11 07:58:01 CST 2016

Total time taken to generate the page: 0.13500 seconds