1 2 Previous Next 22 Replies Latest reply on Apr 14, 2015 4:17 PM by fac586 Go to original post
      • 15. Re: function creating dynamic sql throwing error ora-00932
        fac586

        Don-586198 wrote:

         

        Sorry for being vague... I received the ORA-01403.

        That indicates an internal APEX problem in rendering the report rather than your report not returning any data.

        I searched the forum and found a post suggesting to 'Use Generic Column Names'. Sure enough, it ran without error and produced the correct data. Problem is, of course, having to enter the specific column names defeats the purpose.

        I did suggest that it would be necessary to use Generic Columns in my earlier post. That also suggested using the Headings Type: PL/SQL report attribute to dynamically generate the column headings required.

        I then tried creating a stored function, but I again had difficulty referencing my Term Code page item.

        Not clear what you mean by that, nor is it obvious why you are having difficulty in referencing the page item value. All I can suggest is that you reproduce the problem on apex.oracle.com and share guest login credentials for the workspace so I can see it.

        • 16. Re: function creating dynamic sql throwing error ora-00932
          J-Lig

          This might be a dumb question, but why do you need the bind variable at all when you can pass your page item (variable value) into your function as a parameter and use the parameter within your generated query?

           

          l_qry := l_qry ||' where sfrstcr_term_code = ' || my_parm;


          Jen

          • 17. Re: Re: function creating dynamic sql throwing error ora-00932
            Huzaifa_Apex
            Problem is, of course, having to enter the specific column names defeats the purpose. I then tried creating a stored function, but I again had difficulty referencing my Term Code page item.

            As Paul has also suggested use Headings Type: PL/SQL. In that you can write your code to generate Column based on your report query..

             

            • First Create a function in your DB:
            CREATE  OR REPLACE FUNCTION query_column(v_sql IN VARCHAR2)
            return varchar2 IS
                   v_finaltxt     VARCHAR2(4000);
                   v_varchar2_val VARCHAR2(4000);
                   v_num_val      NUMBER;
                   v_dt_val       DATE;
                   v_ret          NUMBER;
                   cur_handle     NUMBER;
                   var1           NUMBER;
                   col_count      INTEGER;
                  rec_tab        DBMS_SQL.DESC_TAB;
                
                BEGIN
                   cur_handle := DBMS_SQL.OPEN_CURSOR;
                   DBMS_SQL.PARSE(cur_handle, v_sql, DBMS_SQL.NATIVE);
                   var1 := DBMS_SQL.EXECUTE(cur_handle);
                   DBMS_SQL.DESCRIBE_COLUMNS(cur_handle, col_count, rec_tab);
                   FOR j in 1 .. col_count LOOP
                     CASE rec_tab(j).col_type
                       WHEN 1 THEN
                         DBMS_SQL.DEFINE_COLUMN(cur_handle, j, v_varchar2_val, 2000);
                      WHEN 2 THEN
                         DBMS_SQL.DEFINE_COLUMN(cur_handle, j, v_num_val);
                       WHEN 12 THEN
                        DBMS_SQL.DEFINE_COLUMN(cur_handle, j, v_dt_val);
                       ELSE
                         DBMS_SQL.DEFINE_COLUMN(cur_handle, j, v_varchar2_val, 2000);
                     END CASE; END LOOP;
                 
                   FOR j in 1 .. col_count LOOP
                     v_finaltxt := ltrim(v_finaltxt || ':' || upper(rec_tab(j).col_name),
                                         ',');
                   END LOOP;
                   RETURN (LTRIM(v_finaltxt,':'));
                 
                    DBMS_SQL.CLOSE_CURSOR(cur_handle);
                 END;
            

             

            • Save your Dynamic Report query in some Hidden Item P2_QUERY
            • Then call above created function in your Header PLSQL. Column heading will be generated according to your dynamic report query
            DECLARE
            v_query varchar2(4000);
            v_q varchar2(4000);
            v_as varchar2(1000);
            begin
            v_q := :P2_QUERY;
            IF :P2_QUERY is NOT NULL THEN
            v_query := 'SELECT * FROM ('|| v_q||')';
            
            v_as :=query_column(v_query);
                   
            return v_as;
            END IF;
            end;
            

             

            For More details Refer: PL/SQL 101 : Cursors and SQL Projection

             

            Br,

            Zaif

            • 18. Re: function creating dynamic sql throwing error ora-00932
              Don-586198

              Thanks again, Paul, for your time and patience. This has been a bit of a learning experience for me. Having never written dynamic sql (nor had need to) , I relied on examples I found online... problem is, they weren't written in the context of being used in Apex. I see a subsequent post from Zaif seconding your suggestion with provided example. I may ultimately give it a go. Just one final question... When I started with this, I tried to create the dynamic sql via a stored function, but had difficulty referencing the page item. Was going this route not doable... is my only option to go the route of creating a region via PL/SQL function body returning SQL query?

               

              Thanks. 

              • 19. Re: function creating dynamic sql throwing error ora-00932
                Don-586198

                Hi Jen,

                 

                I tried referencing my page item all different ways, including as shown in your example. Many oracle errors later, I ultimately abandoned trying to create the dynamic sql via a stored function.

                 

                Thanks for your post.

                • 20. Re: function creating dynamic sql throwing error ora-00932
                  Don-586198

                  Hi Zaif,

                   

                  You've given me a whole lot to digest... If I end up attempting this approach, are there any limitations in terms of number of columns or report width?

                   

                  Thanks for your post.

                  • 21. Re: function creating dynamic sql throwing error ora-00932
                    J-Lig

                    We did something similar (plsql returning dynamic sql query) in a list on one of our pages. I had tons of issues until I started understanding session state and how to make the variables available to the database to process. Basically, if you are not submitting the page, then you have to inject your variables into the session in another way before you can use it in your back end process. There are multiple ways to do this. Also, depending on the type of page item and configuration of the page item they have different properties that potentially impact session state. Right now, we do it a little differently because our requirements changed, but at one point, we were passing our page item into the function as a parameter. That's why I was asking. I'm kind of surprised that didn't work for you. Sounds like you are on the road to a solution now though. Good luck!

                    • 22. Re: function creating dynamic sql throwing error ora-00932
                      fac586

                      Don-586198 wrote:

                       

                      Thanks again, Paul, for your time and patience. This has been a bit of a learning experience for me. Having never written dynamic sql (nor had need to) , I relied on examples I found online... problem is, they weren't written in the context of being used in Apex. I see a subsequent post from Zaif seconding your suggestion with provided example. I may ultimately give it a go. Just one final question... When I started with this, I tried to create the dynamic sql via a stored function, but had difficulty referencing the page item. Was going this route not doable... is my only option to go the route of creating a region via PL/SQL function body returning SQL query?

                      The v('item_name') function syntax must be used to reference APEX session state values in stored functions. However it's better to pass the required values to the program unit as parameters, using bind variable notation in the calling APEX block. As the generated SQL will be executed in an APEX context, bind variable notation (:item_name) can—and for performance reasons should—be used within the dynamic query text.


                      Given your requirements I would use the XML pivot method because it uses static SQL. The complexity in using that in your scenario lies in generating column headings that match the report data. (Zaif's method cannot be used because it is only applicable to dynamic SQL.) You would in effect be reversing your original approach, with a static query in the report source and using a variation on the FOR loop from your function to dynamically generate the column headings.

                      1 2 Previous Next