调用光标时出现PLS-00306错误

I think I might be missing something here. Here is the relevant part of the trigger:

我想我可能会在这里遗漏一些东西。以下是触发器的相关部分:

CURSOR columnNames (inTableName IN VARCHAR2) IS 
   SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = inTableName;
/* Removed for brevity */
OPEN columnNames('TEMP');

And here is the error message that I'm getting back,

以下是我要回复的错误消息,

27/20   PLS-00306: wrong number or types of arguments in call to 'COLUMNNAMES'
27/2    PL/SQL: Statement ignored

If I am understanding the documentation correctly, that should work, but since it is not I must be doing something wrong. Any ideas?

如果我正确理解文档,那应该有用,但是因为它不是我必须做错事。有任何想法吗?

@Matthew - I appreciate the help, but the reason that I am confused is because this bit of code isn't working for me and is raising the errors referenced. We have other triggers in the database with code almost exactly the as that so I'm not sure if it is something that I did wrong, or something with how I am trying to store the trigger, etc.

@Matthew - 我很感激帮助,但我感到困惑的原因是因为这段代码对我不起作用并引发了引用的错误。我们在数据库中有其他触发器,其代码几乎完全相同,所以我不确定它是否是我做错了什么,或者是我试图存储触发器的东西等等。

@Matthew - Well, now I get to feel embarrassed. I did a copy/paste of the code that you provided into a new trigger and it worked fine. So I went back into the original trigger and tried it and received the error message again, except this time I started to delete stuff out of the trigger and after getting rid of this line,

@Matthew - 好吧,现在我感到很尴尬。我将您提供的代码复制/粘贴到新触发器中并且工作正常。所以我回到原始触发器并尝试了它并再次收到错误消息,除了这次我开始从触发器中删除东西并且在删除此行之后,

FOR columnName IN columnNames LOOP

Things saved fine. So it turns out that where I thought the error was, wasn't actually were the error was.

事情很好。事实证明,我认为错误在哪里,实际上并不是错误。

3 个解决方案

#1

@Rob

If you cut/paste the code I have here, does it work?

如果您剪切/粘贴我在这里的代码,它是否有效?

How/where are you calling your code? its in a trigger is it?

您如何/在何处调用您的代码?它的触发器是什么?

The query you have written here, is that actually the code producing the error, or just an example (eg, can you reproduce the error with the query you have above)

您在此处编写的查询实际上是产生错误的代码,或者只是一个示例(例如,您是否可以使用上面的查询重现错误)

#2

To clarify the cause of the issue. As you state

澄清问题的原因。如你所述

OPEN columnNames('TEMP');

worked while

FOR columnName IN columnNames LOOP

FOR columnName IN columnNames LOOP

did not. The FOR statement would work fine if it also included the parameter like so:

没有。如果FOR语句也包含参数,那么它将正常工作:

FOR columnName IN columnNames('TEMP') LOOP

FOR columnName IN columnNames('TEMP')LOOP

You don't show the code where you fetch the rows so I can't tell your purpose, but where I work OPEN is commonly used to fetch the first row (in this case, the first column name of the given table) while the FOR is used to iterate through all returned rows.

您没有显示获取行的代码,因此我无法告诉您的目的,但我工作的地方OPEN通常用于获取第一行(在本例中,是给定表的第一列名称),而FOR用于遍历所有返回的行。

@Rob's comment. I'm not allowed to comment so updating here instead. The missing parameter is what I describe above. You added a response stating you simply deleted the FOR loop. It did not look like you, at the time, understood why deleting it made a difference. Which is why I attempted to explain since, depending on your need, the FOR loop might be a better solution.

@Rob的评论。我不允许评论,所以在这里更新。缺少的参数是我上面描述的。您添加了一个响应,表明您只是删除了FOR循环。当时看起来不像你,理解为什么删除它会产生影响。这就是我尝试解释的原因,因为根据您的需要,FOR循环可能是更好的解决方案。

#3

Works fine for me.

对我来说很好。

create or replace procedure so_test_procedure as 
 CURSOR columnNames (inTableName IN VARCHAR2) IS 
   SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = inTableName; 
BEGIN      
 OPEN columnNames('TEMP');
 CLOSE columnNames;
END;

procedure so_test_procedure Compiled.
execute so_test_procedure();

anonymous block completed

#1