Home » SQL & PL/SQL » SQL & PL/SQL » Need help writing elements of an array to a single row (merged 3) (Oracle 10g)
Need help writing elements of an array to a single row (merged 3) [message #442514] Mon, 08 February 2010 16:32 Go to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Hello Everyone,

I am novice in PL/SQL. I wanted create function that might be able to return multiple rows of values even if the input is only one value. But I cannot achieve this with a simple function.

I believe this would be more clear if i would try to explain it with a simple example.

Lets say I have a a table called DEPT just like given below

DEPT
key_user_id...user_name....enrolled_dept....enrolled_date
1233.............J Doe............research............01/29/2009
1233.............J Doe............design...............03/15/2009
1233.............J Doe............development.......09/11/2009
1231.............B Curtis.........research............10/08/2001
1232.............R Mani...........development.......08/09/2002

And just for reference lets say I have another table called USER

USER
user_id.......user_name
4561..........B Curtis
4563..........R Mani
4569..........J Doe

This is the function I created

CREATE OR REPLACE FUNCTION get_enroll_date (input_user_id USER.user_id%TYPE)
RETURN DATE IS
 var_date DATE; 

CURSOR my_cur is
 SELECT enrolled_date 
 FROM dept
 where key_user_id = input_user_id
       and user_name = 'J Doe';

my_cur_rec my_cur%ROWTYPE;

BEGIN
 FOR my_cur_rec IN my_cur LOOP
    var_date := my_cur.enrolled_date;
    RETURN var_date;
 END LOOP;
END;


Now lets call the function in a SQL statement

select get_enroll_date(1233) from dual;

Result: 09/11/2009

Only one row was returned.

However I wanted was to return all the enrolled_dates for user "J Doe" which are

01/29/2009
03/15/2009
09/11/2009

What am I not doing right? Any suggestions and recommendations would be greatly appreciated.

Thanks in advance.
Re: Create a function get multiple row with single input value [message #442515 is a reply to message #442514] Mon, 08 February 2010 17:00 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> But I cannot achieve this with a simple function.
By definition, a function returns a single scalar value.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Mon, 08 February 2010 17:00]

Report message to a moderator

Need help writing elements of an array to a single row [message #442522 is a reply to message #442514] Mon, 08 February 2010 21:18 Go to previous messageGo to next message
msalman05
Messages: 4
Registered: February 2010
Junior Member
Looking a way to insert all the elements of an array(long) to a single row(long). After inserting the first entry, tried to append values of the remaining elements to the same row:

counter := 1;
FOR countx IN ll_text_fd.FIRST..ll_text_fd.LAST LOOP
	IF( counter = 1 ) THEN
	ls_tmp_fd_val := ll_text_fd ( countx );
	INSERT INTO REP_TEXT_TABLE (
		SEND_FAC, PATH_NO, REP_DATE,
		SECT_ID, SECTION_TEXT
	)
	VALUES (
		ls_send_fac, ls_path_no, ls_rep_date,
		'FD', ls_tmp_fd_val
	);
	COMMIT;
	counter := counter+1;
	ELSE
		UPDATE REP_TEXT_TABLE
		SET SECTION_TEXT = SECTION_TEXT || CHR ( 10 ) ||ll_text_fd(countx)
		WHERE  SEND_FAC = ls_send_fac AND
		PATH_NO = ls_path_no AND
		REP_DATE = ls_rep_date AND
		SECT_ID = 'FD';
		COMMIT;
		counter := counter+1;                                                                              
	END IF;
END LOOP;


I get an Oracle exception ERROR[3000]: ORA-00932: inconsistent datatypes because of the line 'SECTION_TEXT = SECTION_TEXT || CHR ( 10 ) ||ll_text_fd(countx)'.

Any way around this or some other solution?

Thanks in advance

[Updated on: Mon, 08 February 2010 21:34]

Report message to a moderator

Need help writing elements of an array to a single row [message #442524 is a reply to message #442514] Mon, 08 February 2010 21:21 Go to previous messageGo to next message
msalman05
Messages: 4
Registered: February 2010
Junior Member
Looking a way to insert all the elements of an array(long) to a single row(long). After inserting the first entry, tried to append values of the remaining elements to the same row:

counter := 1;
FOR countx IN ll_text_fd.FIRST..ll_text_fd.LAST LOOP
	IF( counter = 1 ) THEN
	ls_tmp_fd_val := ll_text_fd ( countx );
	INSERT INTO REP_TEXT_TABLE (
		SEND_FAC, PATH_NO, REP_DATE,
		SECT_ID, SECTION_TEXT
	)
	VALUES (
		ls_send_fac, ls_path_no, ls_rep_date,
		'FD', ls_tmp_fd_val
	);
	COMMIT;
	counter := counter+1;
	ELSE
		UPDATE REP_TEXT_TABLE
		SET SECTION_TEXT = SECTION_TEXT || CHR ( 10 ) ||ll_text_fd(countx)
		WHERE  SEND_FAC = ls_send_fac AND
		PATH_NO = ls_path_no AND
		REP_DATE = ls_rep_date AND
		SECT_ID = 'FD';
		COMMIT;
		counter := counter+1;                                                                              
	END IF;
END LOOP;


I get an Oracle exception ERROR[3000]: ORA-00932: inconsistent datatypes because of the line 'SECTION_TEXT = SECTION_TEXT || CHR ( 10 ) ||ll_text_fd(countx)'.

Any way around this or some other solution?

Thanks in advance

[Updated on: Mon, 08 February 2010 21:32]

Report message to a moderator

Re: Create a function get multiple row with single input value [message #442525 is a reply to message #442514] Mon, 08 February 2010 21:54 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> create table orafaq_dept
  2  (key_user_id number,
  3  user_name varchar2(50),
  4  enrolled_dept varchar2(50),
  5  enrolled_date date);

Table created.

SQL> insert into orafaq_dept values(1233,'J Doe','research',to_date('01/29/2009','mm/dd/yyyy'));

1 row created.

SQL> insert into orafaq_dept values(1233,'J Doe','design',to_date('03/15/2009','mm/dd/yyyy'));

1 row created.

SQL> insert into orafaq_dept values(1233,'J Doe','development',to_date('09/11/2009','mm/dd/yyyy'));

1 row created.

SQL> insert into orafaq_dept values(1231,'B Curtis','research',to_date('10/08/2001','mm/dd/yyyy'));

1 row created.

SQL> insert into orafaq_dept values(1232,'R Mani','development',to_date('08/09/2002','mm/dd/yyyy'));

1 row created.



SQL> col user_name format a15
SQL> col enrolled_dept format a15
SQL> select * from orafaq_dept;

KEY_USER_ID USER_NAME       ENROLLED_DEPT   ENROLLED_
----------- --------------- --------------- ---------
       1233 J Doe           research        29-JAN-09
       1233 J Doe           design          15-MAR-09
       1233 J Doe           development     11-SEP-09
       1231 B Curtis        research        08-OCT-01
       1232 R Mani          development     09-AUG-02

SQL> sho user
USER is "SCOTT"
SQL> CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
  2    RETURN t_in_list_tab
  3  AS
  4    l_tab   t_in_list_tab := t_in_list_tab();
  5    l_text  VARCHAR2(32767) := p_in_list || ',';
  6    l_idx   NUMBER;
  7  BEGIN
  8    LOOP
  9      l_idx := INSTR(l_text, ',');
 10      EXIT WHEN NVL(l_idx, 0) = 0;
 11      l_tab.extend;
 12      l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
 13      l_text := SUBSTR(l_text, l_idx + 1);
 14    END LOOP;
 15
 16    RETURN l_tab;
 17  END;
 18  /

Function created.



SQL> SELECT enrolled_date
    FROM   orafaq_dept
   WHERE  key_user_id IN (SELECT * FROM TABLE(in_list('1233')))
SQL> /

ENROLLED_
---------
11-SEP-09
15-MAR-09
29-JAN-09

SQL>


Does the above helpful?
sriram Smile
source : http://www.oracle-base.com/articles/misc/DynamicInLists.php
Re: Create a function get multiple row with single input value [message #442530 is a reply to message #442525] Mon, 08 February 2010 22:10 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Or append the result set and simply return the output after the end of loop in your code...

sriram Smile

[Updated on: Mon, 08 February 2010 22:18]

Report message to a moderator

Re: Create a function get multiple row with single input value [message #442532 is a reply to message #442530] Mon, 08 February 2010 22:32 Go to previous messageGo to next message
msalman05
Messages: 4
Registered: February 2010
Junior Member
Each element in the array has around 32k text. So can't append it to one long variable which has a limit of 32k. But the table long variable can store up to 2GB.

If I use a clob in my stored procedure, I can't write it to a long directly. I have to break it into chucks of 32K and will run into the same problem.

Thanks for the help
Re: Need help writing elements of an array to a single row [message #442533 is a reply to message #442522] Mon, 08 February 2010 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Any way around this or some other solution?

perhaps as below?

TMP_VAR := ll_text_fd(countx);
SET SECTION_TEXT = SECTION_TEXT || CHR ( 10 ) || TMP_VAR;
Re: Create a function get multiple row with single input value [message #442537 is a reply to message #442532] Mon, 08 February 2010 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

How would an independent observer conclude a valid solution has been posted?


What problem are you REALLY trying to solve?
Re: Create a function get multiple row with single input value [message #442545 is a reply to message #442515] Mon, 08 February 2010 23:12 Go to previous messageGo to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Hello Black Swan,

Thanks for directing me to the guidelines but was there any thing in my post you thought was outside the guidelines. My first language is not English but I tried to be polite and if you think that my post was beyond guidelines or was not polite in anyways it was purely because of the language barrier and I apologize for that.

Regards,
SobeNo
Re: Need help writing elements of an array to a single row (merged 3) [message #442548 is a reply to message #442514] Mon, 08 February 2010 23:17 Go to previous messageGo to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Thank you so much for all the response and guidance.

Regards,
SobeNo
Re: Need help writing elements of an array to a single row (merged 3) [message #442583 is a reply to message #442548] Tue, 09 February 2010 03:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think STRAGG would be a good solution for you.

It's a user-defined aggregate function that concatenates the input strings it gets and returns a single variable.

The version I linked to above concatenates into a Varchar2(4000), but it would be easy to rewrite it to concatenate into a CLOB.


Btw - use CLOBs, not LONGs.
The LONG datatype has been depreciated, and they are an absolute pain to work with - CLOBs, especially in 10g are much easier to manipulate.

If you've got a LONG in your table, then I'd really recommend that you push to get it converted to a CLOB
Re: Need help writing elements of an array to a single row [message #442621 is a reply to message #442533] Tue, 09 February 2010 08:55 Go to previous messageGo to next message
msalman05
Messages: 4
Registered: February 2010
Junior Member
BlackSwan wrote on Mon, 08 February 2010 22:34
>Any way around this or some other solution?

perhaps as below?

TMP_VAR := ll_text_fd(countx);
SET SECTION_TEXT = SECTION_TEXT || CHR ( 10 ) || TMP_VAR;


Tried this but it still throws the same exception. It's the sencond 'SECTION_TEXT' that's causing the problem


JRowbottom wrote on Tue, 09 February 2010 03:52
I think STRAGG would be a good solution for you.

It's a user-defined aggregate function that concatenates the input strings it gets and returns a single variable.

The version I linked to above concatenates into a Varchar2(4000), but it would be easy to rewrite it to concatenate into a CLOB.


Btw - use CLOBs, not LONGs.
The LONG datatype has been depreciated, and they are an absolute pain to work with - CLOBs, especially in 10g are much easier to manipulate.

If you've got a LONG in your table, then I'd really recommend that you push to get it converted to a CLOB


Yeah it would be lot easier if I could convert it to clob. But there are a lot of other stored procedures using the same table.

If I get the all the values concatenated to a Clob, is there an easy way to insert the first 2GB charachters of Clob to long?

[Updated on: Tue, 09 February 2010 09:01]

Report message to a moderator

Re: Need help writing elements of an array to a single row [message #442639 is a reply to message #442621] Tue, 09 February 2010 11:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That shouldn't be too much of a problem.

If you look at the white parer here, it demonstrates that pretty much anything that you can do with a LONG or LONG RAW will still work when you convert the underlying table to LOB.

I can't think of an easy way (or even a moderately difficult way) of loading data from a CLOB into a LONG if there's more than 32767 bytes of data in the CLOB.

You could export it to a flat file and load it with SQL*Loader.
Previous Topic: DBMS_SCHEDULER
Next Topic: an SQL query
Goto Forum:
  


Current Time: Sun Dec 04 08:26:03 CST 2016

Total time taken to generate the page: 0.07696 seconds