6 Replies Latest reply: Jan 1, 2013 11:32 PM by Purvesh K RSS

    Need help

    980493
      My below query is giving output in dbms but not in data grid...can any1 help me out to achieve the output in data grid....
      DECLARE
      V_REPORT_DATE DATE:='&V_RPRT_DATE';
      V_FILTERCOL VARCHAR2(30):=UPPER('&V_FLTRCOL');
      V_COL_NAME VARCHAR2(30):=UPPER('&V_COL_NAM');
      V_TAB_NAME VARCHAR2(30):=UPPER('&V_TBL_NAME');
      V_VALUE_TYPE VARCHAR2(8):=UPPER('&VALUE_OF');
      V_GRP_COL VARCHAR2(30):=UPPER('&V_GROUP_COL');
      V_DIFF_COL VARCHAR2(30):=UPPER('&V_DF_COL');
      V_QUARTER_NUMBER  NUMBER(1);
      V_QTY VARCHAR2(1000);
      V_CURRQTD_STRT VARCHAR2(11);
      V_CURRQTD_END  VARCHAR2(11);
      V_CURRQTD VARCHAR2(1000);
      V_PRVQTD_STRT VARCHAR2(11);
      V_PRVQTD_END VARCHAR2(11);
      V_PRVQTD VARCHAR2(1000);
      V_CURRYTD_STRT VARCHAR2(11);
      V_CURRYTD_END VARCHAR2(11);
      V_CURR_YTD VARCHAR2(1000);
      V_PRVYTD VARCHAR2(11);
      V_PRVYTD_STRT VARCHAR2(11);
      V_PRVYTD_END VARCHAR2(11);
      V_PRV_YTD VARCHAR2(1000);
      V_PRVYRMTH VARCHAR2(11);
      V_PRVMTH_STRT VARCHAR2(11);
      V_PRVMTH_END VARCHAR2(11);
      V_PRVYR_MTH VARCHAR2(1000);
      V_CURRQTD_SUM NUMBER(14,3):=0;
      V_PRVQTD_SUM NUMBER(14,3):=0;
      V_CURRYTD_SUM NUMBER(14,3):=0;
      V_PRVYTD_SUM NUMBER(14,3):=0;
      V_PRVYRMTH_SUM NUMBER(14,3):=0;
      A_RET_VALUE NUMBER (14,3):=0;
      TYPE NUMTAB IS TABLE OF DWH_PRODUCTION.ITEM_CODE%TYPE;
      TYPE NUMSTAB IS TABLE OF DWH_PRODUCTION.ACTUAL_YIELD%TYPE;
      TYPE NUMSTAB1 IS TABLE OF DWH_PRODUCTION.PRODUCTION_QTY%TYPE;
      CRQNUMS NUMTAB;
      CRQNUMS1 NUMSTAB1;
      CRQNUMS2 NUMSTAB;
      
      
      
      
      BEGIN
      
      --------------------GETTING QUARTER NO.------------------------------
      
      V_QTY:='SELECT TO_NUMBER(TO_CHAR(TO_DATE('''||V_REPORT_DATE||'''), ''Q'')) FROM DUAL';
      --DBMS_OUTPUT.PUT_LINE(V_QTY);
      EXECUTE IMMEDIATE V_QTY INTO V_QUARTER_NUMBER ; 
      --DBMS_OUTPUT.PUT_LINE('QT NUM'||V_QUARTER_NUMBER);
      
      --------------------CURRENT QUARTER VALUE------------------------------
      
      SELECT   MIN (t)"CURNT_QTR_ST_DT", MAX (LAST_DAY (t))"CURNT_QTR_END_DT", ADD_MONTHS(MIN (t),-3)"PREV_QTR_ST_DT", ADD_MONTHS(MAX (LAST_DAY (t)),-3) "PREV_QTR_END_DT" INTO V_CURRQTD_STRT ,V_CURRQTD_END,V_PRVQTD_STRT, V_PRVQTD_END
      FROM (SELECT ADD_MONTHS (TRUNC(TO_DATE(TO_DATE(V_REPORT_DATE)), 'YYYY'), LEVEL - 1)  t,
            TO_CHAR (ADD_MONTHS (TRUNC(TO_DATE(TO_DATE(V_REPORT_DATE)), 'YYYY'), LEVEL - 1), 'Q')QTD
            FROM DUAL
             CONNECT BY LEVEL <= 12) A
             WHERE A.QTD  = V_QUARTER_NUMBER;
       
      V_CURRQTD:='SELECT '||V_GRP_COL||', '||V_DIFF_COL||', SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_CURRQTD_STRT||''') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''') GROUP BY  '||V_GRP_COL||','||V_DIFF_COL||'';
      
      --V_CURRQTD:= V_CURRQTD ||' GROUP BY '||V_GRP_COL;
      --DBMS_OUTPUT.PUT_LINE(V_CURRQTD);
      --EXECUTE IMMEDIATE V_CURRQTD INTO V_SUM;
      EXECUTE IMMEDIATE V_CURRQTD BULK COLLECT INTO CRQNUMS,CRQNUMS1,CRQNUMS2;
      FOR I IN CRQNUMS.FIRST .. CRQNUMS.LAST LOOP
      --END LOOP;
      DBMS_OUTPUT.PUT_LINE (
      ' ITEM_CODE''              '       || CRQNUMS(I) || ': '                     || CRQNUMS1(I) ||         ': '                          || CRQNUMS2(I));
      --EXIT WHEN CRQNUMS%NOTFOUND;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(V_CURRQTD_SUM);
      
      --------------------END OF CURRENT QUARTER------------------------------
      
      --------------------PREVIOUS QUARTER------------------------------------
      
      V_PRVQTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_PRVQTD_STRT||''') ' || '  AND  TO_DATE('''||V_PRVQTD_END||''')';
      --DBMS_OUTPUT.PUT_LINE(V_PRVQTD);
      EXECUTE IMMEDIATE V_PRVQTD INTO V_PRVQTD_SUM;
      --DBMS_OUTPUT.PUT_LINE(V_PRVQTD_SUM);
      
      --------------------END OF PREVIOUS QUARTER------------------------------
      
      --------------------CURRENT YTD-------------------------------------------
      
      SELECT TRUNC(TO_DATE(V_REPORT_DATE),'YEAR')INTO V_CURRYTD_STRT FROM DUAL;
      
      SELECT LAST_DAY (ADD_MONTHS (TO_DATE (V_CURRYTD_STRT), 11)) INTO V_CURRYTD_END FROM DUAL;
      
      V_CURR_YTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_CURRYTD_STRT||''') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''')';
      --DBMS_OUTPUT.PUT_LINE(V_CURR_YTD);
      EXECUTE IMMEDIATE V_CURR_YTD INTO V_CURRYTD_SUM;
      --DBMS_OUTPUT.PUT_LINE(V_CURRYTD_SUM);
      
      --------------------END OF CURRENT YTD------------------------------------
      
      --------------------PREVIOUS YTD------------------------------------------
      
      SELECT ADD_MONTHS(TO_DATE(V_REPORT_DATE),-12) INTO V_PRVYTD FROM DUAL;
      
      SELECT TRUNC(TO_DATE(V_PRVYTD),'YEAR') INTO V_PRVYTD_STRT FROM DUAL;
      
      SELECT LAST_DAY (ADD_MONTHS (TO_DATE (V_PRVYTD_STRT), 11)) INTO V_PRVYTD_END FROM DUAL;
      
      V_PRV_YTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_PRVYTD_STRT||''') ' || '  AND  TO_DATE('''||V_PRVYTD_END||''')';
      --DBMS_OUTPUT.PUT_LINE(V_PRV_YTD);
      EXECUTE IMMEDIATE V_PRV_YTD INTO V_PRVYTD_SUM;
      --DBMS_OUTPUT.PUT_LINE(V_PRVYTD_SUM);
      
      --------------------END OF PREVIOUS YTD--------------------------------------
      
      --------------------PREVIOUS YR SAME MONTH-----------------------------------
      
      SELECT ADD_MONTHS(V_REPORT_DATE,-12) INTO V_PRVYRMTH FROM DUAL;-----PREVIOUS YR SAME DATE
      
      SELECT TRUNC(TO_DATE(V_PRVYRMTH),'MM')INTO V_PRVMTH_STRT FROM DUAL;------1ST DATE OF PRV YR SAME MONTH
      
      WITH INP AS
      (SELECT TO_DATE(V_PRVMTH_STRT) INP FROM DUAL)
      
      SELECT LAST_DAY(INP) INTO V_PRVMTH_END FROM INP;---------LAST DATE OF PRV YR SAME MONTH
      
      ---SELECT LAST_DAY(ADD_MONTHS(V_PRVMTH_STRT,12 - TO_NUMBER(TO_CHAR(SYSDATE,'MM')))) INTO V_PRVMTH_END FROM DUAL;
      
      V_PRVYR_MTH:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_PRVMTH_STRT||''') ' || '  AND  TO_DATE('''||V_PRVMTH_END||''')';
      --DBMS_OUTPUT.PUT_LINE(V_PRVYR_MTH);
      EXECUTE IMMEDIATE V_PRVYR_MTH INTO V_PRVYRMTH_SUM;
      --DBMS_OUTPUT.PUT_LINE(V_PRVYRMTH_SUM);
      
      --------------------END OF PREVIOUS YR SAME MONTH-----------------------------------
      
      
      --------------------IF CODN. TO GET THE VALUES AS GIVEN DURATION-------------
      
      IF(V_VALUE_TYPE = 'CURRQTD') THEN
         A_RET_VALUE := V_CURRQTD_SUM;
      ELSIF(V_VALUE_TYPE = 'PRVQTD') THEN
         A_RET_VALUE := V_PRVQTD_SUM;
      ELSIF (V_VALUE_TYPE = 'CURRYTD') THEN
         A_RET_VALUE := V_CURRYTD_SUM;
      ELSIF (V_VALUE_TYPE = 'PRVYTD') THEN
         A_RET_VALUE := V_PRVYTD_SUM;
      ELSIF (V_VALUE_TYPE = 'PRVYRMTH') THEN
         A_RET_VALUE := V_PRVYRMTH_SUM;
      END IF;
      
      --DBMS_OUTPUT.PUT_LINE('CURR_QTD'||'          '||'PRVQTD'||'         '||'CURR_YTD'||'           '||'PRV_YTD'||'           '||'PRVYRMTH');
      --DBMS_OUTPUT.PUT_LINE(V_CURRQTD_SUM||'    '||V_PRVQTD_SUM||'      '||V_CURRYTD_SUM||'    '||V_PRVYTD_SUM||'        '||V_PRVYRMTH_SUM);
      
      --------------------END OF IF CONDITION--------------------------------------
      
      END;
      Thnx in advance.....

      Edited by: 977490 on Dec 31, 2012 1:39 AM
        • 1. Re: Need help
          Purvesh K
          977490 wrote:
          My below query is giving output in dbms but not in data grid...can any1 help me out to achieve the output in data grid....
          What do you mean by a "Data Grid"?
          Is it sort of a Tab/Window in your IDE? If yes, then PL/SQL program shall never show an output there.

          Moreover, in the anonymous block, you provided, all i can see is Select Queries. When you execute the Block, where do you want to show the processed data? It will have to be displayed using Buffer, like DBMS that you tried, or will have to be stored into some table(s) that can be queried after this block is completed and you can view the processed data.

          Until then, I do not see of any way, how you will be able to see the data.

          And do remember to commit, if you are storing the data into a Temporary Table.
          • 2. Re: Need help
            980493
            Actually, this query is the part of a function and i need to show the output as tabluar view like the output comes after the firing the select statement on any table...I've tried table function and all the permutation and combination but unable to achieve, as the output coming in the dbms output....can u give some advice on below function to how to achieve as its very critical....

            Thnx in advance....
            CREATE OR REPLACE FUNCTION DWH_PRODUCTION_SUM_QTDYTD(V_TBL_NAME IN VARCHAR2,V_COL_NAM IN VARCHAR2,V_RPRT_DATE VARCHAR2, V_FLTRCOL IN VARCHAR2,VALUE_OF IN VARCHAR2, V_GROUP_COL VARCHAR2,V_DF_COL VARCHAR2)
            RETURN NUMBER  IS 
            
                    A_RET_VALUE NUMBER;
            
            
            V_REPORT_DATE DATE:=V_RPRT_DATE;
            V_FILTERCOL VARCHAR2(30):=UPPER(V_FLTRCOL);
            V_COL_NAME VARCHAR2(30):=UPPER(V_COL_NAM);
            V_TAB_NAME VARCHAR2(30):=UPPER(V_TBL_NAME);
            V_VALUE_TYPE VARCHAR2(8):=UPPER(VALUE_OF);
            V_GRP_COL VARCHAR2(30):=UPPER(V_GROUP_COL);
            V_DIFF_COL VARCHAR2(30):=UPPER(V_DF_COL);
            V_QUARTER_NUMBER  NUMBER(1);
            V_QTY VARCHAR2(1000);
            V_CURRQTD_STRT VARCHAR2(11);
            V_CURRQTD_END  VARCHAR2(11);
            V_CURRQTD VARCHAR2(1000);
            V_PRVQTD_STRT VARCHAR2(11);
            V_PRVQTD_END VARCHAR2(11);
            V_PRVQTD VARCHAR2(1000);
            V_CURRYTD_STRT VARCHAR2(11);
            V_CURRYTD_END VARCHAR2(11);
            V_CURR_YTD VARCHAR2(1000);
            V_PRVYTD VARCHAR2(11);
            V_PRVYTD_STRT VARCHAR2(11);
            V_PRVYTD_END VARCHAR2(11);
            V_PRV_YTD VARCHAR2(1000);
            V_PRVYRMTH VARCHAR2(11);
            V_PRVMTH_STRT VARCHAR2(11);
            V_PRVMTH_END VARCHAR2(11);
            V_PRVYR_MTH VARCHAR2(1000);
            V_CURRQTD_SUM NUMBER(14,3):=0;
            V_PRVQTD_SUM NUMBER(14,3):=0;
            V_CURRYTD_SUM NUMBER(14,3):=0;
            V_PRVYTD_SUM NUMBER(14,3):=0;
            V_PRVYRMTH_SUM NUMBER(14,3):=0;
            --A_RET_VALUE NUMBER (14,3):=0;
            TYPE NUMTAB IS TABLE OF DWH_PRODUCTION.ITEM_CODE%TYPE;
            TYPE NUMSTAB IS TABLE OF DWH_PRODUCTION.ACTUAL_YIELD%TYPE;
            TYPE NUMSTAB1 IS TABLE OF DWH_PRODUCTION.PRODUCTION_QTY%TYPE;
            CRQNUMS NUMTAB;
            CRQNUMS1 NUMSTAB1;
            CRQNUMS2 NUMSTAB;
            
            
            
            
            BEGIN
            
            --------------------GETTING QUARTER NO.------------------------------
            
            V_QTY:='SELECT TO_NUMBER(TO_CHAR(TO_DATE('''||V_REPORT_DATE||'''), ''Q'')) FROM DUAL';
            --DBMS_OUTPUT.PUT_LINE(V_QTY);
            EXECUTE IMMEDIATE V_QTY INTO V_QUARTER_NUMBER ; 
            --DBMS_OUTPUT.PUT_LINE('QT NUM'||V_QUARTER_NUMBER);
            
            --------------------CURRENT QUARTER VALUE------------------------------
            
            SELECT   MIN (t)"CURNT_QTR_ST_DT", MAX (LAST_DAY (t))"CURNT_QTR_END_DT", ADD_MONTHS(MIN (t),-3)"PREV_QTR_ST_DT", ADD_MONTHS(MAX (LAST_DAY (t)),-3) "PREV_QTR_END_DT" INTO V_CURRQTD_STRT ,V_CURRQTD_END,V_PRVQTD_STRT, V_PRVQTD_END
            FROM (SELECT ADD_MONTHS (TRUNC(TO_DATE(TO_DATE(V_REPORT_DATE)), 'YYYY'), LEVEL - 1)  t,
                  TO_CHAR (ADD_MONTHS (TRUNC(TO_DATE(TO_DATE(V_REPORT_DATE)), 'YYYY'), LEVEL - 1), 'Q')QTD
                  FROM DUAL
                   CONNECT BY LEVEL <= 12) A
                   WHERE A.QTD  = V_QUARTER_NUMBER;
             
            V_CURRQTD:='SELECT '||V_GRP_COL||', '||V_DIFF_COL||', SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_CURRQTD_STRT||''') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''') GROUP BY  '||V_GRP_COL||','||V_DIFF_COL||'';
            
            --V_CURRQTD:= V_CURRQTD ||' GROUP BY '||V_GRP_COL;
            --DBMS_OUTPUT.PUT_LINE(V_CURRQTD);
            --EXECUTE IMMEDIATE V_CURRQTD INTO V_SUM;
            EXECUTE IMMEDIATE V_CURRQTD BULK COLLECT INTO CRQNUMS,CRQNUMS1,CRQNUMS2;
            FOR I IN CRQNUMS.FIRST .. CRQNUMS.LAST LOOP
            DBMS_OUTPUT.PUT_LINE (
            ' ITEM_CODE''              '       || CRQNUMS(I) || ': '                     || CRQNUMS1(I) ||         ': '                          || CRQNUMS2(I));
            END LOOP;
            --DBMS_OUTPUT.PUT_LINE(V_CURRQTD_SUM);
            
            --------------------END OF CURRENT QUARTER------------------------------
            
            --------------------PREVIOUS QUARTER------------------------------------
            
            V_PRVQTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_PRVQTD_STRT||''') ' || '  AND  TO_DATE('''||V_PRVQTD_END||''')';
            --DBMS_OUTPUT.PUT_LINE(V_PRVQTD);
            EXECUTE IMMEDIATE V_PRVQTD INTO V_PRVQTD_SUM;
            --DBMS_OUTPUT.PUT_LINE(V_PRVQTD_SUM);
            
            --------------------END OF PREVIOUS QUARTER------------------------------
            
            --------------------CURRENT YTD-------------------------------------------
            
            SELECT TRUNC(TO_DATE(V_REPORT_DATE),'YEAR')INTO V_CURRYTD_STRT FROM DUAL;
            
            SELECT LAST_DAY (ADD_MONTHS (TO_DATE (V_CURRYTD_STRT), 11)) INTO V_CURRYTD_END FROM DUAL;
            
            V_CURR_YTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_CURRYTD_STRT||''') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''')';
            --DBMS_OUTPUT.PUT_LINE(V_CURR_YTD);
            EXECUTE IMMEDIATE V_CURR_YTD INTO V_CURRYTD_SUM;
            --DBMS_OUTPUT.PUT_LINE(V_CURRYTD_SUM);
            
            --------------------END OF CURRENT YTD------------------------------------
            
            --------------------PREVIOUS YTD------------------------------------------
            
            SELECT ADD_MONTHS(TO_DATE(V_REPORT_DATE),-12) INTO V_PRVYTD FROM DUAL;
            
            SELECT TRUNC(TO_DATE(V_PRVYTD),'YEAR') INTO V_PRVYTD_STRT FROM DUAL;
            
            SELECT LAST_DAY (ADD_MONTHS (TO_DATE (V_PRVYTD_STRT), 11)) INTO V_PRVYTD_END FROM DUAL;
            
            V_PRV_YTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_PRVYTD_STRT||''') ' || '  AND  TO_DATE('''||V_PRVYTD_END||''')';
            --DBMS_OUTPUT.PUT_LINE(V_PRV_YTD);
            EXECUTE IMMEDIATE V_PRV_YTD INTO V_PRVYTD_SUM;
            --DBMS_OUTPUT.PUT_LINE(V_PRVYTD_SUM);
            
            --------------------END OF PREVIOUS YTD--------------------------------------
            
            --------------------PREVIOUS YR SAME MONTH-----------------------------------
            
            SELECT ADD_MONTHS(V_REPORT_DATE,-12) INTO V_PRVYRMTH FROM DUAL;-----PREVIOUS YR SAME DATE
            
            SELECT TRUNC(TO_DATE(V_PRVYRMTH),'MM')INTO V_PRVMTH_STRT FROM DUAL;------1ST DATE OF PRV YR SAME MONTH
            
            WITH INP AS
            (SELECT TO_DATE(V_PRVMTH_STRT) INP FROM DUAL)
            
            SELECT LAST_DAY(INP) INTO V_PRVMTH_END FROM INP;---------LAST DATE OF PRV YR SAME MONTH
            
            ---SELECT LAST_DAY(ADD_MONTHS(V_PRVMTH_STRT,12 - TO_NUMBER(TO_CHAR(SYSDATE,'MM')))) INTO V_PRVMTH_END FROM DUAL;
            
            V_PRVYR_MTH:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_PRVMTH_STRT||''') ' || '  AND  TO_DATE('''||V_PRVMTH_END||''')';
            --DBMS_OUTPUT.PUT_LINE(V_PRVYR_MTH);
            EXECUTE IMMEDIATE V_PRVYR_MTH INTO V_PRVYRMTH_SUM;
            --DBMS_OUTPUT.PUT_LINE(V_PRVYRMTH_SUM);
            
            --------------------END OF PREVIOUS YR SAME MONTH-----------------------------------
            
            
            --------------------IF CODN. TO GET THE VALUES AS GIVEN DURATION-------------
            
            IF(V_VALUE_TYPE = 'CURRQTD') THEN
               A_RET_VALUE := V_CURRQTD_SUM;
            ELSIF(V_VALUE_TYPE = 'PRVQTD') THEN
               A_RET_VALUE := V_PRVQTD_SUM;
            ELSIF (V_VALUE_TYPE = 'CURRYTD') THEN
               A_RET_VALUE := V_CURRYTD_SUM;
            ELSIF (V_VALUE_TYPE = 'PRVYTD') THEN
               A_RET_VALUE := V_PRVYTD_SUM;
            ELSIF (V_VALUE_TYPE = 'PRVYRMTH') THEN
               A_RET_VALUE := V_PRVYRMTH_SUM;
            END IF;
            
            --DBMS_OUTPUT.PUT_LINE('CURR_QTD'||'          '||'PRVQTD'||'         '||'CURR_YTD'||'           '||'PRV_YTD'||'           '||'PRVYRMTH');
            --DBMS_OUTPUT.PUT_LINE(V_CURRQTD_SUM||'    '||V_PRVQTD_SUM||'      '||V_CURRYTD_SUM||'    '||V_PRVYTD_SUM||'        '||V_PRVYRMTH_SUM);
            
            --------------------END OF IF CONDITION--------------------------------------
            
            RETURN A_RET_VALUE;
            
            END DWH_PRODUCTION_SUM_QTDYTD;
            /
            • 3. Re: Need help
              VenkatB
              What tool are you using? TOAD/SQL Developer, etc?

              In TOAD, you may have to switch on dbms output explicitly in the Result tab to display results from PL/SQL, I think

              Regards
              Venkat
              • 4. Re: Need help
                980493
                SQL*PLUS
                • 5. Re: Need help
                  EdStevens
                  977490 wrote:
                  SQL*PLUS
                  set serverout on
                  • 6. Re: Need help
                    Purvesh K
                    977490 wrote:
                    Actually, this query is the part of a function and i need to show the output as tabluar view like the output comes after the firing the select statement on any table...I've tried table function and all the permutation and combination but unable to achieve, as the output coming in the dbms output....can u give some advice on below function to how to achieve as its very critical....
                    Agreed that you need to display the data in Tabular Format. But how can you display the data, when all the DBMS_OUTPUT statements are commented.

                    And since, you do mention that data is being displayed with DBMS_OUTPUT, your function works correctly. But, since you have a function, it can return at the most One value. And looking at the code below, you are calculating at least 4.
                    How do you return them?
                    How do you expect the Caller code to read the calculated values?

                    Below are the ways you can adopt to see the output generated by the function:
                    1. Add OUT Variables to the Function parameter list
                    CREATE OR REPLACE FUNCTION DWH_PRODUCTION_SUM_QTDYTD(V_TBL_NAME IN VARCHAR2,V_COL_NAM IN VARCHAR2,
                    V_RPRT_DATE VARCHAR2, V_FLTRCOL IN VARCHAR2,VALUE_OF IN VARCHAR2, V_GROUP_COL VARCHAR2,V_DF_COL VARCHAR2,
                     p_qtd OUT NUMBER, p_prev_qtd OUT NUMBER, ...)  --> Notice Added last two parameters
                    Assign the Calculated value of QTD, for eg., variable to p_qtd before your RETURN statement.

                    2. Update the values into a Global Temporary Table and then access the GTT by the Caller program.