Return Values ​​PLSql

Question

Here I go again with plsql ..

I want to know if there is any way to use the following function, for example, to choose 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 will look like this:

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 mine table, but the results are formatted as "1234,2434,424,45,767,43", and not the result of the table from the query. It will be used later in the code for various purposes, including other requests, where I could just do it in ([variable with that string]).

The fact is that with the help dbms_output.Put_line(outpt);I can’t access it from my application level and it seems that in PLSQL I can’t use returnit without turning it into a function.

- ? PlSql, script.

!

.

Answer 1

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

Answer 2

Oralce 11 r2, LISTAGG http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm

( >= 9i, SQL 11r2): http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php ( , ROW_NUMBER() SYS_CONNECT_BY_PATH Oracle 9i sql)

Answer 3

Another option is to define some I / O variables, and instead returnassign the result value to the output variable.