pl sql related problem

[Ranveer K Kumar]

Hi all,

I am very new to PL SQL (just started to learn).
Facing problem in loop.
My requirement is to fetch first query using distinct. upon the result (cursor) I need to get the result for final out put.
more definition : in the distinct I am getting id and cid. total no of distinct result is 12, so I want to pass the value return by distinct query.
I think 12 time loop (count) will require. but I dont know how to execute this approach.

following is my code :

create or replace
PACKAGE BODY PKG_ARTICLE_FOR_INDEX AS

  PROCEDURE SP_ARTICLE_FOR_INDEX
(
    --INUSERID   IN VARCHAR2,
    RET_REC OUT T_CURSOR
    RET_DIST OUT T_CURSOR
) 
IS

	v_count number := 0;
	v_sqlstmt varchar2(2000) := null;
  BEGIN
  
  

			select count(*)
			into v_count
			from (SELECT DISTINCT a_lang.article_id, a_lang.language_id FROM ARTICLE_LANGUAGE a_lang);

                       SELECT DISTINCT article_id, language_id
			into RET_DIST
			FROM ARTICLE_LANGUAGE;
      
                        v_sqlstmt := 'SELECT * FROM ARTICLE_LANGUAGE a_lang' ;
			
			dbms_output.put_line('v_count - ' || v_count) ;
    /* TODO implementation required */
    open RET_REC for v_sqlstmt;
  END SP_ARTICLE_FOR_INDEX;

END PKG_ARTICLE_FOR_INDEX;

in above RET_DIST is geting 12 record count..
now from RET_DIST I want to pass language_id and article_id to LOOP (12 time) and LOOP should return a CURSOR..
Please help me. Also tell me if any better aproach for same problem..

[Ireneusz Kordal]

Try this (tested on 11g):

set serveroutput on;

DECLARE 
  TYPE CURTYP IS REF CURSOR RETURN ARTICLE_LANGUAGE%ROWTYPE;
 
  DIST_NBR NUMBER;
  RET_CUR CURTYP;
  article_rec ARTICLE_LANGUAGE%ROWTYPE;
  
  PROCEDURE SP_ARTICLE_FOR_INDEX  (
    RET_REC OUT CURTYP,
    RET_DIST OUT NUMBER  
  )
  AS
  BEGIN
     SELECT COUNT(*) INTO RET_DIST
     FROM (select distinct * from ARTICLE_LANGUAGE );
     
     OPEN RET_REC FOR SELECT * FROM ARTICLE_LANGUAGE A_LANG;
  end;
  
BEGIN
  SP_ARTICLE_FOR_INDEX( RET_CUR, DIST_NBR );  -- call procedure
  DBMS_OUTPUT.PUT_LINE('Number of distinct rows = ' || DIST_NBR );
  IF RET_CUR%ISOPEN THEN
    LOOP
        FETCH RET_CUR INTO ARTICLE_REC;
        EXIT WHEN RET_CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Fetched article: ID = ' || ARTICLE_REC.ARTICLE_ID 
                   || '  LANG = ' || article_rec.LANGUAGE_ID);
    END LOOP;
    CLOSE RET_CUR;
  ELSE
    raise_application_error(20002,'Cursor not open.');
  END IF;
end;
/


anonymous block completed
Number of distinct rows = 7
Fetched article: ID = 1  LANG = PL
Fetched article: ID = 1  LANG = HU
Fetched article: ID = 1  LANG = ENG
Fetched article: ID = 2  LANG = US
Fetched article: ID = 2  LANG = PL
Fetched article: ID = 2  LANG = PL
Fetched article: ID = 3  LANG = US
Fetched article: ID = 3  LANG = US
Fetched article: ID = 4  LANG = US
Fetched article: ID = 4  LANG = US