PLS-00306 error on call to cursor

Question

I'm fairly new to Oracle triggers and PL/SQL still, but 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?

Answer 1

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

Answer 2

@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, ect.

Answer 3

@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)

Answer 4

@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,

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.

Thanks for the help, looks like this was one of those situations where a second set of eyes was needed to spot the obvious.

Answer 5

To clarify the cause of the issue. As you state

OPEN columnNames('TEMP');

worked while

FOR columnName IN columnNames LOOP

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

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.

@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.