Home » Other » Client Tools » REF CURSOR to print output from given input (Oracle 10g)
icon2.gif  REF CURSOR to print output from given input [message #540954] Thu, 26 January 2012 13:24 Go to next message
ora_nov
Messages: 35
Registered: January 2012
Member
create table a_user
(
  u_name varchar2(30)
  , grp  varchar2(30)
)
;

INSERT INTO a_user VALUES ('abc', 'new');
INSERT INTO a_user VALUES ('def', 'new');
INSERT INTO a_user VALUES ('ref', 'old');
INSERT INTO a_user VALUES ('ven', 'old');



create table a_grp_count
(
  grp varchar(30)
  , u_count NUMBER
)
;

INSERT INTO a_grp_count VALUES ('new', 100);
INSERT INTO a_grp_count VALUES ('old', 200);
INSERT INTO a_grp_count VALUES ('old', 100);
INSERT INTO a_grp_count VALUES ('old', 400);
INSERT INTO a_grp_count VALUES ('old', 300);
INSERT INTO a_grp_count VALUES ('new', 600);
INSERT INTO a_grp_count VALUES ('new', 200);
INSERT INTO a_grp_count VALUES ('new', 0);
INSERT INTO a_grp_count VALUES ('new', 50);


sql:

SELECT
  a.grp
  , a.g_tot
FROM a_user b
,
(  
SELECT
  grp            AS grp
  , sum(u_count) AS g_tot
FROM
  a_grp_count
WHERE
  u_count > 0
group by grp
) a

WHERE b.grp = getgrpnam(p_u_name)
;

getgrpname is a function to get grp when I input the u_name


Now I want to create a proc to display the output from the sql when I give the p_u_name as input. I am thinking some thing like this:

create or replace procedure get_u_name_data (p_u_name IN VARCHAR2, rc_data OUT SYS_REFCURSOR) IS
IS
BEGIN
OPEN rc_data FOR
SELECT
  a.grp
  , a.g_tot
FROM a_user b
,
(  
SELECT
  grp            AS grp
  , sum(u_count) AS g_tot
FROM
  a_grp_count
WHERE
  u_count > 0
group by grp
) a

WHERE b.grp = getgrpnam(p_u_name)
;

END get_u_name_data;


Can you guys help me out how to print output in SQL developer
Re: REF CURSOR to print output from given input [message #540955 is a reply to message #540954] Thu, 26 January 2012 13:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't know about sqldeveloper, but there's an example of how to do it in sqlplus in this thread
Re: REF CURSOR to print output from given input [message #541190 is a reply to message #540954] Sun, 29 January 2012 08:06 Go to previous message
transfer
Messages: 53
Registered: August 2007
Location: transfer
Member
This works for me in SQL Developer:
VARIABLE RC REFCURSOR
EXEC GET_U_NAME_DATA('abc', :RC);
print :rc;
However, I would not write the procedure that way. You are doing an extra "context switch" between SQL and PL/SQL by calling the function in the SELECT. I would prefer calling the function directly in PL/SQL, then using the value in the SQL.
create or replace procedure get_u_name_data (p_u_name IN VARCHAR2, rc_data OUT SYS_REFCURSOR)
IS
l_grp a_user.grp%type;
BEGIN
l_grp := getgrpnam(p_u_name);
OPEN rc_data FOR
SELECT
  a.grp
  , a.g_tot
FROM a_user b
,
(  
SELECT
  grp            AS grp
  , sum(u_count) AS g_tot
FROM
  a_grp_count
WHERE
  u_count > 0
group by grp
) a

WHERE b.grp = l_grp;
END GET_U_NAME_DATA;
/
P.S. If getgrpnam() has logic that can be implemented in SQL, it would be far better to express the logic within the main SQL statement and forget the function.

P.P.S. In this case I suggest creating a function that returns the ref cursor, rather than a procedure with an OUT parameter.

[Updated on: Sun, 29 January 2012 14:47]

Report message to a moderator

Previous Topic: Not getting Editor from sqlplus*
Next Topic: Suppress Orcale script message dynamically
Goto Forum:
  


Current Time: Thu Mar 28 10:17:14 CDT 2024