oracle 存储过程: PACKAGE Test_pele AS
TYPE mbcur IS REF CURSOR;
PROCEDURE Test_p(p_cursor OUT mbcur,
inmobileno IN NUMBER);
END Test_pele;
PACKAGE BODY Test_pele AS
PROCEDURE Test_p(p_cursor OUT mbcur,
inmobileno IN NUMBER) IS
BEGIN
OPEN p_cursor FOR
SELECT SM_MODEL,SM_BRAND
FROM SM_SUPPORTMOBILE
WHERE SM_ID = inmobileno;
END Test_p;
END Test_pele;
客户端代码:try
{
// 创建数据库连接对象
if ( FAILED(m_pConnection.CreateInstance("ADODB.Connection")) )
{
cout<<"创建数据库连接对象失败"<<endl;
throw;
} // 选择数据库连接文件
m_pConnection->ConnectionString = "File Name=D:\\Myfile\\CLanguage\\connect.UDL";
// 开始连接数据库
if ( FAILED(m_pConnection->Open("","","",NULL)) )
{
cout<<"连接数据库失败"<<endl;
throw;
} // 创建命令对象
if ( FAILED(m_pCommand.CreateInstance("ADODB.Command")) )
{
cout<<"创建命令对象失败"<<endl;
throw;
}
m_pParameter = m_pCommand->CreateParameter("id",
adNumeric,
adParamInput,
sizeof(long)+1,
_variant_t(long(1))
);
m_pCommand->Parameters->Append(m_pParameter);
m_pCommand->ActiveConnection = m_pConnection;
m_pCommand->CommandText = "{CALL Test_pele.Test_p(?,?)}";
// 开始事务
m_pConnection->BeginTrans();
// 执行
_variant_t RecordsAffected; _RecordsetPtr m_pRecordSet;
m_pRecordSet = m_pCommand->Execute(&RecordsAffected,NULL, adCmdStoredProc); // 出错 cout<<"Execute command succeeded"<<endl;
}
catch(...)
{
PrintProviderError(m_pConnection);
m_pConnection->RollbackTrans();
}
在执行m_pCommand->Execute(&RecordsAffected,NULL, adCmdStoredProc)的时候,总是会出错(抛出异常),
说“至少一个参数未被指定值”。
我的存储过程之后两个参数,而Oracle的oledb文档已经说了customer是不用对REF Cursor bind 一个参数的。
我这段代码错在哪里呢?请各位指点迷津。
TYPE mbcur IS REF CURSOR;
PROCEDURE Test_p(p_cursor OUT mbcur,
inmobileno IN NUMBER);
END Test_pele;
PACKAGE BODY Test_pele AS
PROCEDURE Test_p(p_cursor OUT mbcur,
inmobileno IN NUMBER) IS
BEGIN
OPEN p_cursor FOR
SELECT SM_MODEL,SM_BRAND
FROM SM_SUPPORTMOBILE
WHERE SM_ID = inmobileno;
END Test_p;
END Test_pele;
客户端代码:try
{
// 创建数据库连接对象
if ( FAILED(m_pConnection.CreateInstance("ADODB.Connection")) )
{
cout<<"创建数据库连接对象失败"<<endl;
throw;
} // 选择数据库连接文件
m_pConnection->ConnectionString = "File Name=D:\\Myfile\\CLanguage\\connect.UDL";
// 开始连接数据库
if ( FAILED(m_pConnection->Open("","","",NULL)) )
{
cout<<"连接数据库失败"<<endl;
throw;
} // 创建命令对象
if ( FAILED(m_pCommand.CreateInstance("ADODB.Command")) )
{
cout<<"创建命令对象失败"<<endl;
throw;
}
m_pParameter = m_pCommand->CreateParameter("id",
adNumeric,
adParamInput,
sizeof(long)+1,
_variant_t(long(1))
);
m_pCommand->Parameters->Append(m_pParameter);
m_pCommand->ActiveConnection = m_pConnection;
m_pCommand->CommandText = "{CALL Test_pele.Test_p(?,?)}";
// 开始事务
m_pConnection->BeginTrans();
// 执行
_variant_t RecordsAffected; _RecordsetPtr m_pRecordSet;
m_pRecordSet = m_pCommand->Execute(&RecordsAffected,NULL, adCmdStoredProc); // 出错 cout<<"Execute command succeeded"<<endl;
}
catch(...)
{
PrintProviderError(m_pConnection);
m_pConnection->RollbackTrans();
}
在执行m_pCommand->Execute(&RecordsAffected,NULL, adCmdStoredProc)的时候,总是会出错(抛出异常),
说“至少一个参数未被指定值”。
我的存储过程之后两个参数,而Oracle的oledb文档已经说了customer是不用对REF Cursor bind 一个参数的。
我这段代码错在哪里呢?请各位指点迷津。
解决方案 »
- 一直想不通,为什么苹果手机现在这么受欢迎?
- 怎么把一个浮点数格式成带c位小数的浮点数啊?
- 一次性打印excel文件中所有的sheet
- c 16位MD5加密算法 在线等
- 将线程函数声明为类的成员函数的问题(在线等)
- 抽屉视图,如何将焦点设置到指定视图的某个节点上?
- #if !defined(DEMO)中的DEMO是什么东东阿,烦死我了
- 求Visual C++ 6.0数据库系统开发实例导航(人民邮电出版社)的源码和电子书
- 急用!!!! 有谁知道msdn 2001的下载地址啊(http协议的)
- opencv-python ANN 神经网络训练 minist 数据集, 训练后Predict的返回值总是 nan 空矩阵,劳烦大神看看哪里出了问题
- 求购DXF格式转常用矢量格式(E00或Shapefile或MIF)代码或命令行程序!
- 如何修改一个对话框的属性,使其只能被上下拉伸但不能左右拉伸?
type TypCur is ref cursor;
procedure pro_test(p_id number,result in out varchar2, p_cur out TypCur);
end for_result;
create or replace package body for_result is
procedure pro_test(p_id number,result in out varchar2, p_cur out TypCur)
is
begin
open p_cur for select * from a where id>p_id;
result :='pretty pretty gril';
end;
end for_result;
COracleApp *papp=(COracleApp*)AfxGetApp();
try
{
_bstr_t sqltext="{call for_result.pro_test(?,?,{resultset 0, p_cur})}";
_CommandPtr m_commandptr;
_RecordsetPtr m_pUserSet;
m_commandptr.CreateInstance (__uuidof(Command));
m_pUserSet.CreateInstance (__uuidof(Recordset));
m_commandptr->ActiveConnection =papp->m_connectionptr ;
m_commandptr->CommandText =sqltext;
m_commandptr->CommandType =adCmdText;
_ParameterPtr ParameterPtr1=NULL,ParameterPtr2=NULL;
ParameterPtr1=m_commandptr->CreateParameter ("id",adInteger,adParamInput,4,COleVariant((short)1));
m_commandptr->Parameters ->Append (ParameterPtr1);
ParameterPtr2=m_commandptr->CreateParameter ("tablename",adVarChar,adParamInputOutput,255,COleVariant("ff" ));
m_commandptr->Parameters ->Append (ParameterPtr2);
m_pUserSet=m_commandptr->Execute (NULL,NULL,adCmdUnknown);
//如果说没有结果集,则换成
//m_commandptr->Execute (NULL,NULL,adCmdUnknown);
// 至于你说的返回成功标志,可以放在输出参数里
_bstr_t result=m_commandptr->Parameters->GetItem("tablename")->GetValue(); if (!m_pUserSet->adoEOF )
{
long iii;
long count=m_pUserSet->Fields ->GetCount ();
iii=m_pUserSet->GetCollect (COleVariant((short)0)).intVal ;
}
}
catch(...)
{
papp->DisplayAdoError();
}
{
try
{ m_connectionptr.CreateInstance (__uuidof(Connection));
//m_connectionptr->CommandTimeout =30;
_bstr_t strConnect = _T("Provider=MSDAORA.1;Password=manager;User ID=system;Data Source=cdwz;Persist Security Info=True");
m_connectionptr->Open (strConnect,"system","manager",-1);
}
catch(...)
{
DisplayAdoError();
} return 0;
}long COracleApp::DisplayAdoError()
{
long errorcount=m_connectionptr->GetErrors ()->GetCount ();
_bstr_t add;
CString errormsg;
for (short i=0;i<errorcount;i++)
{
add=m_connectionptr->GetErrors ()->GetItem (COleVariant((short)i))->GetDescription ();
errormsg.Insert (0,(char*)add);
errormsg+="\r";
}
AfxMessageBox(errormsg);
return 0;
}
那个时候还在狂用mfc.:(
m_pCommand->CommandText = "{CALL Test_pele.Test_p(?,{resultset 0,p_cursor})}"在执行m_pCommand->Execute的时候,出错:
Error number: -2147217900
ORA-06550: line 1, column 27:
PLS-00103: Encountered the symbol "{" when expecting one of the following: ( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>好像是说语法不正确。
是怎么回事,能帮帮我吗?
Cause: A PL/SQL compilation error has occurred. The numbers given for line and column are the location in the PL/SQL block where the error occurred.
Action: Refer to the following PL/SQL messages for more information about the error. PLS-00103 found 'string' but expected one of the following: 'string'"},Cause: This error message is from the parser. It found a token (language element) that is inappropriate in this context.Action: Check previous tokens as well as the one given in the error message. The line and column numbers given in the error message refer to the end of the faulty language construct
我手头上没有PL/SQL的书。应该怎么改呢?