Advertisement

07.03.2008 at 04:33AM PDT, ID: 23536447
[x]
Attachment Details

Parameter problem when accessing Oracle stored procedure from ASP/ADO

Asked by Stokie69 in Active Server Pages (ASP), Oracle 10.x, PL / SQL

Tags: Oracle, 10g, VBScript, ADO, Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another., Oracle stored procedure from ASP

I have an Oracle package containing a number of calendar-related functions which I need to call from ASP. However, I am receiving an error message, "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." The error occurs at line 18 of the ASP code I have attached below, namely the creation of the return value parameter.

I've tried to be careful to place the parameters in the correct order, and with correct values for the constants etc., and I've basically run out of ideas for solving the problem. I was expecting problems related to Oracle dates, as they can be pretty awkward to deal with, but I just can't see any problem with my code in this case.

I have run the commented out lines in the VBScript and rceived a successful response, so I know the database connection is OK but, as I understand it, it is far better practice to use an ADO command object than to simply send a select statement.

Can anybody help identify the cause of this error?Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:

create or replace package body calendar_functions is 
 
	function is_working_day
		(in_check_date in corp_calendar.c_holidaydate%TYPE)
		return boolean
	is
		v_day_of_week varchar2(10);
		temp_count number;
	begin
		v_day_of_week := upper(trim(to_char(in_check_date,'DAY')));
		if v_day_of_week in ('SATURDAY','SUNDAY') then
			return false;
		else
			select nvl(count(1),0) into temp_count from corp_calendar where c_holidaydate = in_check_date; 
			if temp_count > 0 then  
				return false;
			end if;
		end if;
		return true;
	end is_working_day;
 
 
	function get_working_days
		(in_startdate in corp_calendar.c_holidaydate%TYPE,
		in_enddate in corp_calendar.c_holidaydate%TYPE,
		in_compare_times in varchar2)
		return number
	is
		v_current_checkdate corp_calendar.c_holidaydate%TYPE;
		v_current_checkdatetime corp_calendar.c_holidaydate%TYPE;
		v_current_total	number(4,0) := 0;
		v_short_startdate corp_calendar.c_holidaydate%TYPE := trunc(in_startdate,'DDD');
		v_short_enddate corp_calendar.c_holidaydate%TYPE := trunc(in_enddate,'DDD');
		v_times varchar2(1) := upper(in_compare_times);
		e_times exception;
		e_invalid_dates exception;
	begin
		if v_times <> 'Y' and v_times <> 'N' then
			raise_application_error (-20111,'Invalid argument - must be Y or N');
		end if;
		
		if in_startdate > in_enddate then
			raise_application_error (-20112,'End date must be later than start date.');
		else
			if v_short_startdate = v_short_enddate then
				return 0;
			end if;
		end if;
		v_current_checkdate := v_short_startdate + 1;
		v_current_checkdatetime := in_startdate + 1;
		while v_current_checkdate < v_short_enddate loop
			if is_working_day(v_current_checkdate) then
				v_current_total := v_current_total + 1;
			end if;
			v_current_checkdate := v_current_checkdate + 1;
			if v_times = 'Y' then
				v_current_checkdatetime := v_current_checkdatetime + 1;
			end if;
		end loop;
 
		if v_times = 'N' then
			v_current_total := v_current_total + 1;
		else
			if in_enddate > v_current_checkdatetime then
				v_current_total := v_current_total + 1;
			end if;
		end if;
		return v_current_total;
	
	exception
		when others then
			if sqlcode = -20111 or sqlcode = -20112 then
				dbms_output.put_line(sqlerrm);
			end if;
 
	end get_working_days;
 
 
	function get_end_date
		(in_startdate in corp_calendar.c_holidaydate%TYPE,
		in_working_days in number,
		in_compare_times in varchar2)
		return date
	is
		v_current_checkdatetime corp_calendar.c_holidaydate%TYPE := in_startdate;
		v_current_checkdate corp_calendar.c_holidaydate%TYPE := trunc(in_startdate,'DDD');
		v_current_total	number(4,0) := 0;
		v_times varchar2(1) := upper(in_compare_times);
		e_times exception;
		e_invalid_working_days exception;
	begin
		if v_times <> 'Y' and v_times <> 'N' then
			raise_application_error (-20111,'Invalid argument - must be Y or N');
		end if;
 
		if in_working_days < 1 then 
			raise_application_error (-20113,'Working days must be a positive number.');
		end if;
		
		while v_current_total < in_working_days loop
			v_current_checkdate := v_current_checkdate + 1;
			if v_times = 'Y' then
				v_current_checkdatetime := v_current_checkdatetime + 1;
			end if;	
			if is_working_day(v_current_checkdate) then
				v_current_total := v_current_total + 1;
			end if;
		end loop;
 
		if v_times = 'Y' then 
			return trunc(v_current_checkdatetime,'MI');
		else
			return v_current_checkdate;
		end if;
	exception
		when others then
			if sqlcode = -20111 or sqlcode = -20113 then
				dbms_output.put_line(sqlerrm);
			end if;
 
	end get_end_date;
 
	function get_start_date
		(in_working_days in number,
		in_enddate in corp_calendar.c_holidaydate%TYPE,
		in_compare_times in varchar2)
		return date
	is
		v_current_checkdatetime corp_calendar.c_holidaydate%TYPE := in_enddate;
		v_current_checkdate corp_calendar.c_holidaydate%TYPE := trunc(in_enddate,'DDD');
		v_current_total	number(4,0) := 0;
		v_times varchar2(1) := upper(in_compare_times);
		e_times exception;
		e_invalid_working_days exception;
	begin
		if v_times <> 'Y' and v_times <> 'N' then
			raise_application_error (-20111,'Invalid argument - must be Y or N');
		end if;
 
		if in_working_days < 1 then 
			raise_application_error (-20113,'Working days must be a positive number.');
		end if;
		
		while v_current_total < in_working_days loop
			v_current_checkdate := v_current_checkdate - 1;
			if v_times = 'Y' then
				v_current_checkdatetime := v_current_checkdatetime - 1;
			end if;	
			if is_working_day(v_current_checkdate) then
				v_current_total := v_current_total + 1;
			end if;
		end loop;
 
		if v_times = 'Y' then 
			return trunc(v_current_checkdatetime,'MI');
		else
			return v_current_checkdate;
		end if;
	exception
		when others then
			if sqlcode = -20111 or sqlcode = -20113 then
				dbms_output.put_line(sqlerrm);
			end if;
	end get_start_date;
 
