1 2 Previous Next 16 Replies Latest reply: Feb 17, 2014 5:00 AM by hemu RSS

    PL/SQL RETURNING SQL QUERY

    hemu

      Hi

      i am trying below in apex 4.2

      DECLARE

      frdate             date:=:P22_FROMDATE;

      todate             date:=:P22_TODATE;

      l_date1   varchar2(11):=to_char(frdate,'dd-mon-yyyy');

      l_date2   varchar2(11):=to_char(todate,'dd-mon-yyyy');

      v_sql         varchar2(32000);

      v_name       varchar2(4000):=months_name(frdate,todate);

      v1   char(1):=q'[']';

      lbr1   number(6):=:P22_FROMBRANCH;

      lbr2   number(6):=:P22_TOBRANCH;

      m1   number(6):=11;

         begin

      v_sql:='SELECT * FROM ( SELECT LBRCODE,PRDACCTID,MN,AVGX FROM ( SELECT LBRCODE,PRDACCTID,MN,SUM(BAL) BAL,COUNT(MN)DAYS,ROUND(SUM(BAL)/COUNT(MN),2) AVGX ';

      v_sql:=v_sql||' FROM (SELECT LBRCODE,PRDACCTID,TO_CHAR(BALDATE,'||CHR(39)||'MM'||CHR(39)||')'||'||'||CHR(39)||'_'||CHR(39)||'||' || 'TO_CHAR(BALDATE,'||CHR(39)||'YYYY'||CHR(39)||') MN,BAL ';

      V_SQL:=V_SQL||' FROM (SELECT Q2.LBRCODE,Q2.PRDACCTID,Q1.BALDATE,T_OST_NEW(Q2.LBRCODE,Q2.PRDACCTID,Q1.BALDATE) BAL ';

      V_SQL:=V_SQL||' FROM ( select TO_DATE('||chr(39)||l_date1||chr(39)||','||CHR(39)||'DD-MON-YYYY'||CHR(39)||') baldate  FROM DUAL  union all ';

      V_SQL:=V_SQL||' select (TO_DATE('||chr(39)||l_date1||chr(39)||','||CHR(39)||'DD-MON-YYYY'||CHR(39)||')+level) baldate from dual ';

      V_SQL:=V_SQL||' connect by level<=(TO_DATE('||chr(39)||l_date2||chr(39)||','||CHR(39)||'DD-MON-YYYY'||CHR(39)||')-TO_DATE('||chr(39)|| l_date1||chr(39)||','||CHR(39)||'dd-mon-yyyy'||CHR(39)||' )) )Q1,';

      V_SQL:=V_SQL||'(SELECT LBRCODE,'||'(rpad(prdcd,8,'||v1||''||v1||')||ltrim(rpad('||v1||'x'||v1||',25,'||v1||'0'||v1||'),'||v1||'x'||v1||'))prdacctid FROM D009021 WHERE LBRCODE between '||:P22_FROMBRANCH||' and '||:P22_TOBRANCH||' AND moduletype='||TO_NUMBER(:P22_SELECTLIST);

      V_SQL:=V_SQL||' )Q2 ) ) GROUP BY LBRCODE,PRDACCTID,MN ) )';

      V_SQL:=V_SQL||'PIVOT (MAX(AVGX) FOR (MN) IN (';

      V_SQL:=V_SQL||V_NAME;

      V_SQL:=V_SQL||' ))ORDER BY LBRCODE,PRDACCTID ';

      RETURN V_SQL;

      END;

      i have created all the required page items

      i am getting error as missing expression

      for testing i created a function to return the query it works fine i.e. returning a query which is giving me desired report

      am i missing anything? 

      please help

        • 1. Re: PL/SQL RETURNING SQL QUERY
          fac586

          hemu wrote:

           

          am i missing anything?

          Bind variables and code formatting for starters...

           

          Where does the error occur: in the App Builder when entering the data source, or in the app at runtime? You are trying to create a standard report using a PL/SQL function body returning an SQL query report source, and not an interactive report, which does not support a dynamic data source?

           

          Your approach to constructing the query is wrong. You need to use bind variables rather than concatenating in literal values. This will be more efficient, more secure, and less prone to error.

           

          At runtime, debug mode will display the SQL generated by a PL/SQL function body returning an SQL query report source so that it can be extracted for debugging and testing.

           

          See this thread for an example of a dynamic pivot performed using a static query.

          • 2. Re: PL/SQL RETURNING SQL QUERY
            hemu

            sir

            trying to create a static report

            error occurred while creating a region.

            so i selected parse query at run time option and did debug the report

            but debug report is not generating query

            below is extract

            Session State: Save form items and p_arg_values4

            0

            0.015000.00000...Session State: Save "P22_FROMBRANCH" - saving same value: "2"4

            0

            0.015000.01600...Session State: Save "P22_TOBRANCH" - saving same value: "2"4

            100

            0.031000.00000...Session State: Save "P22_FROMDATE" - saving same value: "01-DEC-2013"4

            0

            0.031000.00000...Session State: Save "P22_TODATE" - saving same value: "31-JAN-2014"4

            0

            0.031000.00000...Session State: Save "P22_SELECTLIST" - saving same value: "000000011"4

            0

            0.031000.00000Processes - point: ON_SUBMIT_BEFORE_COMPUTATION4

            0

            0.031000.00000Branch point: Before Computation4

            0

            0.031000.00000Process point: AFTER_SUBMIT4

            0

            0.031000.00000Tabs: Perform Branching for Tab Requests4

            0

            0.031000.00000Branch point: Before Validation4

            0

            0.031000.00000Validations:4

            0

            0.031000.00000Perform basic and predefined validations:4

            0

            0.031000.00000...Validate is not null for P22_FROMBRANCH4

            0

            0.031000.00000Perform custom validations:

             

            You need to use bind variables rather than concatenating in literal values.

            i am using declared variables where ever required

            is a error related to syntax ?



            • 3. Re: PL/SQL RETURNING SQL QUERY
              hemu

              hi

              below is a generated query

              SELECT * FROM (
              SELECT LBRCODE,PRDACCTID,MN,AVGX
              FROM (
              SELECT LBRCODE,PRDACCTID,MN,SUM(BAL) BAL,COUNT(MN) DAYS,ROUND(SUM(BAL)/COUNT(MN),2) AVGX
               FROM (
              SELECT LBRCODE,PRDACCTID,TO_CHAR(BALDATE,'MM')||'_'||TO_CHAR(BALDATE,'YYYY') MN,BAL  FROM (
              SELECT Q2.LBRCODE,Q2.PRDACCTID,Q1.BALDATE,T_OST_NEW(Q2.LBRCODE,Q2.PRDACCTID,Q1.BALDATE) BAL
               FROM (
              select TO_DATE('01-may-2013','DD-MON-YYYY') baldate  FROM DUAL
               union all
               select (TO_DATE('01-may-2013','DD-MON-YYYY')+level) baldate from dual
               connect by level<=(TO_DATE('01-may-2014','DD-MON-YYYY')-TO_DATE('01-may-2013','dd-mon-yyyy' )) )Q1,
              (SELECT LBRCODE,(rpad(prdcd,8,' ')||ltrim(rpad('x',25,'0'),'x'))prdacctid FROM D009021
              WHERE LBRCODE between 2 and 212
              AND moduletype=20 )Q2
              )
              ) GROUP BY LBRCODE,PRDACCTID,MN
              ) 
              )PIVOT (MAX(AVGX) FOR (MN) IN ( '05_2013' as  "may_2013",'06_2013' as  "jun_2013",'07_2013' as  "jul_2013",'08_2013' as  "aug_2013",'09_2013' as  "sep_2013",'10_2013' as
                "oct_2013",'11_2013' as  "nov_2013",'12_2013' as  "dec_2013",'01_2014' as  "jan_2014",'02_2014' as  "feb_2014",'03_2014' as  "mar_2014",'04_2014' as  "apr_2014",'05_2014' as  "may_2014" )
              )ORDER BY LBRCODE,PRDACCTID
              

              please guide me

              can i call a function here directly ...please help

              • 4. Re: PL/SQL RETURNING SQL QUERY
                fac586

                That appears to be a page accept debug trace. You need one for the page show processing of the page containing the report.

                • 5. Re: PL/SQL RETURNING SQL QUERY
                  hemu

                  sir

                  error detected

                  here is an error

                  SELECT LBRCODE,(rpad(prdcd,8,'')||ltrim(rpad('x',25,'0'),'x'))prdacctid FROM D009021

                  it should be

                  SELECT LBRCODE,(rpad(prdcd,8,' ')||ltrim(rpad('x',25,'0'),'x'))prdacctid FROM D009021--------------a space

                  after changing it  when i try to validate the same  again i am getting error as

                  • Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic columns'' checkbox below the region source to proceed without parsing. ORA-00936: missing expression

                  checking generic columns i am not able to disply the column headings ( for which i am trying for)

                  you have helped me this much ...a little more please

                  how do i do about now?


                  thanxxxxxx

                  • 6. Re: PL/SQL RETURNING SQL QUERY
                    hemu

                    sir please

                    • 7. Re: PL/SQL RETURNING SQL QUERY
                      fac586

                      Get rid of the incomprehensible/borderline insane chr(39) and v1 ideas.

                       

                      Keep the dynamic SQL code as close to being executable standalone as possible:

                       

                      • Most importantly, use bind variables where it's possible to do so.
                      • Don't use concatenation to build dynamic SQL.
                      • Create SQL templates with substitution placeholders for dynamic lexical parameters, and replace these with the generated code.

                       

                      Your report source should be something like this:

                       

                      declare
                      
                      
                        v_sql varchar2(32000);
                      
                      begin
                      
                      v_sql := q'{
                      select
                          *
                      from
                          (select
                              lbrcode
                            , prdacctid
                            , mn
                            , avgx
                          from
                              (select
                                  lbrcode
                                , prdacctid
                                , mn
                                , sum(bal) bal
                                , count(mn) days
                                , round(sum(bal) / count(mn), 2) avgx
                              from
                                  (select
                                      lbrcode
                                    , prdacctid
                                    , to_char(baldate, 'mm_yyyy') mn
                                    , bal
                                  from
                                      (select
                                          q2.lbrcode
                                        , q2.prdacctid
                                        , q1.baldate
                                        , t_ost_new(q2.lbrcode, q2.prdacctid, q1.baldate) bal
                                      from
                                          (select
                                              to_date(:p22_fromdate, 'dd-mon-yyyy') baldate
                                          from
                                              dual
                                          union all
                                          select
                                              to_date(:p22_fromdate, 'dd-mon-yyyy') + level baldate
                                          from
                                              dual
                                                connect by level <= (to_date(:p22_todate, 'dd-mon-yyyy') - to_date(:p22_fromdate, 'dd-mon-yyyy'))) q1
                                        , (select
                                              lbrcode
                                            , rpad(prdcd, 8) || ltrim(rpad('x', 25, '0'), 'x') prdacctid
                                          from
                                              d009021
                                          where
                                              lbrcode between to_number(:p22_frombranch) and to_number(:p22_tobranch)
                                          and moduletype = to_number(:p22_selectlist)) q2))
                              group by
                                  lbrcode
                                , prdacctid
                                , mn))
                      pivot (
                          max(avgx) for (mn) in (%dynamic_pivot%))
                      order by
                          lbrcode
                        , prdacctid}';
                      
                        return replace(v_sql, '%dynamic_pivot%', months_name(to_date(:p22_fromdate, 'dd-mon-yyyy'), to_date(:p22_todate, 'dd-mon-yyyy'))));
                      
                      
                      end;
                      

                      Note that this is not tested as I don't have access to your tables, data, or APEX session state. The generated code does however yield only a ORA-00942: table or view does not exist error when executed on my system, indicating that it's free of obvious syntax errors.

                       

                      Also suggest you look into using subquery factoring using the WITH clause as this is much easier to develop, test, and understand than using multiple in-line views.

                      • 8. Re: PL/SQL RETURNING SQL QUERY
                        hemu

                        million thanx sir

                        will follow as you suggested

                         

                        thanx again

                        • 9. Re: PL/SQL RETURNING SQL QUERY
                          hemu

                          hi

                          sorry to bother you again

                          i tried the query

                          error no data found

                          tried to see the  generated sql in debug mode

                          apex did not generated a whole query...

                          then i tried

                           

                          pivot ( 

                              max(avgx) for (mn) in (}||q'{v_name}||')) 

                          order by 

                              lbrcode 

                            , prdacctid'; 

                            return v_sql

                          end; 

                          and now i am getting error as

                          Non constant not allowed for pivot unpivot values

                          • 10. Re: PL/SQL RETURNING SQL QUERY
                            fac586

                            hemu wrote:

                             

                            hi

                            sorry to bother you again

                            i tried the query

                            error no data found

                            tried to see the  generated sql in debug mode

                            apex did not generated a whole query...

                            It almost certainly did, it's simply that entire query is not included in the debug output as standard debug messages are limited to 4000 characters. To see the full query, add your own debug output to the report source block using the apex_debug.log_long_message method.

                             

                            You are recommended to reproduce the problem on a workspace on apex.oracle.com using your table definitions and some sample data, and post guest developer credentials so we can see this at first hand.

                            • 11. Re: PL/SQL RETURNING SQL QUERY
                              hemu

                              sir

                              workspace name          hrk

                              user                            hemukarnik@gmail.com

                               

                              password                     Hrk!123

                               

                              application                   2201

                              pageno                        901

                              • 12. Re: PL/SQL RETURNING SQL QUERY
                                fac586

                                Working after changing the bind variable item reference prefixes from P22 to P901, and setting the region to Use Generic Column Names (parse query at runtime only).

                                • 13. Re: PL/SQL RETURNING SQL QUERY
                                  hemu

                                  sir

                                   

                                  the query is being return as below

                                   

                                  select * from (select lbrcode,prdacctid ,mn, avgx from (select lbrcode, prdacctid, mn,sum(bal) bal,count(mn) days,round(sum(bal) / count(mn), 2) avgx from (select lbrcode , prdacctid , to_char(baldate, 'mm_yyyy') mn,bal from (select q2.lbrcode , q2.prdacctid , q1.baldate , t_ost_new(q2.lbrcode, q2.prdacctid, q1.baldate) bal from (select to_date(:P510_FROMDATE, 'dd-mon-yyyy') baldate from dual union all select to_date(:P510_FROMDATE, 'dd-mon-yyyy') + level baldate from dual connect by level <= (to_date(:P510_TODATE, 'dd-mon-yyyy') - to_date(:P510_FROMDATE, 'dd-mon-yyyy'))) q1, (select lbrcode , rpad(prdcd, 8) || ltrim(rpad('x', 25, '0'), 'x') prdacctid from d009021 where lbrcode between to_number(:P510_FROMBRANCH) and to_number(:P510_TOBRANCH) and moduletype = to_number(:P510_SELECTLIST)) q2)) group by lbrcode,prdacctid, mn)) pivot ( max(avgx) for (mn) in ( '10_2013' as "oct_2013",'11_2013' as "nov_2013",'12_2013' as "dec_2013")) order by lbrcode , prdacctid


                                  it should run ..i think

                                  i mean i validated the query

                                  however data is not fetched


                                  thanxxxxx sir for your valuable time and efforts

                                  thankxx again



                                  • 14. Re: PL/SQL RETURNING SQL QUERY
                                    hemu

                                    Hello Sir

                                    sorry to bother you again

                                    i tried to use 'Function Returning colon delimited headings'

                                    and i got following error

                                     

                                    unable to determine query headings: ORA-06550: line 1, column 138: PLS-00103: Encountered the symbol ";" when expecting one of the following:  . ( ) , * % & = - + < / > at in is mod remainder not rem =>  <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol ")" was substituted for ";" to continue.
                                    failed to parse SQL query: 

                                    can you have a look at page901 again please and a funtion named months_namex

                                    is it doable what i am trying?

                                     

                                    thanx and would be obliged forever

                                    1 2 Previous Next