10 Replies Latest reply: Nov 27, 2013 11:01 AM by Sunil Bhatia - Freelancer RSS

    APEX_UTIL.JSON from SQL into variable

    Sunil Bhatia - Freelancer

      Hi all,

       

      How can we store resultant from APEX_UTIL.JSON_FROM_SQL('select a,b,c from dual') into a PL SQL variable? Any help. Urgent!!

       

      Thanks

      Sunil bhatia

        • 1. Re: APEX_UTIL.JSON from SQL into variable
          fac586

          SunilBhatia wrote:

           

          How can we store resultant from APEX_UTIL.JSON_FROM_SQL('select a,b,c from dual') into a PL SQL variable? Any help. Urgent!!

           

          It's no more or less "urgent" than any other post here. In a critical situation, raise a priority 1 SR with Oracle support.


          As the APEX_UTIL.JSON_FROM_* methods remain undocumented (despite multiple promises from Oracle that this would be addressed) you are recommended to use a fully featured JSON utility such as PL/JSON.


          If you must attempt this in PL/SQL, you'd have to expose the APEX_UTIL.JSON_FROM_SQL('select a,b,c from dual') call in APEX as an On-Demand process, and read the OWA output from the resulting URI in PL/SQL using UTL_HTTP methods.

          • 2. Re: APEX_UTIL.JSON from SQL into variable
            Sunil Bhatia - Freelancer

            Thanks FAC,

             

            My requirement is to create a region plugin which takes input as JSON (from SQL) and output a grid to me. I have created complete structure of region plugin, and last thing left is the source reading as

             

            APEX_UTIL.JSON_FROM_SQL('select a,b,c from dual');


            Once this is done, this will finish my plugin work. I hope i am more clear in requirement now. I cannot use PL/JSON, as then i have to depend on that package each time i have to move plugin to different workspace.


            Thanks

            Sunil Bhatia

            • 3. Re: APEX_UTIL.JSON from SQL into variable
              fac586

              SunilBhatia wrote:

               

              My requirement is to create a region plugin which takes input as JSON (from SQL) and output a grid to me. I have created complete structure of region plugin, and last thing left is the source reading as

               

              APEX_UTIL.JSON_FROM_SQL('select a,b,c from dual');


              Once this is done, this will finish my plugin work. I hope i am more clear in requirement now. I cannot use PL/JSON, as then i have to depend on that package each time i have to move plugin to different workspace.

              You'll have to create your own code to generate the output in JSON format for it to be self-contained.

              • 4. Re: APEX_UTIL.JSON from SQL into variable
                Sunil Bhatia - Freelancer

                Correct,

                 

                So i return to my original question. Has anyone tried to return value of APEX_UTIL.JSON_FROM_SQL('select a,b,c from dual') into a PL SQL variable, OR it is not possible? Any help would be appreciated.

                 

                Thanks

                Sunil Bhatia

                • 5. Re: APEX_UTIL.JSON from SQL into variable
                  jariola

                  Hi,

                   

                  Maybe I do not understand.

                   

                  I assume you call APEX_UTIL in process.

                  If you need store select values to PL/SQL variables, why you create JSON ?

                   

                  Why not use simple thing like SELECT a,b,c INTO ...

                   

                  Regards,

                  Jari

                  • 6. Re: APEX_UTIL.JSON from SQL into variable
                    Sunil Bhatia - Freelancer

                    Hi jarola,

                     

                    I need to convert to JSON because jqxgrid (the plugin which i am creating) takes input parameter as json. So first i need to take SQL input from user creating this region, and then in plugin i need to convert this SQL into JSON.

                     

                    var source =

                                {

                                    datatype: "json",               

                                    localdata: ''' || region.source || '''

                                };

                     

                    Here region.source is my SQL statement input. I need to convert this region.source statement into JSON using that utility

                     

                    Thanks

                    Sunil Bhatia

                    • 7. Re: APEX_UTIL.JSON from SQL into variable
                      ascheffer

                      You could try using htp.get_page, but it will probably easier to write your own sql2json using dbms_sql

                      • 8. Re: APEX_UTIL.JSON from SQL into variable
                        Sunil Bhatia - Freelancer

                        hmmmmm.... ok

                         

                        perfect then.. i will try to write my own and will update this forum once successful.. thanks everyone

                        • 9. Re: APEX_UTIL.JSON from SQL into variable
                          ascheffer
                          declare
                            function sql2json( p_sql varchar2 )
                            return varchar2
                            is
                              t_cur pls_integer;
                              t_cols dbms_sql.desc_tab2;
                              t_ncols pls_integer;
                              t_dummy pls_integer;
                              t_d date;
                              t_n number;
                              t_v varchar2(4000 char);
                              t_json varchar2(32767);
                              t_object varchar2(32767);
                            begin
                              t_cur := dbms_sql.open_cursor;
                              dbms_sql.parse( t_cur, p_sql, dbms_sql.native );
                              dbms_sql.describe_columns2( t_cur, t_ncols, t_cols );
                              for i in 1 .. t_ncols
                              loop
                                case
                                  when t_cols(i).col_type in ( 2, 100, 101 )
                                  then
                                    dbms_sql.define_column( t_cur, i, t_n );
                                  when t_cols(i).col_type in ( 12, 180, 181, 231 )
                                  then
                                    dbms_sql.define_column( t_cur, i, t_d );
                                  else
                                    dbms_sql.define_column( t_cur, i, t_v, 4000 );
                                end case;
                              end loop;
                              t_dummy := dbms_sql.execute( t_cur );
                              while dbms_sql.fetch_rows( t_cur ) > 0
                              loop
                                t_object := '{';
                                for i in 1 .. t_ncols
                                loop
                                  t_object := t_object || case when i > 1 then ',' end || '"' || t_cols(i).col_name || '":';
                                  case
                                    when t_cols(i).col_type in ( 2, 100, 101 )
                                    then
                                      dbms_sql.column_value( t_cur, i, t_n );
                                      t_object := t_object || nvl( to_char( t_n, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,' ), 'null' );
                                    when t_cols(i).col_type in ( 12, 180, 181, 231 )
                                    then
                                      dbms_sql.column_value( t_cur, i, t_d );
                                      t_object := t_object || '"' || to_char( t_d, 'dd-mm-yyyy' ) || '"';
                                    else
                                      dbms_sql.column_value( t_cur, i, t_v );
                                      t_object := t_object || '"' || replace(
                                                                     replace(
                                                                     replace(
                                                                     replace(
                                                                     replace(
                                                                     replace(
                                                                     replace(
                                                                     replace( t_v
                                                                            , '\', '\\' )
                                                                            , '"', '\"' )
                                                                            , '/', '\/' )
                                                                            , chr(8), '\b' )
                                                                            , chr(12), '\f' )
                                                                            , chr(10), '\n' )
                                                                            , chr(13), '\r' )
                                                                            , chr(9), '\t' ) || '"';
                                  end case;
                                end loop;
                                t_json := t_json || case when t_json is null then '[' else ',' end || t_object || '}';
                              end loop;
                              dbms_sql.close_cursor( t_cur );
                              return nvl( t_json, '[' ) || ']';
                            end;
                          begin
                            dbms_output.put_line( sql2json( q'~select 'test' || level a, level b, sysdate c from dual connect by level <= 5~' ) );
                          end;
                          
                          
                          • 10. Re: APEX_UTIL.JSON from SQL into variable
                            Sunil Bhatia - Freelancer

                            Ohhh Wow... Great ascheffer, thats what i needed. Thanks alot.

                             

                            Sunil Bhatia