PLS-00306 error on call to cursor

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?

From stackoverflow Rob

• 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
  

  From Matthew Watson

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

  From Rob

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

  From Matthew Watson

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

  From Rob

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

  Rob : Frode - The reason I was getting the error was because I was trying to make a call to a parametrized query in the FOR LOOP without providing a parameter.
  From Frode Hauge