0 Replies Latest reply on Aug 24, 2017 4:24 PM by hsemar

    How to pass an array of variables through ORDS

    hsemar

      How to pass an array of variables through ORDS GET.

       

      create or replace TYPE TAGID_T

      AS VARRAY(200) OF VARCHAR2(5);

       

      I have custom type  array as an input for the procedure.  The procedure is used in ORDS GET functionality.   How to pass multiple values/array of values to the ORDS functionality..

       

      create or replace TYPE TAGID_T

      AS VARRAY(200) OF VARCHAR2(5);

       

        ORDS.DEFINE_TEMPLATE(

            p_module_name    => 'cmdb',

            p_pattern        => 'ostype/:tagid',

            p_priority       => 0,

            p_etag_type      => 'HASH',

            p_etag_query     => NULL,

            p_comments       => NULL);

        ORDS.DEFINE_HANDLER(

            p_module_name    => 'cmdb',

            p_pattern        => 'ostype/:tagid',

            p_method         => 'GET',

            p_source_type    => 'plsql/block',

            p_items_per_page =>  25,

            p_mimes_allowed  => '',

            p_comments       => NULL,

            p_source         =>

      'BEGIN ords_custom.get_ostype_json(TAGID_T(:tagid)); END;'

            );

       

       

      create or replace PACKAGE ORDS_CUSTOM AS

        PROCEDURE get_ostype_json(

          p_tagid TAGID_T  DEFAULT NULL

          );

      END ORDS_CUSTOM;

      create or replace PACKAGE BODY ORDS_CUSTOM AS

       

        PROCEDURE get_ostype_json

         ( p_tagid IN TAGID_T)

      AS

       

        str1  varchar2(4000);

        query varchar2(4000);

       

        l_cursor SYS_REFCURSOR;

       

        BEGIN

               str1:='';

        IF P_TAGID is NULL THEN

         -- QUERY:='select distinct (os_type) as name,count(os_type) as uv from host group by os_type';

          -- TODO: Implementation required for PROCEDURE ORDS_CUSTOM.ostype

              --    dbms_output.put_line(query);

          OPEN l_cursor FOR

                  select distinct (os_type) as name,count(os_type) as uv from host group by os_type;                         

              APEX_JSON.open_object;

              APEX_JSON.write('ostype', l_cursor);

              APEX_JSON.close_object;     

                ELSE

             -- dbms_output.put_line('hello');

          --        dbms_output.put_line(p_tagid.count);

        FOR i IN 1..p_tagid.count LOOP

             if str1 is NULL THEN

                str1:=p_tagid(i);

              ELSE

               str1:=str1||','||p_tagid(i);

              end if;

             --tmp:= cast(p_tagid(i) as varchar2);

           dbms_output.put_line(p_tagid(i));

          END LOOP;

                  query:='select distinct (A.os_type) as name,count(A.os_type) as uv from host A,HOST_TAG B WHERE A.HOST_ID=B.HOST_ID AND B.TAG_id IN ('||str1||')group by os_type';                         

                OPEN l_cursor FOR query;

             --dbms_output.put_line(query);

            APEX_JSON.open_object;

            APEX_JSON.write('ostype', l_cursor);

            APEX_JSON.close_object;     

       

        END IF;

                 END get_ostype_json;

      END ORDS_CUSTOM;