Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 26th, 2012, 04:24 PM
Registered User
 
Join Date: Feb 2012
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Calling an Oracle function from an ASP page

What is the syntax for calling an Oracle function from a Classic ASP page?

This is how I call the function:
Code:
strSQL = "Call PkgName.ProcessMyPaymentTransaction (1234567,'Credit',0,'4111111111111111','06','14',0  .10,6000677,NULL,'Y')"
Conn.Execute(strSQL)
But I get an error message:
Code:
Microsoft OLE DB Provider for Oracle error '80004005' 

ORA-06576: not a valid function or procedure name 

/Secure1/pymtprocess.asp, line 28
Any help would be greatly appreciated!! Thanks
Sean
 
Old February 27th, 2012, 04:54 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

Hi,


it's been a long time since I've done that, but looking at some old code it's basically
> creating an ADO command
> add the stored procedure call to this ADO command
> add each parameter to this ADO command

Here's a small example to get you started...
Code:
	Set cmdItem = Server.CreateObject("ADODB.Command")
	With cmdItem 
		.CommandText = "{call mySchema.myPkg.myProc(?,?)}"
		.CommandType = adCmdText
		.ActiveConnection = conn
		.Prepared = True
		Set prmItem = .CreateParameter("",adNumeric,adParamInput)
		Set prmUser = .CreateParameter("",adVarchar,adParamInput,255)
		.Parameters.Append(prmItem)
		.Parameters.Append(prmUser)
	End With

	With cmdItem 
		.Parameters(0).Value = 1
		.Parameters(1).Value = "USER1"
		Set objRs  = .Execute
	End With

	'...
Hope this helps
 
Old February 28th, 2012, 01:01 PM
Registered User
 
Join Date: Feb 2012
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for getting back to me. Do you think I'm getting that error message because I didn't specify the schema name? I noticed in your call, you are doing this:
Code:
call mySchema.myPkg.myProc(arg1, arg2, arg3)
I'm currently just calling it this way:
Code:
call myPkg.myProc(arg1, arg2, arg3)
I know your approach, using an ADO command and parameters, is way better than my simple approach but I'm just going to try the simple way to see if I can at least call the function successfully and then switch to the way you've done it.

Thanks again.
 
Old February 29th, 2012, 05:34 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

Hi,

on second read I noticed that you're trying to call an Oracle function, not a procedure. Your errror could thus be the result of not handling the return type...

This sounds like you
- either need to add an additional Oracle parameter object for the return value...
Code:
cmdItem.Parameters.Append("returnval", OracleType.Number).Direction = ParameterDirection.Output;
- or if you have a simple return type, you may also want to do this the really easy way and just call it like this:
Code:
strSQL = "SELECT PkgName.ProcessMyPaymentTransaction(1234567,'Credit',0,'4111111111111111','06','14',0.10,6000677,NULL,'Y') FROM dual"
Set oRS = Conn.Execute(strSQL)
Hope this helps
 
Old March 1st, 2012, 12:18 AM
Registered User
 
Join Date: Feb 2012
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks a bunch! You're definitely steering me in the right direction. My function does have a return value which is a table containing the following:
label VARCHAR2(100),
value VARCHAR2(600)

Would I use this?
Code:
.Parameters.Append("returnval", OracleType.Cursor).Direction = ParameterDirection.Output;
 
Old March 1st, 2012, 12:58 AM
Registered User
 
Join Date: Feb 2012
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Now I get this error message:

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Here is my code so far:
Code:
Set cmdItem = Server.CreateObject("ADODB.Command")
	With cmdItem 
		.CommandText = "{myschema.mypkg.myproc (?,?,?,?,?,?)}"
		.CommandType = adCmdStoredProc
		.ActiveConnection = ORConn
		.Prepared = True
		Set param1 = .CreateParameter("",adNumeric,adParamInput)
		Set param2 = .CreateParameter("",adVarchar,adParamInput,255)
		Set param3 = .CreateParameter("",adNumeric,adParamInput)
		Set param4 = .CreateParameter("",adVarchar,adParamInput,255)
		Set param5 = .CreateParameter("",adVarchar,adParamInput,255)
		Set param6 = .CreateParameter("",adVarchar,adParamInput,255)
			
		
		.Parameters.Append(param1)
		.Parameters.Append(param2)
		.Parameters.Append(param3)
		.Parameters.Append(param4)
		.Parameters.Append(param5)
		.Parameters.Append(param6)
		.Parameters.Append("returnval", OracleType.Cursor).Direction = ParameterDirection.Output
	End With

	With cmdItem 

		.Parameters(0).Value = 1234567
		.Parameters(1).Value = "Credit"
		.Parameters(2).Value = 0
		.Parameters(3).Value = "4111111111111111"
		.Parameters(4).Value = "06"
		.Parameters(5).Value = "14"
		
		Set objRs  = .Execute
	End With
Do I need to set the parameter for the return value too? for instance,
Code:
   Set param7 = .CreateParameter("",adVarchar,adParamInput,255)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling Oracle Function from Visual Basic 6.0 sanju_0071 Pro VB Databases 0 June 25th, 2009 07:07 PM
calling an oracle function from VB6 kalyansarkar108 VB Databases Basics 14 April 3rd, 2007 12:02 PM
calling an oracle procedure from a jsp page jasonteaboy Pro JSP 0 November 20th, 2006 03:06 PM
Calling Oracle function from Vb.net class monuindia2002 ADO.NET 1 August 4th, 2006 05:00 AM
Calling codebehind C# function from aspx page ravindrasingh77 BOOK: ASP.NET Website Programming Problem-Design-Solution 2 March 16th, 2005 12:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.