PLSql return values

Question

Here I go again with some PLSql..

I want to know, if there's any way I can use the following function like a select without having to turn it into a function or procedure (so I can see the code from the script where it is contained).

The code would be like:

DECLARE
    outpt  VARCHAR2(1000) := ''; 
    flow_rI  VARCHAR2(50); 
    CURSOR flow_r IS
      select flow_run_id
        from table
        where CREATED_DATE < sysdate - 32
        and rownum < 10
        order by 1 desc;
BEGIN 
    OPEN flow_r;
    LOOP 
        FETCH flow_r INTO flow_rI; 
        EXIT WHEN flow_r%notfound; 
        BEGIN 
            outpt := outpt ||  ',' || flow_rI;
        EXCEPTION 
            WHEN no_data_found THEN 
              dbms_output.Put_line(outpt); 
        END; 
    END LOOP;
    dbms_output.Put_line(outpt); 
    outpt := '';
    CLOSE flow_r;
END;

The idea is simple, I just want to get a series of codes from my table but having the results formatted like "1234,2434,424,45,767,43" sort of thing, rather than the table result from a query. It will be used later in the code for various purposes including another queries where I could simply do an in ([variable with that string]).

Thing is that using the dbms_output.Put_line(outpt);, I can't access it from my application layer and seems that in plsql I cannot use the return without turning it into a function.

Would anyone have an idea? It doesn't need to be a PlSql like that as long as I can have the whole code in the script.

thanks!

f.

ps.: yeah, I know I have a leftover "," in the beginning of the string -_- — filippo
– filippo, Commented Oct 14, 2010 at 14:18

Michael Pakhantsov · Accepted Answer · 2010-10-14 15:45:02Z

13

Why not use just sql:

   SELECT MAX(ltrim(sys_connect_by_path(flow_run_id, ','), ','))
   FROM
   (
    select flow_run_id, rownum rn
    from table
    where CREATED_DATE < sysdate - 32
    and rownum < 10
    order by 1 desc
   )
   START WITH rn = 1
   CONNECT BY PRIOR rn = rn - 1

edited Oct 14, 2010 at 15:45

answered Oct 14, 2010 at 14:25

Michael Pakhantsov

25.5k7 gold badges63 silver badges60 bronze badges

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

5 Comments

filippo Over a year ago

That my friend, is because I'm too newbie to know beauties like sys_connect_by_path :) Thank you very much! Works like a charm. f.

DCookie Over a year ago

+1, nice answer, good illustration of CONNECT BY PRIOR. I did try this on a table I have and it doesn't yield the same answer as OP, but I think the concept is correct and I don't have time to debug it.

filippo Over a year ago

ops... I think this would be predictable but I've got this error ORA-01489 (output string is too long). Well, I reckon it would be better to deal with that in the code, but, just in case, would you have a suggestion for that ?

Michael Pakhantsov Over a year ago

@flpgdt, result of sys_connect_by_path can't be more than 4000 char (VARCHAR2 limitation) so if you need longer string, you need have to write function which will be return CLOB

filippo Over a year ago

I see. Vincent gave me a work around with sql (using multiple rows, which solves for me) stackoverflow.com/questions/3975967/… Works pretty awesomely. Thanks!

Harrison · Accepted Answer · 2010-10-14 14:23:42Z

1

if you are on Oralce 11 r2 you can use LISTAGG to do exactly this http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm

but this site has how you can do it an any version (but you need to be >=9i to do it in pure SQL with 11r2 being ideal): http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php (specifically look at ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i for a pure sql )

answered Oct 14, 2010 at 14:23

Harrison

9,1401 gold badge34 silver badges29 bronze badges

Comments

ZeissS · Accepted Answer · 2010-10-14 15:20:59Z

0

Another option would be to define some input/output variables and instead of return assign your result value to an output variable.

answered Oct 14, 2010 at 15:20

ZeissS

12.2k4 gold badges38 silver badges50 bronze badges

Comments

Adrian Mole · Accepted Answer · 2021-08-07 18:31:48Z

0

You can use simple method WM_CONCAT as a solution.

-- It will show the output in comma separated form
-- you can change the delimiter as well if needed
-- 1234,2434,424,45,767,43
SELECT WM_CONCAT (flow_run_id)
FROM   table_name
WHERE  created_date < SYSDATE - 32
       AND ROWNUM < 10;

edited Aug 7, 2021 at 18:31

Adrian Mole

52.1k193 gold badges61 silver badges101 bronze badges

answered Aug 7, 2021 at 3:53

Shinto Joy

337 bronze badges

Collectives™ on Stack Overflow

PLSql return values

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related