Home » SQL & PL/SQL » SQL & PL/SQL » function returns multiple values (2columns, numerous rows) (ORACLE 10Gr2)
function returns multiple values (2columns, numerous rows) [message #354996] Wed, 22 October 2008 02:57 Go to next message
hjvrvp
Messages: 4
Registered: October 2008
Junior Member
Hi,
I need to run a sql query returning 2 columns with numerous rows. Here is my query: select oma_maint.oma_maintenance(30613,'PARAMETER21') from dual
The result should be (it depends of the oma_maintenance parameter values):
col1,col2
12345,'toto'
45678,'titi'
But the result of this query is:
Invalid DATATYPE
at the line: RETURN l_list;

I don't want to create a new table, I can't use a complicated SQL query because it supposed to be used by an ERP product.
So I decided to create a package (oma_maint) containing a new type(value_record) and my fonction(oma_maintenance).
I have two cases in this function.
I attached my package.
  • Attachment: export.sql
    (Size: 4.19KB, Downloaded 169 times)

[Updated on: Wed, 22 October 2008 02:59]

Report message to a moderator

Re: function returns multiple values (2columns, numerous rows) [message #355004 is a reply to message #354996] Wed, 22 October 2008 03:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I didn't go through your script.

I think you have to create TYPE as an object in DATABASE and preferrably use TABLE Function .

Thumbs Up
Rajuvan.
Re: function returns multiple values (2columns, numerous rows) [message #355007 is a reply to message #354996] Wed, 22 October 2008 03:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you show us the SQL you are using?

I suspect that @Rajuvan has the correct answer - if you want a user defined type to be used in SQL, it must be declared in SQL with a CREATE TYPE statement.
Re: function returns multiple values (2columns, numerous rows) [message #355010 is a reply to message #354996] Wed, 22 October 2008 03:51 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

whenever you define plsql records or collections you should have to declare the records or collections with identifiers..Here
a record EMP_RECORD, of the type EMP_RECORD_TYPE is declared.
a associative array ENAME_TABLE,of the type ENAME_TABLE_TYPE is declared.

this is the syntax

TYPE type_name IS RECORD
     (field_declaration[, field_declaration]…);
identifier	type_name; 

TYPE type_name IS TABLE OF 
     {column_type | variable%TYPE
     | table.column%TYPE} [NOT NULL] 
     | table.%ROWTYPE  
     [INDEX BY BINARY_INTEGER];
identifier	type_name;





example :


DECLARE
TYPE emp_record_type IS RECORD
        (employee_id	   NUMBER(6) NOT NULL := 100,
         last_name        employees.last_name%TYPE,
         job_id           employees.job_id%TYPE);
         emp_record       emp_record_type;
      
TYPE ename_table_type IS TABLE OF 
       employees.last_name%TYPE
       INDEX BY BINARY_INTEGER;
       ename_table       ename_table_type; 

[Updated on: Wed, 22 October 2008 03:56]

Report message to a moderator

Re: function returns multiple values (2columns, numerous rows) [message #355013 is a reply to message #354996] Wed, 22 October 2008 04:00 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
DECLARE
TYPE value_record IS RECORD( value1_type VARCHAR2(200)
  ,value2_type VARCHAR2(200)
  );
  TYPE value_list IS TABLE OF value_record INDEX BY BINARY_INTEGER;
  LIST value_list;
BEGIN
	SELECT oma_maint.oma_maintenance(30613,'PARAMETER21')
	INTO LIST(1)
	FROM dual;
END;	 


I have not tested but it may be like this one.

[Updated on: Wed, 22 October 2008 04:01]

Report message to a moderator

Re: function returns multiple values (2columns, numerous rows) [message #355015 is a reply to message #354996] Wed, 22 October 2008 04:52 Go to previous messageGo to next message
hjvrvp
Messages: 4
Registered: October 2008
Junior Member
Ora_2007, don't forget, that I have to use the SELECT oma_maint.oma_maintenance(30613,'PARAMETER21') FROM dual; into an ERP. In other word, i need to use this select from sqlplus only. That's why I used a package in order to declare the value_record type.

Also, I have to tell you that I have no grants to add types.

Here is a copy of my package:

CREATE OR REPLACE PACKAGE OMA_MAINT AS
TYPE value_record IS RECORD( value1_type VARCHAR2(200)
,value2_type VARCHAR2(200)
);
TYPE value_list IS TABLE OF value_record INDEX BY binary_integer;

FUNCTION OMA_MAINTENANCE (p_reqid IN NUMBER
,p_parameter_name IN VARCHAR2
)RETURN value_list;
END OMA_MAINT;
Re: function returns multiple values (2columns, numerous rows) [message #355128 is a reply to message #355015] Wed, 22 October 2008 15:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can do this using a pipelined function. I have provided a simplified example below using the dept and emp demo tables, since you did not provide any create table and insert statements for sample data.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE oma_maint
  2  AS
  3    TYPE value_record IS RECORD
  4  	 (value1_type	      VARCHAR2(200),
  5  	  value2_type	      VARCHAR2(200));
  6    TYPE value_list	   IS TABLE OF value_record;
  7    FUNCTION oma_maintenance
  8  	 (p_reqid	   IN NUMBER,
  9  	  p_parameter_name IN VARCHAR2)
 10  	 RETURN value_list PIPELINED;
 11  END oma_maint;
 12  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY oma_maint
  2  AS
  3    FUNCTION oma_maintenance
  4  	 (p_reqid	   IN NUMBER,
  5  	  p_parameter_name IN VARCHAR2)
  6  	 RETURN value_list PIPELINED
  7    IS
  8  	 l_list 	      value_list;
  9    BEGIN
 10  	 -- populate collection:
 11  	 SELECT emp.ename, dept.loc
 12  	 BULK	COLLECT
 13  	 INTO	l_list
 14  	 FROM	emp, dept
 15  	 WHERE	emp.deptno = dept.deptno
 16  	 AND	emp.deptno = p_reqid
 17  	 AND	emp.job = p_parameter_name
 18  	 ORDER	BY ename;
 19  	 -- output collection:
 20  	 FOR i IN 1 .. l_list.COUNT LOOP
 21  	   PIPE ROW (l_list(i));
 22  	 END LOOP;
 23  	 RETURN;
 24    END oma_maintenance;
 25  END oma_maint;
 26  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> COLUMN value1_type FORMAT A15
SCOTT@orcl_11g> COLUMN value2_type FORMAT A15
SCOTT@orcl_11g> SELECT * FROM TABLE (oma_maint.oma_maintenance (30, 'SALESMAN'))
  2  /

VALUE1_TYPE     VALUE2_TYPE
--------------- ---------------
ALLEN           CHICAGO
MARTIN          CHICAGO
TURNER          CHICAGO
WARD            CHICAGO

SCOTT@orcl_11g> 


Re: function returns multiple values (2columns, numerous rows) [message #355132 is a reply to message #355128] Wed, 22 October 2008 15:41 Go to previous messageGo to next message
hjvrvp
Messages: 4
Registered: October 2008
Junior Member
Thank you Barbara,
It seems to be a good idea.
I'm gonna try it and get back to you on friday.
Regards,
Re: function returns multiple values (2columns, numerous rows) [message #355135 is a reply to message #354996] Wed, 22 October 2008 15:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Wow!

you learn something new every day. I didn't think you could do that. I thought you always had to define a database object and collection in order to have sql call a fuction with a complex type. I did not believe sql would ever see the package type.

Neat.

SQL> create table temp1 as select * from TABLE (oma_maint.oma_maintenance (30, 'SALESMAN'));

Table created.

SQL> set linesize 80
SQL> desc temp1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VALUE1_TYPE                                        VARCHAR2(200)
 VALUE2_TYPE                                        VARCHAR2(200)

SQL> 


Thanks a bunch. Kevin
Re: function returns multiple values (2columns, numerous rows) [message #355459 is a reply to message #355135] Fri, 24 October 2008 03:12 Go to previous message
hjvrvp
Messages: 4
Registered: October 2008
Junior Member
Barbara, you're the best.
Your solution works corretly and your example was very usefull.
Thanks you for all your answers.
I attached my package in case someone would get the same issue.
Regards,
  • Attachment: export.sql
    (Size: 4.90KB, Downloaded 179 times)
Previous Topic: replace multiple strings
Next Topic: Comparison between join and IN clause
Goto Forum:
  


Current Time: Sat Dec 10 04:49:07 CST 2016

Total time taken to generate the page: 0.05292 seconds