6 Replies Latest reply: May 7, 2012 9:11 AM by 935360 RSS

    Get the column names of a query (in own plugin)

    935360
      Hi,

      I'm developing my first plugin. It is a region plugin where the plugin-user passes a sql query as the source that the plugin will turn into a formated report.
      I found how to retreive the query result by using APEX_PLUGIN_UTIL.GET_DATA functions. Since it is an array, it is simple to cycle through the data rows. But how can i figure out
      1) how many columns the query returns
      2) whats the name of the columns is?

      Thanks a lot in advance for any hint...

      Cheers, Hans
        • 1. Re: Get the column names of a query (in own plugin)
          Prabodh
          Not the easiest way, but you can use the DBMS_SQL package to parse the SQL and get this info out of it.

          Regards,
          • 2. Re: Get the column names of a query (in own plugin)
            935360
            Thanks!

            Do I have to replace all the bind variables, or is this automatically done by APEX?
            If I have to, how (since I do not know in advance which ones will propably will be used, and tring to replace every bind variable would be a bit over the top)?
            • 3. Re: Get the column names of a query (in own plugin)
              Tom Petrus
              You can replace bind vars with this code:
              lv_binds := wwv_flow_utilities.get_binds(lv_sql);
              
              FOR i IN 1..lv_binds.COUNT
              LOOP            
                 lv_sql := regexp_replace(lv_sql, UPPER(lv_binds(i)), ''''||v(LTRIM(UPPER(lv_binds(i)), ':'))||'''', 1, 0, 'i');
              END LOOP;
              Undocumented feature, but tremendously handy. Either this way, or parsing through your query looking for ':<anystring>' and replacing those.
              • 4. Re: Get the column names of a query (in own plugin)
                Prabodh
                If you pass the SQL as it is to APEX then it knows how to substitute the bind vars.
                However, DBMS_SQL does not. So, you should use DBMS_SQL only to get the column names, data types, etc. If you want to execute it within DBMS_SQL then you will need to substitute the values yourself using the V function (which is nothing but a synonym for APEX_UTIL.GET_SESSION_STATE), as suggested, is the easiest work around.

                Regards,
                • 5. Re: Get the column names of a query (in own plugin)
                  Patrick Wolf-Oracle
                  Hi Hans,

                  the package APEX_PLUGIN_UTIL provides everything you need. No need to do your own DBMS_SQL handling!

                  Have a look at the API documentation and the example of GET_DATA at http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_plugin_util.htm#BABFBIJD
                  The t_column_value_list record type is a two dimensional array defined as
                  type t_column_value_list  is table of wwv_flow_global.vc_arr2 index by pls_integer;
                  The first array contains an entry for each column.
                  l_column_value_list.COUNT
                  can be used to get the number of columns.
                  l_column_value_list(1).COUNT
                  can be used to get the number of rows. An example to iterate over all columns and rows:
                  for l_column in 1 .. l_column_value_list.count loop
                      for l_row in 1 .. l_column_value_list(l_column).count loop
                          sys.htp.p('column#=' || l_column || ' row=' || l_row || ' value: ' || l_column_value_list(l_column)(l_row));
                      end loop;
                  end loop;
                  If you need the name of the columns and maybe the original data type and not everything converted to VARCHAR2 you have to use the more sophisticated GET_DATA2. See http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_plugin_util.htm#BABFJHAI

                  This function returns the record type t_column_value_list2 which is defined as
                  type t_value is record (
                      varchar2_value      varchar2(32767),
                      number_value        number,
                      date_value          date,
                      timestamp_value     timestamp,
                      timestamp_tz_value  timestamp with time zone,
                      timestamp_ltz_value timestamp with local time zone,
                      interval_y2m_value  interval year to month,
                      interval_d2s_value  interval day to second,
                      blob_value          blob,
                      bfile_value         bfile,
                      clob_value          clob );
                  
                  type t_value_list is table of t_value index by pls_integer;
                  
                  type t_column_values is record (
                      name       varchar2(32767),
                      data_type  varchar2(20), /* use c_data_type_* constants to compare */
                      value_list t_value_list );
                  
                  type t_column_value_list2 is table of t_column_values         index by pls_integer;
                  Again, it's some kind of two dimensional array. The first dimensions are the columns. But this time you also get some details of the parsed column. For example:
                  for l_column in 1 .. l_column_value_list2.COUNT loop
                      sys.htp.p('Column Name: ' || l_column_value_list2(l_column).name || ' Data Type: ' || l_column_value_list2(l_column).data_type );
                  end loop;
                  Will return you all the columns of the SQL statement. Using "name" and "data_type" you can get the column name and the data type. To access the values you can use
                  for l_column in 1 .. l_column_value_list2.COUNT loop
                      sys.htp.p('Column Name: ' || l_column_value_list2(l_column).name || ' Data Type: ' || l_column_value_list2(l_column).data_type );
                  
                      for l_rows in 1 .. l_column_value_list2(l_column).value_list.COUNT loop
                          sys.htp.p(
                              'row=' || l_row || ' value: ' ||
                              apex_plugin_util.get_value_as_varchar2 (
                                  p_data_type => l_column_value_list2(l_column).data_type,
                                  p_value     => l_column_value_list2(l_column).value_list(l_row) ); 
                              );
                      end loop;
                  end loop;
                  I used apex_plugin_util.get_value_as_varchar2 to always get a VARCHAR2, independent of the actual data type of the column. If you need the original data type, use "number_value", ... of the t_value record type.

                  Hope that gives you a direction
                  Patrick

                  Regards
                  Patrick
                  -----------
                  My Blog: http://www.inside-oracle-apex.com
                  APEX Plug-Ins: http://apex.oracle.com/plugins
                  Twitter: http://www.twitter.com/patrickwolf
                  • 6. Re: Get the column names of a query (in own plugin)
                    935360
                    Thanks Patrick!

                    obviously I did not read the doc properly enough. I did not see all the possibilities it grands me (specially the GET_DATA2). I did not even read thatthe first "row" of the array contains the meta-data.

                    I think now I have every information I need!