简单的Oracle过程失败 [英] Simple Oracle Procedure Failing
问题描述
我有一个非常简单的测试过程:
I have a very simple test proc:
create or replace PROCEDURE TestSproc
(userName in VARCHAR2, p_Test OUT SYS_REFCURSOR)IS
BEGIN
OPEN p_Test FOR
SELECT * FROM Test_Table
WHERE name = userName ;
END TestSproc;
当我在proc页面上的sqldeveloper中运行proc(ctrl f10)时,我得到了我期望的结果.但是,当我尝试使用以下查询调用proc时,出现了错误:
When I run the proc (ctrl f10) in sqldeveloper on the proc page I get the result I would expect. But when I try to call the proc with the below query I get the error:
从命令第1行开始的错误-开始DB.TestSproc('Phil');结束;
错误报告-ORA-06550:第2行,第1列:
PLS-00306:调用"TestSproc"时参数的数量或类型错误
ORA-06550:第2行,第1列:PL/SQL:语句被忽略
06550.00000-%s行,%s列:\ n%s"
*原因:通常是PL/SQL编译错误.
*操作:
Error starting at line : 1 in command - begin DB.TestSproc('Phil'); end;
Error report - ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'TestSproc'
ORA-06550: line 2, column 1: PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
begin
DB.TestSproc('Phil');
end;
谁能给我指出正确的方向.
Can anyone please point me in the correct direction.
编辑
在SQL Server中,我会简单地做:
In SQL Server I would simply do:
USE DB;
GO
EXEC dbo.TestSproc@Name= 'Phil';
推荐答案
假定您将Oracle 12c与12c客户端一起使用:
Assuming you are using Oracle 12c with a 12c client:
create or replace procedure testsproc
( username in varchar2 )
as
resultset sys_refcursor;
begin
open resultset for
select * from test_table
where name = username;
dbms_sql.return_result(resultset);
end testsproc;
然后用
exec testsproc('Phil')
或
call testsproc('Phil');
或
begin
testsproc('Phil');
end;
取决于您调用它的方式.
depending on what you are calling it from.
这篇关于简单的Oracle过程失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!