0

I have the below user defined table:

CREATE OR REPLACE TYPE number_ntt AS TABLE OF varchar2(500);

Also I have the below function:

CREATE OR REPLACE FUNCTION TO_STRINGS (
                    nt_in        IN number_ntt,
                  delimiter_in IN VARCHAR2 DEFAULT ','
                    ) RETURN VARCHAR2 IS
      v_idx PLS_INTEGER;
      v_str VARCHAR2(32767);
      v_dlm VARCHAR2(10);    
   BEGIN    
      v_idx := nt_in.FIRST;
      WHILE v_idx IS NOT NULL LOOP
         v_str := v_str || v_dlm || nt_in(v_idx);
         v_dlm := delimiter_in;
         v_idx := nt_in.NEXT(v_idx);
      END LOOP;    
      RETURN v_str;    
   END TO_STRINGS;
/
EXIT;

I have the below two tables:(Image attachedTables)

Now, I am running the below query:

SELECT A.PERSONNUMBER,
(select TO_STRINGS
(CAST(COLLECT(C.LOCATIONNAME) as number_ntt)
) AS cnt 
from client.Tab_EMPLOYEES B JOIN client.Tab_Locations C ON B.MOBILELOCATIONID = C.MOBILELOCATIONID 
WHERE B.PERSONNUMBER = A.PERSONNUMBER)  as Assignedlocations
FROM client.Tab_EMPLOYEES A WHERE A.MOBILELOCATIONID = '100';

But I am getting the below error:

ORA-06502:PL/SQL:numeric or value error:character string buffer too small ORA-06512:at line 1.

I believe either I need to modify the TO_STRINGS function or the query; any help will be highly appretiated.

Thanks in advance.

Aleksej
  • 22,443
  • 5
  • 33
  • 38
user2948533
  • 1,143
  • 3
  • 13
  • 32
  • 2
    Please,[not screenshots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) – Aleksej Mar 03 '17 at 06:14
  • Is there a `LOCATIONNAME` column in `Tab_Locations`? – Nitish Mar 03 '17 at 06:20
  • Is it possible that the result of the string concatenation exceeds the limit? Also, you seem to need a way to concatenate strings, are you sure you need a user defined function? Can you please post some sample data and desired result ? – Aleksej Mar 03 '17 at 06:25
  • Do you need to write your own function? Modern versions of Oracle have a host of built-ins to efficiently aggregate strings. [See this SO thread](http://stackoverflow.com/q/4686543/146325) – APC Mar 03 '17 at 07:20

1 Answers1

0

Basically what i understand here is the requirement is to get a set of locations based on person i.e. a aggregated list based on delimiter. That can be done by LISTAGG function (if Oracle version is equivalent or > 11g) or WM_CONCAT can be used (note this is undocumented function from Oracle)

CREATE TABLE AVR_1
  (SR NUMBER, A1 NUMBER_NTT
  )NESTED TABLE A1 STORE AS A1_TAB ;

INSERT INTO AVR_1 VALUES
  ( 1,NUMBER_NTT(1,5,2)
  );

INSERT INTO AVR_1 VALUES
  ( 2,NUMBER_NTT(7,6)
  );


    --Customized function to get output

    CREATE OR REPLACE
  FUNCTION TO_STR(
      P_IN in number,
      p_delimiter IN VARCHAR2 DEFAULT ',')
    RETURN VARCHAR2
  AS
    lv_out VARCHAR2(32676);
  BEGIN
    FOR I IN
    (SELECT a.SR,
      B.column_value val
    FROM AVR_1 a,
      table(a.A1) B
    WHERE a.SR = P_IN
    )
    LOOP
      LV_OUT:=LV_OUT||p_delimiter||I.VAL;
    end LOOP;
    RETURN substr(lv_out,2,length(lv_out));
  EXCEPTION
  WHEN OTHERS THEN
    RETURN ' ';
  END;


--For result

SELECT TO_STR(1,',') FROM DUAL;
Avrajit Roy
  • 3,233
  • 1
  • 13
  • 25
  • You are correct but we have Oracle 10 and 11 in different Prod Box so, ListAgg or WM_Concat will not work..need a solution which works across Oracle versions. – user2948533 Mar 03 '17 at 09:36
  • I have modified the code a bit as per requirement. Now this has to work across al Oracle platforms. Hope this helps. – Avrajit Roy Mar 03 '17 at 09:54
  • Thank you, but getting this error:ORA-06553: PLS-306: wrong number or types of arguments in call to 'TO_STR' – user2948533 Mar 04 '17 at 06:49
  • Its working correctly. Please check the argumemenst passing to the function. – Avrajit Roy Mar 06 '17 at 09:07