12 Replies Latest reply: Jan 21, 2013 6:00 AM by 980493 RSS

    Need help in stored procedure

    980493
      Hi Experts,

      I've wrote a below procedure, there is no any error in this, but while i m executing the procedure its now showing any output...Am I missing something please help me out
      CREATE OR REPLACE PROCEDURE Dwh_Production_Sum_Qtdytd_1901(V_TBL_NAME IN VARCHAR2,V_COL_NAM IN VARCHAR2,V_RPRT_DATE VARCHAR2, V_FLTRCOL IN VARCHAR2,VALUE_OF IN VARCHAR2, GRoup_COL in varchar2)
      
      is
      
      V_REPORT_DATE DATE:='&V_REPORT_DATE';
      V_FILTERCOL VARCHAR2(30):=UPPER('&V_FILTERCOL');
      V_COL_NAME VARCHAR2(30):=UPPER('&V_COL_NAME');
      V_TAB_NAME VARCHAR2(30):=UPPER('&V_TAB_NAME');
      V_VALUE_TYPE VARCHAR2(8):=UPPER('&V_VALUE_TYPE');
      V_GRP_COL VARCHAR2(100):=UPPER('&V_GRP_COL');
      V_START_DATE VARCHAR2(20);
      V_CURRMNTH_STRT VARCHAR2(11);
      V_CURRMNTH_END VARCHAR2(11);
      V_CURRMNTH VARCHAR2(1000);
      V_CURRMNTH_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;
      ENUMS NUMTAB;
      ENUMS1 NUMSTAB;
      BEGIN
      -------------------GETTING START_END DATE OF THE MONTH--------------------------------------------
      SELECT TRUNC(TO_DATE(V_REPORT_DATE), 'MM') INTO V_CURRMNTH_STRT FROM DUAL;
      SELECT LAST_DAY (TO_DATE (V_CURRMNTH_STRT)) INTO V_CURRMNTH_END FROM DUAL;
      DBMS_OUTPUT.PUT_LINE (V_CURRMNTH_STRT||'          '||V_CURRMNTH_END);
      -------------------CURRENT MONTH VALUE--------------------------------------------------------------
      V_CURRMNTH:='SELECT '||V_GRP_COL||', SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_START_DATE||''') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''')  GROUP BY  '||V_GRP_COL||'';
      DBMS_OUTPUT.PUT_LINE(V_CURRMNTH);
      --EXECUTE IMMEDIATE V_CURRMNTH INTO V_CURRMNTH_SUM;
      --DBMS_OUTPUT.PUT_LINE(V_CURRMNTH_SUM);
      EXECUTE IMMEDIATE V_CURRMNTH BULK COLLECT INTO ENUMS,ENUMS1;
      FOR I IN nvl(ENUMS.FIRST,1) .. nvl(ENUMS.LAST,0) LOOP
      DBMS_OUTPUT.PUT_LINE (
      ' ITEM_CODE''              '       || ENUMS(I) || ': '    || ENUMS1(I));
      END LOOP;
      --------------------IF CODN. TO GET THE VALUES AS GIVEN DURATION--------------------------------------
      IF(V_VALUE_TYPE = 'CURRMNTH') THEN
         A_RET_VALUE := V_CURRMNTH_SUM;
      --ELSIF(VALUE_OF = 'CURRQTD') THEN
      --   A_RET_VALUE := V_CURRQTD_SUM;
      --ELSIF(VALUE_OF = 'PRVQTD') THEN
      --   A_RET_VALUE := V_PRVQTD_SUM;
      --ELSIF (VALUE_OF = 'CURRYTD') THEN
      --   A_RET_VALUE := V_CURRYTD_SUM;
      --ELSIF (VALUE_OF = 'PRVYTD') THEN
      --   A_RET_VALUE := V_PRVYTD_SUM;
      --ELSIF (VALUE_OF = 'PRVYRMTH') THEN
      --   A_RET_VALUE := V_PRVYRMTH_SUM;
      END IF;
      -------------------END OF CURRENT MONTH VALUE-------------------------------------------------------
      end;
      Executing like below:
      EXECUTE Dwh_Production_Sum_Qtdytd_1901 ('20-DEC-2012','WO_RELEASE_DATE', 'ACTUAL_YIELD', 'DWH_PRODUCTION', 'CURRMNTH', 'ITEM_CODE');
      As I am new to oracle technology, plz guide....
        • 1. Re: Need help in stored procedure
          user639304
          Hi,

          What tool do you use to execute the procedure? SQLPlus or what?
          • 2. Re: Need help in stored procedure
            Niket Kumar
            set serveroutput on
            • 3. Re: Need help in stored procedure
              980493
              SQL*Plus and I've already fired SET SERVEROUTPUT on...
              Furthermore there is more than 800 rows in the table.
              • 4. Re: Need help in stored procedure
                Niket Kumar
                are you getting any error?
                • 6. Re: Need help in stored procedure
                  980493
                  Now I've changed only the decalration part in procedure it's throwing error--

                  Like below:
                  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(100):=UPPER(GRoup_COL);
                  Procedure is being created successfully but while executing its throwing error:

                  ERROR
                  ERROR at line 1:
                  ORA-01858: a non-numeric character was found where a numeric was expected
                  ORA-06512: at "UTL.DWH_PRODUCTION_SUM_QTDYTD_1901", line 3
                  ORA-06512: at line 1
                  • 7. Re: Need help in stored procedure
                    SomeoneElse
                    V_RPRT_DATE VARCHAR2
                    Change this argument to be a DATE type, then call the procedure with a proper date value (using to_date if it's a literal value).
                    • 8. Re: Need help in stored procedure
                      Oracle Maniac
                      if you have access to tools like toad ,plsql developer , sql developer then you may use the debugger module to identify the bottleneck.



                      In case you use sql*plus ,I wonder y arent you getting any exception message . what I do (not a good way) , I make use of dbms_output.put_line to print any random things ,say for example

                      dbms_output.put_line('I crossed line number five'); and so on ....Just a way ,not valid for length modules .:).Experts please suggest a better way .
                      • 9. Re: Need help in stored procedure
                        rp0428
                        >
                        I've wrote a below procedure, there is no any error in this, but while i m executing the procedure its now showing any output...Am I missing something please help me out
                        >
                        You are making the classic mistake that many people make when they try to write PL/SQL code that does dynamic sql.

                        DO NOT TRY TO AUTOMATE WHAT YOU CAN'T DO MANUALLY!

                        If you can't execute a query in sql*plus and get results from it why do you think you can write a procedure that executes a query and gets results from it?

                        Your development process is bass-ackwards.

                        1. Write a query typical of what you want to execute: that means NO substitution variables, no bind variables just the query
                        2. Execute the query in sql*plus
                        3. If you dont' get the results you expect tweak the query and go back to step #2
                        4. Repeate steps 2 and 3 MANUALLY untile you get the expected results.
                        5. Produce an execution plan (the actual plan) and examine it for any issues or problems
                        6. If there are any issues or problems with the plan that might prevent the query from scaling tweak the DDL or query and go back to step #2 and begin again.

                        Only AFTER you successfully complete the above preliminaries should you even begin to think about writing a procedure.

                        Get a piece of paper or a whiteboard and write this on it: THERE ARE NO SHORTCUTS!

                        Then before you begin any PL/SQL read the whiteboard and repeat that statement 3 times.
                        you have this line of code in what you posted
                        EXECUTE IMMEDIATE V_CURRMNTH BULK COLLECT INTO ENUMS,ENUMS1;
                        Presumably this is the line you are saying gives no output.

                        But you also have this line of code in the procedure
                        DBMS_OUTPUT.PUT_LINE(V_CURRMNTH);
                        THAT OUTPUT is what you should be debugging and examining (and should have posted) since that is the underlying query that is at issue.

                        That is your step #2 starting point. Until you can execute the query that DBMS_OUTPUT produces just forget about the procedure. Nothing you do in the procedure can ever make the procedure work properly if that query doesn't produce the correct output.

                        It's time you start over and do things right.

                        You should also consider yourself fortunate that no one (yet) has jumped all over you for even using dynamic sql in this fashion. You are creating a gigantic security and sql injection risk and are likely using dynamic sql when standard sql would get the job done.

                        I'm talking about the sort of response Billy Verreynne gave in this thread from today from someone doing something very similar to what you are asking about:

                        https://forums.oracle.com/forums/thread.jspa?threadID=2488054&tstart=0
                        >
                        Also keep in mind that dynamic SQL is almost always the wrong solution. Around here, I'll say that easily 99% of people using dynamic SQL and having problems with it, is using it not only incorrectly, but for invalid reasons.

                        I suggest you reconsider hacking Oracle and your data model using dynamic SQL. Unless you have very sound justification.
                        • 10. Re: Need help in stored procedure
                          980493
                          Thnx for your reply...

                          But before posting the procedure on the forum I've checked all the things by firing the simple query.....that's why I mentioned there are more than 800 rows in the table...
                          DBMS_OUTPUT.PUT_LINE(V_CURRMNTH);
                          What is the effect of this line, it's just the dbms output.....if other than this can u plz elaborate how it's being a hurdle for
                          EXECUTE IMMEDIATE V_CURRMNTH BULK COLLECT INTO ENUMS,ENUMS1;
                          Ok I've commented this line....but still m not getting any output...can u plz help me out
                          Enter value for v_report_date: 20-DEC-2012
                          old   5: V_REPORT_DATE DATE:='&V_REPORT_DATE';
                          new   5: V_REPORT_DATE DATE:='20-DEC-2012';
                          Enter value for v_filtercol: WO_RELEASE_DATE
                          old   6: V_FILTERCOL VARCHAR2(30):=UPPER('&V_FILTERCOL');
                          new   6: V_FILTERCOL VARCHAR2(30):=UPPER('WO_RELEASE_DATE');
                          Enter value for v_col_name: ACTUAL_YIELD
                          old   7: V_COL_NAME VARCHAR2(30):=UPPER('&V_COL_NAME');
                          new   7: V_COL_NAME VARCHAR2(30):=UPPER('ACTUAL_YIELD');
                          Enter value for v_tab_name: DWH_PRODUCTION
                          old   8: V_TAB_NAME VARCHAR2(30):=UPPER('&V_TAB_NAME');
                          new   8: V_TAB_NAME VARCHAR2(30):=UPPER('DWH_PRODUCTION');
                          Enter value for v_value_type: CURRMNTH
                          old   9: V_VALUE_TYPE VARCHAR2(8):=UPPER('&V_VALUE_TYPE');
                          new   9: V_VALUE_TYPE VARCHAR2(8):=UPPER('CURRMNTH');
                          Enter value for v_grp_col: ITEM_CODE
                          old  10: V_GRP_COL VARCHAR2(100):=UPPER('&V_GRP_COL');
                          new  10: V_GRP_COL VARCHAR2(100):=UPPER('ITEM_CODE');
                          
                          Procedure created.
                          
                          SQL> EXECUTE Dwh_Production_Sum_Qtdytd_1901 ('20-DEC-2012','WO_RELEASE_DATE', 'ACTUAL_YIELD', 'DWH_P
                          RODUCTION', 'CURRMNTH', 'ITEM_CODE');
                          
                          PL/SQL procedure successfully completed.
                          • 11. Re: Need help in stored procedure
                            980493
                            The below error has been resolved... now what I am doing in the same procedure is inserting the output values into a table....
                            CREATE OR REPLACE PROCEDURE Dwh_Production_Sum_Qtdytd_1901(V_TBL_NAME IN VARCHAR2,V_COL_NAM IN VARCHAR2,V_RPRT_DATE VARCHAR2, V_FLTRCOL IN VARCHAR2,VALUE_OF IN VARCHAR2, GRoup_COL in varchar2, GRoup_COL2 in varchar2 )
                            
                            is
                            
                            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('&GRoup_COL');
                            V_GRP_COL2 VARCHAR2(30):=UPPER('&GRoup_COL2');
                            --V_START_DATE VARCHAR2(20);
                            V_CURRMNTH_STRT VARCHAR2(11);
                            V_CURRMNTH_END VARCHAR2(11);
                            V_CURRMNTH VARCHAR2(1000);
                            V_CURRMNTH_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 NUMSTAB2 IS TABLE OF DWH_PRODUCTION.SITE_CODE__PARENT%TYPE;
                            ENUMS NUMTAB;
                            ENUMS1 NUMSTAB;
                            ENUMS2 NUMSTAB2;
                            
                            BEGIN
                            
                            EXECUTE IMMEDIATE('TEST_X');
                            
                            
                            -------------------GETTING START_END DATE OF THE MONTH--------------------------------------------
                            SELECT TRUNC(TO_DATE(V_REPORT_DATE), 'MM') INTO V_CURRMNTH_STRT FROM DUAL;
                            
                            SELECT LAST_DAY (TO_DATE (V_CURRMNTH_STRT)) INTO V_CURRMNTH_END FROM DUAL;
                            
                            DBMS_OUTPUT.PUT_LINE (V_CURRMNTH_STRT||'          '||V_CURRMNTH_END);
                            
                            -------------------CURRENT MONTH VALUE--------------------------------------------------------------
                            
                            V_CURRMNTH:='SELECT '||V_GRP_COL||', '||V_GRP_COL2||', SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_CURRMNTH_STRT||''') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''')  GROUP BY  '||V_GRP_COL||','||V_GRP_COL2||'';
                            --DBMS_OUTPUT.PUT_LINE(V_CURRMNTH);
                            --EXECUTE IMMEDIATE V_CURRMNTH INTO V_CURRMNTH_SUM;
                            --DBMS_OUTPUT.PUT_LINE(V_CURRMNTH_SUM);
                            EXECUTE IMMEDIATE V_CURRMNTH BULK COLLECT INTO ENUMS,ENUMS2,ENUMS1;
                            FOR I IN nvl(ENUMS.FIRST,1) .. nvl(ENUMS.LAST,0) LOOP
                            insert into test_x values (ENUMS,ENUMS2,ENUMS1);
                            END LOOP;
                            --DBMS_OUTPUT.PUT_LINE (
                            --' ITEM_CODE''              '       || ENUMS(I) || ': '    || ENUMS2(I)|| ': '    || ENUMS1(I));
                            
                            --------------------IF CODN. TO GET THE VALUES AS GIVEN DURATION--------------------------------------
                            IF(V_VALUE_TYPE = 'CURRMNTH') THEN
                               A_RET_VALUE := V_CURRMNTH_SUM;
                            --ELSIF(VALUE_OF = 'CURRQTD') THEN
                            --   A_RET_VALUE := V_CURRQTD_SUM;
                            --ELSIF(VALUE_OF = 'PRVQTD') THEN
                            --   A_RET_VALUE := V_PRVQTD_SUM;
                            --ELSIF (VALUE_OF = 'CURRYTD') THEN
                            --   A_RET_VALUE := V_CURRYTD_SUM;
                            --ELSIF (VALUE_OF = 'PRVYTD') THEN
                            --   A_RET_VALUE := V_PRVYTD_SUM;
                            --ELSIF (VALUE_OF = 'PRVYRMTH') THEN
                            --   A_RET_VALUE := V_PRVYRMTH_SUM;
                            END IF;
                            -------------------END OF CURRENT MONTH VALUE-------------------------------------------------------
                            end;
                            It's throwing below error
                            LINE/COL ERROR
                            -------- -----------------------------------------------------------------
                            45/28    PLS-00642: local collection types not allowed in SQL statements
                            45/34    PLS-00642: local collection types not allowed in SQL statements
                            45/41    PLS-00642: local collection types not allowed in SQL statements
                            As i m new to oracle can any1 plz help me out.

                            Thnx in advance...

                            Edited by: 977490 on Jan 21, 2013 3:35 AM
                            • 12. Re: Need help in stored procedure
                              980493
                              Done....
                              Thnx all of you for the support....