How can I create a SQL function with a parameter?

Question

The following code returns one field, a string, based on the input where it says "28".

SELECT data.id, LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
  FROM (
     SELECT id, name, ROW_NUMBER() OVER (order by name) rownumber, COUNT(*) OVER () cnt
      FROM (
         SELECT es.EVENT_ID as id, s.SERVICE_NAME as name FROM DT_SERVICES s
            JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
         WHERE es.EVENT_ID = 28
      )
) data
WHERE rownumber = cnt
START WITH rownumber = 1
CONNECT BY PRIOR rownumber = rownumber-1;

How can I create a SQL function out of this so that I can pass in whatever number (instead of 28) and the function would return whatever the result of that select turns out to be?

I tried creating one but I keep getting compilation errors.

Current SQL to create function

create or replace function "DT_SERVICE_STRING" (id in VARCHAR2)
  return VARCHAR2 is
begin
  DECLARE 
     result VARCHAR(200);
  SELECT data.id, LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
    INTO result
    FROM (
      SELECT id, name, ROW_NUMBER() OVER (order by name) rownumber, COUNT(*) OVER () cnt
      FROM (
        SELECT es.EVENT_ID as id, s.SERVICE_NAME as name FROM DT_SERVICES s
        JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
        WHERE es.EVENT_ID = id
      )
  ) data
  WHERE rownumber = cnt
  START WITH rownumber = 1
  CONNECT BY PRIOR rownumber = rownumber-1;

  return result;
end;

Error:
Compilation failed,line 7 (15:22:21) PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior

and what does it look like, that isn't working? update your question with your existing but broken code. — Alexander Corwin
– Alexander Corwin, Commented Mar 14, 2012 at 19:19

Justin Cave · Accepted Answer · 2012-03-14 19:29:47Z

4

Assuming that you want a PL/SQL function that is callable from a SQL statement (you cannot have a function defined in SQL), it sounds like you want something like

CREATE OR REPLACE FUNCTION get_conc_names( p_event_id IN dt_event_service.event_id%type )
  RETURN VARCHAR2
IS
  l_conc_names VARCHAR2(32676); 
  -- You may want a smaller variable if you know the result will be smaller
BEGIN
  SELECT LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
    INTO l_conc_names
    FROM (
      SELECT id, name, ROW_NUMBER() OVER (order by name) rownumber, COUNT(*) OVER () cnt
        FROM (SELECT es.EVENT_ID as id, s.SERVICE_NAME as name 
                FROM DT_SERVICES s
                     JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
               WHERE es.EVENT_ID = p_event_id )
          ) data
   WHERE rownumber = cnt
   START WITH rownumber = 1
 CONNECT BY PRIOR rownumber = rownumber-1;
  RETURN l_conc_names;
END;

Based on the code you just posted, it appears that you just need to get rid of the DECLARE and to move the declaration of the local variable result before the BEGIN and after the IS.

edited Mar 14, 2012 at 19:29

answered Mar 14, 2012 at 19:23

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

antonpug Over a year ago

Did. Now I get this: Compilation failed,line 8 (15:27:25) PL/SQL: ORA-00947: not enough valuesCompilation failed,line 7 (15:27:25) PL/SQL: SQL Statement ignored

Justin Cave Over a year ago

@antonpug - I updated my answer. Didn't notice that your SELECT statement was selecting two columns. Since you're only returning one value, I assume that you really only need to select the conc_names string.

antonpug Over a year ago

Hm, that's strange, the same error persists. And yeah i noticed the two columns myself as well. It keeps telling me Not Enough Values

Beverly · Accepted Answer · 2012-03-16 00:40:50Z

I think the analytic functions need to be inside the inner inline view and then the outer inline view can select them--that's how I've always done it. Try this instead:

CREATE OR REPLACE FUNCTION get_conc_names( p_event_id IN dt_event_service.event_id%type ) 
  RETURN VARCHAR2 
IS 
  l_conc_names VARCHAR2(32676);  
  -- You may want a smaller variable if you know the result will be smaller 
BEGIN 
  SELECT LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names 
    INTO l_conc_names 
    FROM ( 
      SELECT id, name, rownumber, cnt 
        FROM (SELECT es.EVENT_ID as id
                    ,s.SERVICE_NAME as name
                    ,ROW_NUMBER() OVER (order by name) as rownumber
                    ,COUNT(*) OVER () as cnt
                FROM DT_SERVICES s 
                     JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID 
               WHERE es.EVENT_ID = p_event_id ) 
             ) data 
    WHERE rownumber = cnt 
    START WITH rownumber = 1 
    CONNECT BY PRIOR rownumber = rownumber-1; 
  RETURN l_conc_names; 
END;

Collectives™ on Stack Overflow

How can I create a SQL function with a parameter?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related