Home » SQL & PL/SQL » SQL & PL/SQL » How to get output for procedure using optional parameter and ref cursor (oracle 9.0.2.8)
How to get output for procedure using optional parameter and ref cursor [message #319546] Mon, 12 May 2008 02:41 Go to next message
NIRVANA
Messages: 11
Registered: May 2008
Junior Member
Hi! I have written a procedure.And it compiled with no errors.

Here I am using some optional parameter. How can I see the result for this below procedure.
whats the syntax for getting the output? I need help.

CREATE OR REPLACE PROCEDURE GET_COUNT 
	( P_SCHOOL_CD    VARCHAR2(4),
	  P_DEPT_NO VARCHAR2(4) DEFAULT NULL,,
	  P_STD_ID    NUMBER,	  
	  C1 OUT GlobalPkg.REF_C1
	 )
 IS 
 	
 BEGIN
    IF p_dept_no IS NULL THEN
    OPEN C1 FOR
		SELECT DEPT_NO, COUNT(*) FROM STUDENT WHERE  SCHOOL_CD= P_SCHOOL_CD
		GROUP BY DEPT_NO;
	ELSE
    OPEN C1 FOR
		SELECT DEPT_NO, COUNT(*) FROM STUDENT WHERE  SCHOOL_CD= P_SCHOOL_CD
		AND DEPT_NO = P_DEPT_NO; 
    END IF;	
			 	

 END GET_COUNT;
 
/

[Updated on: Mon, 12 May 2008 02:49]

Report message to a moderator

Re: How to get output for procedure using optional parameter and ref cursor [message #319555 is a reply to message #319546] Mon, 12 May 2008 03:22 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A similar question.

Besides, your second SELECT misses GROUP BY clause.
Re: How to get output for procedure using optional parameter and ref cursor [message #319560 is a reply to message #319546] Mon, 12 May 2008 03:42 Go to previous messageGo to next message
NIRVANA
Messages: 11
Registered: May 2008
Junior Member
Does 2nd SQL needs GROUP BY? Here I am passing the DEPT_ID.

But here I am using optional parameters.
So how I will pass the parameter and can get the output results?

Say, I want to pass values

P_SCHOOL_CD => A12



CREATE OR REPLACE PROCEDURE GET_COUNT 
	( P_SCHOOL_CD    VARCHAR2(4),
	  P_DEPT_NO VARCHAR2(4) DEFAULT NULL,
          P_STD_ID    NUMBER,	  
	  C1 OUT GlobalPkg.REF_C1
	 )
...
...


varaible g_ref REFCURSOR
EXECUTE GET_COUNT( 'A12',:g_ref)
will this work?

[Updated on: Mon, 12 May 2008 03:52]

Report message to a moderator

Re: How to get output for procedure using optional parameter and ref cursor [message #319562 is a reply to message #319546] Mon, 12 May 2008 04:02 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maybe you should first read the documentation, found eg. online on http://tahiti.oracle.com/.
Especially the chapter http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#525, part "Using Default Values for Subprogram Parameters".

It is not wise to use DEFAULT parameter in the middle of the list; when calling it you may use the named notation or combine it with the positional one, having the restrictions described in that article.
Re: How to get output for procedure using optional parameter and ref cursor [message #319563 is a reply to message #319560] Mon, 12 May 2008 04:14 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
NIRVANA wrote on Mon, 12 May 2008 10:42
Does 2nd SQL needs GROUP BY?

Yes, it does.

It looks like
SELECT DEPT_NO, COUNT(*) 
FROM STUDENT 
WHERE SCHOOL_CD = P_SCHOOL_CD
  AND DEPT_NO = P_DEPT_NO; 
If there's an aggregate function in the selectable column list along with another non-aggregate column, you'll miss that GROUP BY clause.

Here's a Scott's schema based example:
SQL> -- first, without the GROUP BY clause:
SQL> select deptno, count(*)
  2  from emp
  3  where job = 'CLERK';
select deptno, count(*)
       *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> -- now with the GROUP BY clause:
SQL> select deptno, count(*)
  2  from emp
  3  where job = 'CLERK'
  4  group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          1
        20          2
        10          1

SQL>

It doesn't matter you use an optional parameter; it isn't the part of the SELECT column list but WHERE clause. If it was, you'd probably have another problem (but that's another story).
Re: How to get output for procedure using optional parameter and ref cursor [message #319565 is a reply to message #319562] Mon, 12 May 2008 04:16 Go to previous messageGo to next message
NIRVANA
Messages: 11
Registered: May 2008
Junior Member
Thanks for your early response.
Here I am passing the SCHOOL CODE which is not in the iddle of the list.


Is it something like this below?
execute(p_school_cd => 'A12',:g_ref)

While I am trying with the above staement it is showing following error:
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_COUNT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Re: How to get output for procedure using optional parameter and ref cursor [message #319566 is a reply to message #319563] Mon, 12 May 2008 04:20 Go to previous messageGo to next message
NIRVANA
Messages: 11
Registered: May 2008
Junior Member
Thank you very much for pointing out my mistake.You are correct!
Thanks..


execute(p_school_cd => 'A12',:g_ref)

While I am trying with the above staement for the above procedure it is showing following error:

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_COUNT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


How I can see the result?

Re: How to get output for procedure using optional parameter and ref cursor [message #319567 is a reply to message #319565] Mon, 12 May 2008 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It doesn't matter that the parameter is optional; you don't have to USE it in the procedure, but you have to pass it to the procedure.

Another example (Scott's schema), this time using the NVL function instead of IF-THEN-ELSE as you did (I like it better):
SQL> create or replace procedure get_count
  2    (p_deptno in number,
  3     p_job    in char,
  4     c1       out sys_refcursor
  5    )
  6  is
  7  begin
  8    open c1 for
  9      select e.deptno, count(*)
 10        from emp e
 11        where e.deptno = p_deptno
 12          and e.job = NVL(p_job, e.job)       --> JOB is optional
 13        group by e.deptno;
 14  end;
 15  /

Procedure created.

SQL> var cnt refcursor
SQL>
SQL> exec get_count(10, 'CLERK', :cnt);

PL/SQL procedure successfully completed.

SQL> print cnt

    DEPTNO   COUNT(*)
---------- ----------
        10          1

SQL> exec get_count(10, null, :cnt);          --> note use of NULL

PL/SQL procedure successfully completed.

SQL> print cnt

    DEPTNO   COUNT(*)
---------- ----------
        10          3

SQL>
Re: How to get output for procedure using optional parameter and ref cursor [message #319573 is a reply to message #319546] Mon, 12 May 2008 04:52 Go to previous messageGo to next message
NIRVANA
Messages: 11
Registered: May 2008
Junior Member
Thank you very much (Littlefoot)...
Liked the way of your try to make things easy....
Thanks ...Thanks a lot for all your help!
Re: How to get output for procedure using optional parameter and ref cursor [message #319587 is a reply to message #319546] Mon, 12 May 2008 05:35 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I just wonder how did you understand the documentation, as your example is totally opposite:
DECLARE
   FUNCTION gross_pay (
      emp_id   IN NUMBER,
      st_hours IN NUMBER DEFAULT 40,
      ot_hours IN NUMBER) RETURN REAL IS
   BEGIN
      ...
   END;

...

IF gross_pay(emp_num, ot_hour => NULL) > max_pay THEN ...

Although it is (sometimes) possible to combine parameter notations, I do not like it at all; I would choose one notation (positional or named) and used it for all parameters.
In positional notation, you have to specify all DEFAULT parameters followed by non-default ones (as showed by Littlefoot) - no way for skipping them.
Previous Topic: Views:advantages
Next Topic: Help Please
Goto Forum:
  


Current Time: Sun Dec 04 23:05:57 CST 2016

Total time taken to generate the page: 0.13003 seconds