Oracle function that return multiple rows 2

[AlStl]

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

 

[carp]

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)).

 

[AlStl]

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

 

[AlStl]

Crap,

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

Al

 

[carp]

Al -

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

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.

 

[AlStl]

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

 

[ChrisHunt]

Something like this?

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

 

[AlStl]

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

 

[AlStl]

Never mind. My Bad... ; was missing after END LOOP

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

 

[cmmrfrds]

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-aggregation-techniques.php