Home » SQL & PL/SQL » SQL & PL/SQL » Using sql object types as an out parameter to a procedure
Using sql object types as an out parameter to a procedure [message #289231] Thu, 20 December 2007 06:32 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi Experts Laughing ,

Is it possible to use sql objects to return the result sets.
For e.g., I had created a object called emp_details_obj which contains
	Create or replace object emp_details as 
     (
        Emp_name varchar2(100),
	Emp_id   number(3),
	Sal      number(8),
	Comm     number(4)
	Deptno   number(3)
      );

Now I want to use this object type as a out parameter for the bellow procedure
e.g.,
create or replace procedure test( p_deptno IN number
                                , ??????)
is
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end test;

Please let me know what should I replace in ‘???’ so that my out parameter is of emp_details object type.

Thanks,

[EDITED by LF: added [code] tags]

[Updated on: Thu, 20 December 2007 07:24] by Moderator

Report message to a moderator

Re: Using sql object types as an out parameter to a procedure [message #289234 is a reply to message #289231] Thu, 20 December 2007 07:02 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
If you use the code tags then those smileys go away and the code is actually readable.

[Updated on: Thu, 20 December 2007 07:04]

Report message to a moderator

Re: Using sql object types as an out parameter to a procedure [message #289245 is a reply to message #289231] Thu, 20 December 2007 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, try to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Also post your Oracle version (4 decimals).

Then, try to post something that compiles:
SQL> Create or replace object emp_details as 
  2       (
  3          Emp_name varchar2(100),
  4   Emp_id   number(3),
  5   Sal      number(8),
  6   Comm     number(4)
  7   Deptno   number(3)
  8        );
Create or replace object emp_details as
                  *
ERROR at line 1:
ORA-00922: missing or invalid option

"create object" does not exist.
"create type" does.

Then if you create a type, it is obvious how to use it as OUT parameter.

Regards
Michel
Re: Using sql object types as an out parameter to a procedure [message #289246 is a reply to message #289231] Thu, 20 December 2007 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

create or replace procedure test( p_deptno IN number
, ??????)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;

What you want to return is not an object/record but a REF CURSOR.

Regards
Michel

[Updated on: Thu, 20 December 2007 08:02]

Report message to a moderator

Re: Using sql object types as an out parameter to a procedure [message #289247 is a reply to message #289231] Thu, 20 December 2007 07:31 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you looking for something like this?
SQL> create or replace procedure prc_test
  2    (p_deptno in number, p_cursor out sys_refcursor)
  3  is
  4  begin
  5    open p_cursor for
  6      select * from emp
  7      where deptno = p_deptno;
  8  end;
  9  /

Procedure created.

SQL> var l_out refcursor;
SQL>
SQL> exec prc_test(10, :l_out);

PL/SQL procedure successfully completed.

SQL> print l_out;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09.06.81       2450
        10

      7839 KING       PRESIDENT            17.11.81       5000
        10

      7934 MILLER     CLERK           7782 23.12.82       1300
        10


SQL>
Re: Using sql object types as an out parameter to a procedure [message #289249 is a reply to message #289247] Thu, 20 December 2007 07:38 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Yes, But instead of sys_refcursor i want to use my own object type(emp_details type).
Is it possible?


Thanks,
Re: Using sql object types as an out parameter to a procedure [message #289255 is a reply to message #289231] Thu, 20 December 2007 07:52 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Any updates on this please.
Re: Using sql object types as an out parameter to a procedure [message #289260 is a reply to message #289255] Thu, 20 December 2007 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create you own REF CURSOR type or create you own record type and change your procedure.

Regards
Michel
Re: Using sql object types as an out parameter to a procedure [message #289330 is a reply to message #289231] Thu, 20 December 2007 13:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I am not sure if what you are asking for is what you need, because you haven't given a reason for it, so I have provided what you asked for and what I am guessing you might need.

The first method below is exactly what you asked for, a procedure that returns the result set in a sql object as an out parameter and an anonymous pl/sql block to show that it works.

I am guessing that you may be wanting to use objects, rather than a ref cursor, so that you can select from sql. So, the second method below uses a function, instead of a procedure to return the object, so that you can select from it in sql.

-- types for demo:
SCOTT@orcl_11g> Create or replace TYPE emp_details_obj as OBJECT
  2  	  (
  3  	     Emp_name varchar2(10),
  4  	     Emp_id   number(4),
  5  	     Sal      number(7,2),
  6  	     Comm     number(7,2),
  7  	     Deptno   number(2)
  8  	   );
  9  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- procedure:
SCOTT@orcl_11g> CREATE OR REPLACE TYPE emp_details_tab AS TABLE OF emp_details_obj
  2  /

Type created.


-- first method with procedure using object as out parameter:
SCOTT@orcl_11g> create or replace PROCEDURE test_proc
  2    ( p_deptno  IN  number
  3    , p_details OUT emp_details_tab)
  4  is
  5  begin
  6    p_details := emp_details_tab();
  7    FOR r IN
  8  	 (select *
  9  	  from	 emp
 10  	  where  deptno = p_deptno)
 11    LOOP
 12  	 p_details.EXTEND;
 13  	 p_details(p_details.COUNT) := emp_details_obj (r.ename, r.empno, r.sal, r.comm, r.deptno);
 14    END LOOP;
 15  end test_proc;
 16  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
-- anonymous pl/sql block to test the procedure:
SCOTT@orcl_11g> DECLARE
  2    v_details emp_details_tab;
  3  BEGIN
  4    test_proc (10, v_details);
  5    FOR i IN v_details.FIRST .. v_details.LAST LOOP
  6  	 DBMS_OUTPUT.PUT_LINE (v_details(i).emp_name);
  7  	 DBMS_OUTPUT.PUT_LINE (v_details(i).emp_id);
  8  	 DBMS_OUTPUT.PUT_LINE (v_details(i).sal);
  9  	 DBMS_OUTPUT.PUT_LINE (v_details(i).comm);
 10  	 DBMS_OUTPUT.PUT_LINE (v_details(i).deptno);
 11  	 DBMS_OUTPUT.PUT_LINE ('-----------------------------------------');
 12    END LOOP;
 13  END;
 14  /
CLARK
7782
2450
10
-----------------------------------------
KING
7839
5000
10
-----------------------------------------
MILLER
7934
1300
10
-----------------------------------------

PL/SQL procedure successfully completed.


-- second method using function:
SCOTT@orcl_11g> create or replace FUNCTION test_func
  2    ( p_deptno  IN  number )
  3    RETURN emp_details_tab
  4  is
  5    v_details emp_details_tab := emp_details_tab();
  6  begin
  7    FOR r IN
  8  	 (select *
  9  	  from	 emp
 10  	  where  deptno = p_deptno)
 11    LOOP
 12  	 v_details.EXTEND;
 13  	 v_details(v_details.COUNT) := emp_details_obj (r.ename, r.empno, r.sal, r.comm, r.deptno);
 14    END LOOP;
 15    RETURN v_details;
 16  end test_func;
 17  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
-- selecting the data from sql using the function:
SCOTT@orcl_11g> SELECT * FROM TABLE (CAST (test_func (10) AS emp_details_tab))
  2  /

EMP_NAME       EMP_ID        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- ----------
CLARK            7782       2450                    10
KING             7839       5000                    10
MILLER           7934       1300                    10

SCOTT@orcl_11g>  


Re: Using sql object types as an out parameter to a procedure [message #289630 is a reply to message #289231] Mon, 24 December 2007 09:02 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks ,

Yes,that Procedure meets my requirements.
But, i am unable to understand which is the best approach from these i.e., the same can be achieved by
1) Create my own REF CURSOR type or creating my own record type and change the procedure accordingly
2)The way which BARBARA used (using sql objects)

Please let me know from these methods which is the best way and why?And also which method is used when.

My requirement is to send the result set to the front developer and he will loop through the result set and popullate accordingly.


Thanks in Advance.
Re: Using sql object types as an out parameter to a procedure [message #289650 is a reply to message #289630] Mon, 24 December 2007 18:15 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
ramanajv1968 wrote on Mon, 24 December 2007 07:02

Thanks ,

Yes,that Procedure meets my requirements.
But, i am unable to understand which is the best approach from these i.e., the same can be achieved by
1) Create my own REF CURSOR type or creating my own record type and change the procedure accordingly
2)The way which BARBARA used (using sql objects)

Please let me know from these methods which is the best way and why?And also which method is used when.

My requirement is to send the result set to the front developer and he will loop through the result set and popullate accordingly.


Thanks in Advance.



You need to find out what is required in order to know which method to use. Does your developer want a strongly typed ref cursor returned or does your developer want an object returned or does it matter which? Does he require a procedure or a function or does it matter? Option 1 will return a ref cursor using either a procedure or function. Option 2 will return an object using either a procedure or a function. If it is completely up to you, then returning a ref cursor is about the simplest, most standard method, however it will not return an object, as you initially stated was part of your requirement. Here is an example of method 1, declaring your own record type and ref cursor type within a package.

-- types package:
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE types_pkg
  2  AS
  3    TYPE emp_details_rec IS RECORD
  4  	  (
  5  	     Emp_name varchar2(10),
  6  	     Emp_id   number(4),
  7  	     Sal      number(7,2),
  8  	     Comm     number(7,2),
  9  	     Deptno   number(2)
 10  	   );
 11    TYPE p_ref IS REF CURSOR RETURN emp_details_rec;
 12  END types_pkg;
 13  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- procedure that uses types package above:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE test_proc
  2    ( p_deptno  IN  number
  3    , p_details OUT types_pkg.p_ref)
  4  is
  5  begin
  6    OPEN p_details FOR
  7    SELECT ename AS emp_name,
  8  	      empno AS emp_id,
  9  	      sal, comm, deptno
 10    FROM   emp
 11    WHERE  deptno = p_deptno;
 12  end test_proc;
 13  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- test procedure:
SCOTT@orcl_11g> VARIABLE g_details REFCURSOR
SCOTT@orcl_11g> EXEC test_proc (10, :g_details)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_details

EMP_NAME       EMP_ID        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- ----------
CLARK            7782       2450                    10
KING             7839       5000                    10
MILLER           7934       1300                    10

SCOTT@orcl_11g> 



-- loop through the results as your developer would do:
SCOTT@orcl_11g> DECLARE
  2    v_ref types_pkg.p_ref;
  3    TYPE emp_details_tab IS TABLE OF types_pkg.emp_details_rec;
  4    v_details emp_details_tab;
  5  BEGIN
  6    test_proc (10, v_ref);
  7    FETCH v_ref BULK COLLECT INTO v_details;
  8    CLOSE v_ref;
  9    FOR i IN v_details.FIRST .. v_details.LAST LOOP
 10  	 DBMS_OUTPUT.PUT_LINE (v_details(i).emp_name);
 11  	 DBMS_OUTPUT.PUT_LINE (v_details(i).emp_id);
 12  	 DBMS_OUTPUT.PUT_LINE (v_details(i).sal);
 13  	 DBMS_OUTPUT.PUT_LINE (v_details(i).comm);
 14  	 DBMS_OUTPUT.PUT_LINE (v_details(i).deptno);
 15  	 DBMS_OUTPUT.PUT_LINE ('-----------------------------------------');
 16    END LOOP;
 17  END;
 18  /
CLARK
7782
2450
10
-----------------------------------------
KING
7839
5000
10
-----------------------------------------
MILLER
7934
1300
10
-----------------------------------------

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 



[Updated on: Mon, 24 December 2007 18:35]

Report message to a moderator

Previous Topic: Why I can't read sys.v_$session from procedures or functions?
Next Topic: Display the error thrown by a SQL statement using a PLSQL trigger.
Goto Forum:
  


Current Time: Wed Dec 07 11:03:52 CST 2016

Total time taken to generate the page: 0.21060 seconds