INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Oracle function that return multiple rows

Oracle function that return multiple rows

(OP)
I would like to create a Oracle FUNCTION that will return unique listings of location types once a site_id is passed to it with carriage return as a separator (char13)

I took a first crack at the code, but I don't know how to handle multi row return. As it would happen in this case.


CREATE TABLE SITE (
SITE_ID INTEGER,
LOCATION VARCHAR2(50)
);


INSERT ALL
INTO SITE (SITE_ID, LOCATION) VALUES (3, "CHICAGO")
INTO SITE (SITE_ID, LOCATION) VALUES (3, "DALLAS")
INTO SITE (SITE_ID, LOCATION) VALUES (4, "LA")
INTO SITE (SITE_ID, LOCATION) VALUES (5, "KANSAS CITY")
INTO SITE (SITE_ID, LOCATION) VALUES (6, "RIO")
INTO SITE (SITE_ID, LOCATION) VALUES (4, "NEW YORK")
SELECT * FROM DUAL;



CREATE OR REPLACE FUNCTION UNIQUE_LOCATION (P_siteid INTEGER)

RETURN VARCHAR2(50)

IS

V_LOCATION VARCHAR2(50);

BEGIN

SELECT DISTINCT LOCATION INTO V_LOCATION FROM SITE WHERE SITE_ID=P_siteid;

RETURN V_LOCATION;

END UNIQUE_LOCATION;

Any help will be highly appreciated.

Thx.

Al

RE: Oracle function that return multiple rows

What is it you want to do with this data once your function returns it? That will have an influence on a final answer. But if you do wind up returning a VARCHAR2, you should change your function definition to RETURN VARCHAR2 (not VARCHAR2(50)).

RE: Oracle function that return multiple rows

(OP)
Carp,

Thanks for your reply. This function will be called by a java program and returning values will be displayed on the front end. btw above i meant to say chr(13) and not char(13).

Also, this select should be:

SELECT DISTINCT LOCATION INTO V_LOCATION FROM SITE WHERE SITE_ID=P_siteid where LOCATION IS NOT NULL;

I hope this helps.

Al

RE: Oracle function that return multiple rows

(OP)
Crap,

Should I be using a procedure to achieve above? If yes,how do i even start on that?

Al

RE: Oracle function that return multiple rows

Al -

An unfortunate transposition of letters on the name, but what the heck - many would say you got it right the second time! smile

I think you are on the right track with a function, so I would stick with that.

If you want to return a VARCHAR2, I assume you would be looking at something like a comma-separated string - is that right? If you are expecting a list of separate values, you will need to return a different data structure. Oracle can do this, but I am not java-conversant so somebody else will need to help you with that approach. Let me know which way you want to go.

RE: Oracle function that return multiple rows

(OP)
Carp,

Sorry for transposition of letters. For right now I will take a solution with a comma-separated string. List of seperate values may be a better choice later on. Its all in flux, but for now let’s go with a comma-separated string.

Thanks,

Al

RE: Oracle function that return multiple rows

Something like this?

CODE

CREATE OR REPLACE FUNCTION UNIQUE_LOCATION (P_siteid INTEGER)
RETURN VARCHAR2 IS
   v_answer  VARCHAR2(32767);     -- This is the biggest a VARCHAR2 can be in PL/SQL
BEGIN
   FOR r IN (SELECT DISTINCT LOCATION
             FROM   SITE
             WHERE SITE_ID=P_siteid
             AND    location IS NOT NULL) LOOP
       IF v_answer IS NOT NULL THEN
          v_answer := v_answer||',';    -- or whatever delimiter you prefer
       END IF;
       v_answer := v_answer||r.location;
   END LOOP
   RETURN v_answer;
END UNIQUE_LOCATION; 

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

RE: Oracle function that return multiple rows

(OP)
Chris,

Thanks for your reply. I appreciate it. For some reason I am getting this error when trying to compile the function:

Line Column Error
14 11 PLS-00103: Encountered the symbol "V_ANSWER"

For some reason its not liking RETURN v_answer;

Any suggestions?

Al

RE: Oracle function that return multiple rows

(OP)
Never mind. My Bad... ; was missing after END LOOP

I appreciate you and Carp taking time to answer my question.

RE: Oracle function that return multiple rows

You might not need to create your own function depending on the requirements. Here is a list of possible solutions.
There are a number of built in Oracle techniques that can be used.

http://www.oracle-base.com/articles/misc/string-ag...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close