新伤未治,旧痛复发~!beckham兄还得麻烦你: 上面的问题是数据库连接的问题,但数据库搞定之好,又出现了新问题,提示信息如下:OraOLEDB 错误 '80040e14' ORA-06550: 第 1 行, 第 7 列: PLS-00306: 调用 'GET' 时参数个数或类型错误 ORA-06550: 第 1 行, 第 7 列: PL/SQL: Statement ignored /waterres/ysq/rhsl/NB_PL_TEST.asp,行31 我测试所用的过程你应该看着很熟,就是下面这段: CREATE OR REPLACE PACKAGE BODY "PKG_TEST" AS PROCEDURE get (p_id NUMBER, p_rc OUT myrctype) IS sqlstr VARCHAR2 (500); BEGIN IF p_id = 0 THEN sqlstr := 'SELECT ID, NAME, sex, address, postcode, birthday FROM NB_TEST_SQL'; OPEN p_rc FOR sqlstr;
ELSE sqlstr := 'select id,name,sex,address,postcode,birthday from NB_TEST_SQL where id=:w_id'; OPEN p_rc FOR sqlstr USING p_id; END IF; END get; END pkg_test; 在ASP中调用如下: <% Set Conn=Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset")
在ASP中调用动态ORACLE包\过程:1.所查视图表结构: 名称 空? 类型 ----------------------------------------- -------- ---------------------------- ID NUMBER XK_SX NUMBER XK_WSX_DJ NUMBER XK_WSX_XJ NUMBER XK_WSX_HJ NUMBER F_ZS NUMBER F_WZS_DJ NUMBER F_WZS_XJ NUMBER F_WZS_HJ NUMBER SW_CS_DJ NUMBER SW_CS_XJ NUMBER SW_CS_HJ NUMBER BZ CHAR(100) YY CHAR(4) XZQ NUMBER WRR2 NUMBER WRR3 NUMBER WRR2NAME CHAR(50) WRR3NAME CHAR(50) ADDVNM CHAR(9)2.在ORACLE中包的定义:CREATE OR REPLACE PACKAGE nb_supFind AS AS TYPE myrctype IS REF CURSOR;
PROCEDURE RecSel( p_StartDate IN CHAR, p_EndDate IN CHAR, p_Region IN VARCHAR2, --分区内容 p_flag IN NUMBER, --标识是行政区/二级区/三级区 p_col IN VARCHAR2, --选中项目名 p_table IN VARCHAR2, --操作表名 v_ADDV OUT myrctype --作为输出的游标 ); END nb_supFind;CREATE OR REPLACE PROCEDURE NB_SURERFIND ( --参数声明 p_StartDate IN VIEW_NB_TYGL.YY%TYPE, p_EndDate IN VIEW_NB_TYGL.YY%TYPE, p_Region IN VARCHAR2, --分区内容 p_flag IN NUMBER, --标识是行政区/二级区/三级区 p_item IN NUMBER, p_table IN VARCHAR2, )
AS
PROCEDURE RecSel( p_StartDate IN CHAR, p_EndDate IN CHAR, p_Region IN VARCHAR2, --分区内容 p_flag IN NUMBER, --标识是行政区/二级区/三级区 p_col IN VARCHAR2, --选中项目名 p_table IN VARCHAR2, --操作表名 v_ADDV OUT myrctype --作为输出的游标 ) IS
strsql VARCHAR2(500);
BEGIN IF p_flag = 1 then if p_Region = 'NULL' then strsql:='SELECT ADDVNM,YY,SUM('||p_col||') FROM '||p_table|| ' WHERE YY <= '||p_EndDate||' AND YY>='|| p_StartDate||' GROUP BY ADDVNM,YY'; else strsql:='SELECT ADDVNM,YY,SUM('||p_col||') FROM '||p_table|| ' WHERE YY <= TO_NUMBER('||p_EndDate||') AND YY>=TO_NUMBER('|| p_StartDate||') AND ADDVNM='''||p_Region|| ''' GROUP BY ADDVNM,YY'; end if; DBMS_OUTPUT.PUT_LINE(strsql); --打开动态游标 OPEN v_ADDV FOR strsql; END IF; END RecSel; END nb_supFind; 3.在SQL PLUS中测试: set serveroutput on DECLARE p_rc NB_SUPFIND.myrctype; str varchar2(50); BEGIN NB_SUPFIND.RecSel('2000','2000','北京',1,'YSQ_SL','VIEW_NB_QSXK',p_rc); LOOP FETCH p_rc INTO str; DBMS_OUTPUT.PUT_LINE(str); EXIT WHEN p_rc%NOTFOUND; END LOOP; END;成功!4.在ASP里的调用 Set Conn=Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset")
CMD.Parameters.Append CMD.CreateParameter("p_StartDate",129,1,4,"2000")'开始年份 CMD.Parameters.Append CMD.CreateParameter("p_EndDate",129,1,4,EndYr) '结束年份 CMD.Parameters.Append CMD.CreateParameter("p_Region",129,1,9,Addv) '行政区名 CMD.Parameters.Append CMD.CreateParameter("p_flag",3,1,4,1) '标识分区 CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,50,Item) '选中项目名 CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,100,Tname) '得到表名 set rs.Source = CMD on error resume next set rs=CMD.Execute
if err.number <> 0 then response.Write err.Description response.End end if
rs.Open()
以前不是已讨论过了吗,参数是针对对应oracle数据类型: //---- DataTypeEnum Values ---- var adEmpty = 0; var adTinyInt = 16; var adSmallInt = 2; var adInteger = 3; var adBigInt = 20; var adUnsignedTinyInt = 17; var adUnsignedSmallInt = 18; var adUnsignedInt = 19; var adUnsignedBigInt = 21; var adSingle = 4; var adDouble = 5; var adCurrency = 6; var adDecimal = 14; var adNumeric = 131; var adBoolean = 11; var adError = 10; var adUserDefined = 132; var adVariant = 12; var adIDispatch = 9; var adIUnknown = 13; var adGUID = 72; var adDate = 7; var adDBDate = 133; var adDBTime = 134; var adDBTimeStamp = 135; var adBSTR = 8; var adChar = 129; var adVarChar = 200; var adLongVarChar = 201; var adWChar = 130; var adVarWChar = 202; var adLongVarWChar = 203; var adBinary = 128; var adVarBinary = 204; var adLongVarBinary = 205; var adChapter = 136; var adFileTime = 64; var adDBFileTime = 137; var adPropVariant = 138; var adVarNumeric = 139;
上面的问题是数据库连接的问题,但数据库搞定之好,又出现了新问题,提示信息如下:OraOLEDB 错误 '80040e14' ORA-06550: 第 1 行, 第 7 列: PLS-00306: 调用 'GET' 时参数个数或类型错误 ORA-06550: 第 1 行, 第 7 列: PL/SQL: Statement ignored /waterres/ysq/rhsl/NB_PL_TEST.asp,行31 我测试所用的过程你应该看着很熟,就是下面这段:
CREATE OR REPLACE PACKAGE BODY "PKG_TEST"
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
sqlstr := 'SELECT ID, NAME, sex, address, postcode, birthday
FROM NB_TEST_SQL';
OPEN p_rc FOR sqlstr;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from NB_TEST_SQL where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
在ASP中调用如下:
<%
Set Conn=Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
conn.Open "Provider=oraoledb.oracle;Data Source=ORADB_10.3.2.8;User ID=WATER;password=WATER"
set cm1 = Server.CreateObject("ADODB.Command")
cm1.ActiveConnection = conn cm1.CommandType = 1
cm1.CommandTimeout = 0
cm1.Prepared = true
cm1.CommandText = "{call pkg_test.get(?)}"
set para = cm1.Parameters
para.Append cm1.CreateParameter("p_id",3,1,4,1)
set rs.Source = cm1
rs.Open() response.write "SUCCESS" & rs(0)%>
执行脚本后就出现上面的错,把游标类型的输出去了,就没问题,一加上就报类型或个数不对,你以前测试成功的游标是怎样的?
还有call pkg_test.get(?)里面的?到底是几个?包括输出吗?通过其它例子的测试,是包括输出的,但游标就不好弄了,请beckham兄费心帮忙看看吧!
名称 空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
XK_SX NUMBER
XK_WSX_DJ NUMBER
XK_WSX_XJ NUMBER
XK_WSX_HJ NUMBER
F_ZS NUMBER
F_WZS_DJ NUMBER
F_WZS_XJ NUMBER
F_WZS_HJ NUMBER
SW_CS_DJ NUMBER
SW_CS_XJ NUMBER
SW_CS_HJ NUMBER
BZ CHAR(100)
YY CHAR(4)
XZQ NUMBER
WRR2 NUMBER
WRR3 NUMBER
WRR2NAME CHAR(50)
WRR3NAME CHAR(50)
ADDVNM CHAR(9)2.在ORACLE中包的定义:CREATE OR REPLACE PACKAGE nb_supFind
AS
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE RecSel( p_StartDate IN CHAR,
p_EndDate IN CHAR,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_col IN VARCHAR2, --选中项目名
p_table IN VARCHAR2, --操作表名
v_ADDV OUT myrctype --作为输出的游标
);
END nb_supFind;CREATE OR REPLACE PROCEDURE NB_SURERFIND (
--参数声明
p_StartDate IN VIEW_NB_TYGL.YY%TYPE,
p_EndDate IN VIEW_NB_TYGL.YY%TYPE,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_item IN NUMBER,
p_table IN VARCHAR2,
)
AS
PROCEDURE RecSel(
p_StartDate IN CHAR,
p_EndDate IN CHAR,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_col IN VARCHAR2, --选中项目名
p_table IN VARCHAR2, --操作表名
v_ADDV OUT myrctype --作为输出的游标
) IS
strsql VARCHAR2(500);
BEGIN
IF p_flag = 1 then
if p_Region = 'NULL' then
strsql:='SELECT ADDVNM,YY,SUM('||p_col||')
FROM '||p_table||
' WHERE YY <= '||p_EndDate||' AND YY>='|| p_StartDate||'
GROUP BY ADDVNM,YY';
else
strsql:='SELECT ADDVNM,YY,SUM('||p_col||')
FROM '||p_table||
' WHERE YY <= TO_NUMBER('||p_EndDate||') AND YY>=TO_NUMBER('|| p_StartDate||') AND ADDVNM='''||p_Region||
''' GROUP BY ADDVNM,YY';
end if;
DBMS_OUTPUT.PUT_LINE(strsql);
--打开动态游标
OPEN v_ADDV FOR strsql;
END IF;
END RecSel;
END nb_supFind; 3.在SQL PLUS中测试:
set serveroutput on
DECLARE
p_rc NB_SUPFIND.myrctype;
str varchar2(50);
BEGIN
NB_SUPFIND.RecSel('2000','2000','北京',1,'YSQ_SL','VIEW_NB_QSXK',p_rc);
LOOP
FETCH p_rc INTO str;
DBMS_OUTPUT.PUT_LINE(str);
EXIT WHEN p_rc%NOTFOUND;
END LOOP;
END;成功!4.在ASP里的调用
Set Conn=Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
'以下两句含在globo文件中
connstr="Provider=MSDAORA;Password=water;User ID=water;Data Source=oradb_netserver"
Session("connstr")=connstr
conn.Open Session("connstr") Set CMD=Server.CreateObject("ADODB.Command")
CMD.ActiveConnection = conn
CMD.CommandType = adCmdText
CMD.CommandTimeout = 0
CMD.Prepared = true CMD.CommandText = "{call nb_supFind.RecSel(?,?,?,?,?,?)}" 这里只写输入不写输出
CMD.Parameters.Append CMD.CreateParameter("p_StartDate",129,1,4,"2000")'开始年份
CMD.Parameters.Append CMD.CreateParameter("p_EndDate",129,1,4,EndYr) '结束年份
CMD.Parameters.Append CMD.CreateParameter("p_Region",129,1,9,Addv) '行政区名
CMD.Parameters.Append CMD.CreateParameter("p_flag",3,1,4,1) '标识分区
CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,50,Item) '选中项目名
CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,100,Tname) '得到表名 set rs.Source = CMD
on error resume next
set rs=CMD.Execute
if err.number <> 0 then
response.Write err.Description
response.End
end if
rs.Open()
//---- DataTypeEnum Values ----
var adEmpty = 0;
var adTinyInt = 16;
var adSmallInt = 2;
var adInteger = 3;
var adBigInt = 20;
var adUnsignedTinyInt = 17;
var adUnsignedSmallInt = 18;
var adUnsignedInt = 19;
var adUnsignedBigInt = 21;
var adSingle = 4;
var adDouble = 5;
var adCurrency = 6;
var adDecimal = 14;
var adNumeric = 131;
var adBoolean = 11;
var adError = 10;
var adUserDefined = 132;
var adVariant = 12;
var adIDispatch = 9;
var adIUnknown = 13;
var adGUID = 72;
var adDate = 7;
var adDBDate = 133;
var adDBTime = 134;
var adDBTimeStamp = 135;
var adBSTR = 8;
var adChar = 129;
var adVarChar = 200;
var adLongVarChar = 201;
var adWChar = 130;
var adVarWChar = 202;
var adLongVarWChar = 203;
var adBinary = 128;
var adVarBinary = 204;
var adLongVarBinary = 205;
var adChapter = 136;
var adFileTime = 64;
var adDBFileTime = 137;
var adPropVariant = 138;
var adVarNumeric = 139;
调用 'RECSEL3_2' 时参数个数或类型错误 ORA-06550: 第 1 行, 第 7 列: PL/SQL: Statement ignored 报错行数就是 set rs=CMD.Execute我又做了测试,把输出的游标参数去掉就没问题,一加上就不行,就报参数类型和个数的错误,上次我项目里没有用到这个,所以没做详细的测试,你是否有过成功的读出了游标里记录的测试呢?
CMD.CommandText = "{call nb_supFind.RecSel(?,?,?,?,?,?)}" 这里只写输入不写输出这句要写对就可以了,现在上网不方便,手上没有资料,与你交流比较困难,晚上才有时间上网