CODEWITHSUNDEEP
oracle-databaseplsql
Martin Míňa Kunz
Martin Míňa Kunz

Reputation: 43

Oracle single row into variable

I need to get all result in one row... it is working but when i want to see it in dbms there is nothink.. why ? 

  CREATE OR REPLACE PROCEDURE NXMESEP.SP_IN_CHECK_AND_SEND_SMS

(    RC_TABLE0   OUT SYS_REFCURSOR,
    RS_CODE            OUT        VARCHAR2,     -- RETURN 코드
    RS_MSG             OUT        VARCHAR2
    ) IS   ERROR_EXCEPTION            EXCEPTION;

BEGIN 

     begin
        DECLARE
        promena varchar2(32767);

            BEGIN

                OPEN RC_TABLE0 FOR
                SELECT listagg(ITEM_ID,', ') within group(order by ITEM_ID)
                 INTO promena
                  FROM TB_PL_M_WRKORD WRKOD
                WHERE 1 = 1
                AND WO_DATE = '20181012'
                AND WRKOD.ITEM_ID NOT IN (SELECT ITEM_ID FROM TB_CM_M_FERT_COST_CHK FERT)
                AND WC_ID = 'U';
                LOOP
                FETCH rc_table0 INTO promena;
                EXIT WHEN rc_table0%NOTFOUND;
                dbms_output.put_line(promena);
                END LOOP;
                  CLOSE rc_table0;

               end;
                   EXCEPTION
    ....    END;

   RS_CODE := 'S';    RS_MSG := 'Complete successfully!';

   RETURN; END SP_CHECK_AND_SEND_SMS; /

This should be promena that i expected..

" 12993NXUA, 13595NXUA, 14495NXUA, 16589NX, 16589NX, 16590NX, 16590NX, 16622NX, 16622NX "

Now it is working but im getting unknown error ORA-65535 every time when i execute. But after this I can see dbms result is ok.

Upvotes: 0

Views: 821

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Assuming your real code has RC_TABLE0 declared, as a ref cursor, then your variable ends up null because opening the cursor into something doesn't really do anything. You can't open a cursor and select something from the cursor query into a separate variable at the same time, whichever way round you try to do it. You need either a cursor, or a simple select ... into:

DECLARE
  promena varchar2(32767);
BEGIN
  SELECT listagg(ITEM_ID,', ') within group (order by ITEM_ID)
  INTO promena
  FROM TB_PL_M_WRKORD WRKOD
  WHERE 1 = 1
  AND WO_DATE = '20181012'
  AND WRKOD.ITEM_ID NOT IN (SELECT ITEM_ID FROM TB_CM_M_FERT_COST_CHK FERT)
  AND WC_ID = 'U';

  dbms_output.put_line('test: '||promena);
END;
/

test: 12993NXUA, 13595NXUA, 14495NXUA ...

PL/SQL procedure successfully completed.

You also have to set serveroutput on or equivalent to actually see the results, of course.

I've also removed the redundant distinct, the unnecessary select .. from dual - which seemed to be part of the odd cursor construct - and the extra level of begin/end.

Incidentally, your code implies that wo_date is a string, which seems unlikely, or at least not ideal. If it is actually a real date then you should not be using a string for the comparison as you're forcing implicit conversions; use an actual date instead, maybe as an ANSI date literal:

  AND WO_DATE = DATE '2018-10-12'

If you did really want to use an explicit cursor approach you would need to use a loop to populate the string variable:

DECLARE
  promena varchar2(32767);
  rc_table0 sys_refcursor;
BEGIN
  OPEN rc_table0 FOR
    SELECT DISTINCT listagg(ITEM_ID,', ') within group (order by ITEM_ID)
    FROM TB_PL_M_WRKORD WRKOD
    WHERE 1 = 1
    AND WO_DATE = '20181012'
    AND WRKOD.ITEM_ID NOT IN (SELECT ITEM_ID FROM TB_CM_M_FERT_COST_CHK FERT)
    AND WC_ID = 'U';

  LOOP
    FETCH rc_table0 INTO promena;
    EXIT WHEN rc_table0%NOTFOUND;
    dbms_output.put_line('test: '||promena);
  END LOOP;

  CLOSE rc_table0;
END;
/

As you're only expecting a single row back there isn't much point doing that; and if you expected multiple rows (from a modified query, e.g. getting several days data and grouping by day) then an implicit cursor would be simpler anyway:

BEGIN
  FOR r IN (
    SELECT DISTINCT listagg(ITEM_ID,', ') within group (order by ITEM_ID) AS promena
    FROM TB_PL_M_WRKORD WRKOD
    WHERE 1 = 1
    AND WO_DATE = '20181012'
    AND WRKOD.ITEM_ID NOT IN (SELECT ITEM_ID FROM TB_CM_M_FERT_COST_CHK FERT)
    AND WC_ID = 'U'
  )
  LOOP
    dbms_output.put_line('test: '||r.promena);
  END LOOP;
END;
/

If this is really part of a procedure and the rc_table0 is an OUT parameter then you just can't do this. In code you posted as an answer you tried:

OPEN RC_TABLE0 FOR
  SELECT listagg(ITEM_ID,', ') within group(order by ITEM_ID)
  INTO promena
  FROM TB_PL_M_WRKORD WRKOD
  ...

In that construct the into is still ignored, because the open doesn't fetch anything. And if you loop and fetch inside your procedure to display the results as I did above then you are consuming the result set, so the caller will get no results (or "ORA-01001: invalid cursor" if you close it inside the procedure).

You just can't do both, unless you re-open the cursor, which seems like overhead you probably don't want...

Upvotes: 3

Related Questions