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  |
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
|
Senior Member |
|
|
Hi Experts  ,
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 #289245 is a reply to message #289231] |
Thu, 20 December 2007 07:27  |
 |
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #289330 is a reply to message #289231] |
Thu, 20 December 2007 13:44 |
 |
Barbara Boehmer
Messages: 9106
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 |
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 |
|
Barbara Boehmer
Messages: 9106
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 |
|
|
|
Goto Forum:
Current Time: Fri Jul 11 03:51:18 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