end calendar_functions;
/
 
<%
' Database connection from an include file
%>
<!--#include file="DBConn.inc"-->
<%
	'Set rs1 = DBConn.execute("select calendar_functions.get_start_date(4,to_date('03/07/2008','DD/MM/YYYY'),'N') from dual")
	'Response.Write(rs1(0))
 
	Set oCmd = Server.CreateObject("ADODB.Command")
	oCmd.ActiveConnection = DBconn
	
	oCmd.CommandText = "calendar_functions.get_start_date"
	oCmd.CommandType = 4
	
	Set pReturn = oCmd.CreateParameter ("RETURN_VALUE",adDBTimeStamp,adParamReturnValue)
	oCmd.Parameters.Append pReturn
 
	Set pWorkingDays = oCmd.CreateParameter ("in_working_days",adNumeric,adParamInput,,4)
	oCmd.Parameters.Append pWorkingDays
	
	Set pStartDate = oCmd.CreateParameter ("in_enddate",adDBTimeStamp,adParamInput,,CDate(Date))
	oCmd.Parameters.Append pStartDate
	
	Set pTimes = oCmd.CreateParameter ("in_compare_times",adVarChar,adParamInput,,"N")
	oCmd.Parameters.Append pTimes	
	
	oCmd.Execute
	
'Set rs1 = Nothing
Set DBconn = Nothing
%>
 
Keywords: Parameter problem when accessing Or…
Title
1 How Can I Debug An ADO Using VBSc…
2 ADO and DAO
3 Pass-Through vs. ADO
4 Slow DataAccess
5 Proper ado syntax for Open recordset
6 call Oracle Function from VBScript usin…
 
Loading Advertisement...
 
[+][-]07.03.2008 at 05:27AM PDT, ID: 21924728

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 07:47AM PDT, ID: 21925977

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 09:27AM PDT, ID: 21926999

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 09:29AM PDT, ID: 21927020

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 10:04AM PDT, ID: 21927290

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 10:07AM PDT, ID: 21927308

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 10:14AM PDT, ID: 21927363

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Active Server Pages (ASP), Oracle 10.x, PL / SQL
Tags: Oracle, 10g, VBScript, ADO, Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another., Oracle stored procedure from ASP
Sign Up Now!
Solution Provided By: samic400
Participating Experts: 2
Solution Grade: A
 
 
[+][-]07.03.2008 at 10:38AM PDT, ID: 21927563

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 10:40AM PDT, ID: 21927586

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628