0

I have declared a SELECT query into a cursor which takes 1 argument - the output of which is 1 row and 1 column containing an integer value. I am attempting to open this cursor to store the result into a variable and later print it.

But it is throwing an error: PLS-00306: wrong number or types of arguments in call to 'c1'

I am not sure what I am doing wrong, I have tried declaring return types/ arguments too, but nothing works. Please help.

Here's what I am working with:

DECLARE    
dept_no int ;    
cursor c1(Var1 date)        
IS            
   SELECT dept_no                
    from Employees                    
      where academic_period = 202050                        
         and trunc(r_date) = to_date(:Var1, 'yyyy-mm-dd');

BEGIN    
   dbms_output.put_line('Department Number: ');    
   OPEN c1;    
   FETCH c1 INTO dept_no;    
   CLOSE c1;    
END;
Improve this question
8
  • how many results does return the cursor c1? - if it's more than one, you have to change the dept_no variable type. Commented Mar 5, 2020 at 19:30
  • @Marco Aurelio Fernandez Reyes It returns one row and 1 column, so essentially just one result which is a number Commented Mar 5, 2020 at 19:45
  • 1
    Just a couple of side observations, not completely relevant to the immediate problem under consideration. First, if you know the query (cursor) will ever only return exactly on row, why do you even need a cursor at all? Second, you column reference "trunc(date)", indicates a column named DATE. A very bad choice of names. In fact, when I try to create a column named DATE, oracle tells me 'invalid identifier'. So it appears the code you presented is not the actual code. Commented Mar 5, 2020 at 20:26
  • @EdStevens - To address your point of why have a cursor at all - In my actual function, I have more than 1 queries and about 5-6 cases in which I would chose to execute them. I am still a novice at plsql - but based on my little research, having cursors when dealing with more than one queries in a function would be pretty organized. Please correct me if I am wrong here, happy to get inputs. On the column name date - It is not the actual code, for privacy and security reasons, of course :). But you are right, just corrected it in the question. Thanks for pointing that out. Commented Mar 6, 2020 at 17:11
  • 1
    "date" date - if you declare the column name like this - you should be able to accept it without throwing an error. -- Just because you can do something, it does not follow that you "should". Forcing the column name to be lower-case is bad in and of itself. Much worse when it is done just to avoid conflict with a reserved word. Commented Mar 6, 2020 at 19:50

2 Answers 2

Reset to default
2

You need to open the cursor like this :

OPEN c1(value);

Here is one example how your code "should" look like:

declare

cursor c1(Var1 date)       
IS   
SELECT dept_no                
from Employees                    
where academic_period = 202050                       
and trunc("date") = Var1; -- I have removed ":" from the variable call
-- I have also removed the format Check if you actually need it

v_dept_no int ;

BEGIN
   dbms_output.put_line('Department Number: ');
   OPEN c1(to_date('01-02-2020','dd-mm-yyyy'));  --I have formatred the value I am sending 
   FETCH c1 INTO v_dept_no;   
   CLOSE c1;
    dbms_output.put_line(v_dept_no); -- I have shown you the value reutrned just for show
END;
/

Here is a demo

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

thank you so much, I was declaring the parameter for c1 in the wrong place, also , formatting the input into date format makes sense. This certainly helps.
Can I pass Var1 into the Begin -End clause instead of declaring an actual value of the Var1? I want this cursor to be a part of a function and where I am passing the cursor value into the declaration through a prompt window, already.
Hi @sweta24 , can I ask why is my answer not correct anymore ? :)
You can pass the variable to the function and then use that function parameter to call the cursor. I am not sure if this is what you have asked me :)
About the function-cursor question, I shall try and let you know if I got it. Thanks a lot!
|
1

Your cursor declaration requires a date input parameter:

cursor c1(Var1 date)
            
IS
                ...

But then you call it without a date:

OPEN c1;


Fix one, or the other!

Improve this answer

1 Comment

Thank you! fixed it

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.