Solved

To executing a dynamic query containing functions with Execute immediate

Posted on 2008-06-15
5
588 Views
Last Modified: 2013-12-19
Hi Experts,

I am trying to execute a query dynamically with the structure "insert into <table values select <data> from tables.
When I create the procedure given below and execute it, it compiles successfully but when I try to execute it . It gives me the following error:

SQL> exec prc_build_control_panel(7001,5,2008);

begin prc_build_control_panel(7001,5,2008); end;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.PRC_BUILD_CONTROL_PANEL", line 6
ORA-06512: at line 1

Kindly help me resolve the error.
CREATE OR REPLACE PROCEDURE PRC_BUILD_CONTROL_PANEL(USER_ROLE_ID IN TBL_RMT_USER_MST.USM_SG_ROLE_ID%TYPE,

                                                   GET_MONTH IN NUMBER,

                                                   GET_YEAR IN NUMBER) IS

EXEC_QUERY VARCHAR2(5520);

BEGIN

EXEC_QUERY := ' INSERT INTO tbl_rmt_control_panel_data '||

'   SELECT A.ROLE_ID,'||

'          A.INDICATOR_ID,'||

'          '||GET_MONTH||','||GET_YEAR||','||

'          A.INDICATOR_NAME,'||

'          A.ORG_UNIT,'||

'          FN_GET_ORDER(:USER_ROLE_ID, :GET_MONTH, A.INDICATOR_ID, A.LVL) AS TREE_ORDER,'||

'          A.DEVIATION,'||

'          A.FIRST_NAME,'||

'          A.LAST_NAME,'||

'          A.USER_ZONE,'||

'          A.PLAN_VAL,'||

'          A.ACTUAL_VAL,'||

'          A.FIRST_NAME,'||

'          SYSDATE,'||

'          NULL,'||

'          NULL'||

'     FROM (SELECT DISTINCT FINAL.INDICATOR_ID,'||

'                           FINAL.MASTER_ID,'||

'                           FINAL.LVL,'||

'                           FINAL.DEVIATION,'||

'                           FINAL.ROLE_ID,'||

'                           FINAL.ORG_UNIT,'||

'                           FN_GET_ROWNUMBER(:USER_ROLE_ID,'||

'                                            :GET_MONTH,'||

'                                            FINAL.INDICATOR_ID,'||

'                                            FINAL.LVL) AS ROW_NUM,'||

'                           FINAL.FIRST_NAME,'||

'                           FINAL.LAST_NAME,'||

'                           FINAL.USER_ZONE,'||

'                           FINAL.PLAN_VAL,'||

'                           FINAL.ACTUAL_VAL,'||

'                           FINAL.INDICATOR_NAME'||

'             FROM (SELECT DISTINCT LINK_INDICATOR.INDICATOR_NAME,'||

'                                   LINK_INDICATOR.INDICATOR_ID,'||

'                                   LINK_INDICATOR.MASTER_ID,'||

'                                   LINK_INDICATOR.ROLE_ID,'||

'                                   LINK_INDICATOR.LVL,'||

'                                   FN_GET_DEVIATION(FN_GET_MASTER_INDICATOR(LINK_INDICATOR.INDICATOR_ID),'||

'                                                    LINK_INDICATOR.INDICATOR_ID,'||

'                                                    :GET_MONTH) AS DEVIATION,'||

'                                   LINK_INDICATOR.ORG_UNIT,'||

'                                   LINK_INDICATOR.FIRST_NAME,'||

'                                   LINK_INDICATOR.LAST_NAME,'||

'                                   LINK_INDICATOR.USER_ZONE,'||

'                                   LINK_INDICATOR.PLAN_VAL,'||

'                                   LINK_INDICATOR.ACTUAL_VAL'||

'                     FROM (SELECT DISTINCT I.INM_INDICATOR_NAME                   AS INDICATOR_NAME,'||

'                                           I.INM_INDICATOR_ID                     AS INDICATOR_ID,'||

'                                           I.INM_MASTER_INDICATOR_ID              AS MASTER_ID,'||

'                                           LEVEL                                  AS LVL,'||

'                                           CONTROL_PANEL.USM_SG_ROLE_ID           AS ROLE_ID,'||

'                                           CONTROL_PANEL.USM_SG_ORGANIZATION_UNIT AS ORG_UNIT,'||

'                                           CONTROL_PANEL.USM_SG_USER_FIRST_NAME   AS FIRST_NAME,'||

'                                           CONTROL_PANEL.USM_SG_USER_LAST_NAME    AS LAST_NAME,'||

'                                           CONTROL_PANEL.USM_SG_HIERARCHY_LEVEL   AS USER_ZONE,'||

'                                           DSB.IDD_DATA_POINT_'||GET_MONTH||'_PLAN1_VALUE       AS PLAN_VAL,'||

'                                           DSB.IDD_DATA_POINT_'||GET_MONTH||'_ACTUAL_VALUE      AS ACTUAL_VAL'||

'                             FROM TBL_RMT_USER_MST U,'||

'                                  TBL_RMT_CONTROL_PANEL CP,'||

'                                  TBL_RMT_INDICATOR_MST I,'||

'                                  TBL_RMT_DASHBOARD DSB,'||

'                                  (SELECT DISTINCT CP.CTP_INDICATOR_ID AS MASTER_INDICATOR_ID,'||

'                                                   U.USM_SG_ROLE_ID,'||

'                                                   U.USM_SG_ORGANIZATION_UNIT,'||

'                                                   U.USM_SG_USER_FIRST_NAME,'||

'                                                   U.USM_SG_USER_LAST_NAME,'||

'                                                   U.USM_SG_HIERARCHY_LEVEL'||

'                                     FROM TBL_RMT_USER_MST      U,'||

'                                          TBL_RMT_CONTROL_PANEL CP,'||

'                                          TBL_RMT_INDICATOR_MST I'||

'                                    WHERE U.USM_SG_HIERARCHY_LEVEL IN'||

'                                          FN_GET_TERRITORY(:GET_USER_ID,'||

'                                                           CP.CTP_INDICATOR_ID) AND'||

'                                          CP.CTP_SELECTED = ''||Y||'' AND'||

'                                          LEVEL < = CP.CTP_HIERARCHY_LEVEL'||

'                                    START WITH USM_SG_ROLE_ID = :GET_USER_ID'||

'                                   CONNECT BY PRIOR USM_SG_ROLE_ID ='||

'                                              USM_SG_MGR_ROLE_ID) CONTROL_PANEL'||

'                           WHERE DSB.IDD_INDICATOR_ID = I.INM_INDICATOR_ID AND'||

'                                 FN_GET_COLOR(I.INM_INDICATOR_ID, :GET_MONTH) = ''||RED||'' AND'||

'                                 U.USM_SG_HIERARCHY_LEVEL IN'||

'                                 FN_GET_TERRITORY(:GET_USER_ID, I.INM_INDICATOR_ID) AND'||

'                                 LEVEL < = CP.CTP_HIERARCHY_LEVEL'||

'                           START WITH I.INM_INDICATOR_ID ='||

'                                      CONTROL_PANEL.MASTER_INDICATOR_ID'||

'                          CONNECT BY PRIOR I.INM_INDICATOR_ID ='||

'                                      I.INM_MASTER_INDICATOR_ID) LINK_INDICATOR,'||

'                         TBL_RMT_CONTROL_PANEL CP'||

'                   WHERE FN_GET_DEVIATION(FN_GET_MASTER_INDICATOR(LINK_INDICATOR.INDICATOR_ID),'||

'                                          LINK_INDICATOR.INDICATOR_ID,'||

'                                         :GET_MONTH) > = CP.CTP_MAX_DEVIATION OR'||

'                         LINK_INDICATOR.INDICATOR_ID = CP.CTP_INDICATOR_ID'||

'                   ORDER BY DEVIATION DESC) FINAL,'||

'                 TBL_RMT_CONTROL_PANEL CP'||

'           WHERE ROWNUM <= CP.CTP_MAX_LIST_INDICATORS OR'||

'                 FINAL.INDICATOR_ID = CP.CTP_INDICATOR_ID'||

'           ORDER BY ROW_NUM) A';
 
 

EXECUTE IMMEDIATE EXEC_QUERY USING USER_ROLE_ID,GET_MONTH;

COMMIT;
 

END PRC_BUILD_CONTROL_PANEL;

Open in new window

0
Comment
Question by:110210
  • 3
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
ishando earned 500 total points
Comment Utility
The numeric or value error is because your varchar is too small. for the query as you have written it here, you will need it to be about varchar2(6000)
0
 

Author Comment

by:110210
Comment Utility
Thanks Ishando,
The procedure now compiles and runs fine...but strangely it is not Inserting any records into the destination table.
can you please tell me why?
0
 
LVL 15

Expert Comment

by:ishando
Comment Utility
Thats a bit difficult without more information.
I presume the insert statement is working correctly outside the procedure....
It is always worthwhile when this happens to printout the actual statement being executed to ensure that it is executing what you expect it to be.
 
0
 

Author Comment

by:110210
Comment Utility
yes the same insert statement works fine outside the procedure and without bind variables.
the snippet for the same is below.

But when I substitute the bind variables in the procedure, it doesn't insert the records which is why I find it strange.




INSERT INTO TBL_RMT_CONTROL_PANEL_DATA 

SELECT   A.ROLE_ID,

         A.INDICATOR_ID,

         5,

         2008,

         A.INDICATOR_NAME,

          A.ORG_UNIT,

          FN_GET_ORDER(7001, 5, A.INDICATOR_ID, A.LVL) AS TREE_ORDER,

          A.DEVIATION,

          A.FIRST_NAME,

          A.LAST_NAME,

          A.USER_ZONE,

          A.PLAN_VAL,

          A.ACTUAL_VAL,

          A.FIRST_NAME,

          SYSDATE,

          NULL,

          NULL

     FROM (SELECT DISTINCT FINAL.INDICATOR_ID,

                           FINAL.MASTER_ID,

                           FINAL.LVL,

                           FINAL.DEVIATION,

                           FINAL.ROLE_ID,

                           FINAL.ORG_UNIT,

                           FN_GET_ROWNUMBER(7001,5,FINAL.INDICATOR_ID,FINAL.LVL) AS ROW_NUM,

                           FINAL.FIRST_NAME,

                           FINAL.LAST_NAME,

                           FINAL.USER_ZONE,

                           FINAL.PLAN_VAL,

                           FINAL.ACTUAL_VAL,

                           FINAL.INDICATOR_NAME

             FROM (SELECT DISTINCT LINK_INDICATOR.INDICATOR_NAME,

                                   LINK_INDICATOR.INDICATOR_ID,

                                   LINK_INDICATOR.MASTER_ID,

                                   LINK_INDICATOR.ROLE_ID,

                                   LINK_INDICATOR.LVL,

                                   FN_GET_DEVIATION(FN_GET_MASTER_INDICATOR(LINK_INDICATOR.INDICATOR_ID),

                                                    LINK_INDICATOR.INDICATOR_ID,5) AS DEVIATION,

                                   LINK_INDICATOR.ORG_UNIT,

                                   LINK_INDICATOR.FIRST_NAME,

                                   LINK_INDICATOR.LAST_NAME,

                                   LINK_INDICATOR.USER_ZONE,

                                   LINK_INDICATOR.PLAN_VAL,

                                   LINK_INDICATOR.ACTUAL_VAL

                     FROM (SELECT DISTINCT I.INM_INDICATOR_NAME                   AS INDICATOR_NAME,

                                           I.INM_INDICATOR_ID                     AS INDICATOR_ID,

                                           I.INM_MASTER_INDICATOR_ID              AS MASTER_ID,

                                           LEVEL                                  AS LVL,

                                           CONTROL_PANEL.USM_SG_ROLE_ID           AS ROLE_ID,

                                           CONTROL_PANEL.USM_SG_ORGANIZATION_UNIT AS ORG_UNIT,

                                           CONTROL_PANEL.USM_SG_USER_FIRST_NAME   AS FIRST_NAME,

                                           CONTROL_PANEL.USM_SG_USER_LAST_NAME    AS LAST_NAME,

                                           CONTROL_PANEL.USM_SG_HIERARCHY_LEVEL   AS USER_ZONE,

                                           DSB.IDD_DATA_POINT_5_PLAN1_VALUE       AS PLAN_VAL,

                                           DSB.IDD_DATA_POINT_5_ACTUAL_VALUE      AS ACTUAL_VAL

                             FROM TBL_RMT_USER_MST U,

                                  TBL_RMT_CONTROL_PANEL CP,

                                  TBL_RMT_INDICATOR_MST I,

                                  TBL_RMT_DASHBOARD DSB,

                                  (SELECT DISTINCT CP.CTP_INDICATOR_ID AS MASTER_INDICATOR_ID,

                                                   U.USM_SG_ROLE_ID,

                                                   U.USM_SG_ORGANIZATION_UNIT,

                                                   U.USM_SG_USER_FIRST_NAME,

                                                   U.USM_SG_USER_LAST_NAME,

                                                   U.USM_SG_HIERARCHY_LEVEL

                                     FROM TBL_RMT_USER_MST      U,

                                          TBL_RMT_CONTROL_PANEL CP,

                                          TBL_RMT_INDICATOR_MST I

                                    WHERE U.USM_SG_HIERARCHY_LEVEL IN FN_GET_TERRITORY(7001,CP.CTP_INDICATOR_ID) AND

                                          CP.CTP_SELECTED = 'Y' AND

                                          LEVEL < = CP.CTP_HIERARCHY_LEVEL

                                    START WITH USM_SG_ROLE_ID = 7001

                                   CONNECT BY PRIOR USM_SG_ROLE_ID = USM_SG_MGR_ROLE_ID) CONTROL_PANEL

                           WHERE DSB.IDD_INDICATOR_ID = I.INM_INDICATOR_ID AND

                                FN_GET_COLOR(I.INM_INDICATOR_ID, 5) = 'RED' AND

                                 U.USM_SG_HIERARCHY_LEVEL IN FN_GET_TERRITORY(7001, I.INM_INDICATOR_ID) AND

                                 LEVEL < = CP.CTP_HIERARCHY_LEVEL

                           START WITH I.INM_INDICATOR_ID = CONTROL_PANEL.MASTER_INDICATOR_ID

                          CONNECT BY PRIOR I.INM_INDICATOR_ID = I.INM_MASTER_INDICATOR_ID) LINK_INDICATOR,

                         TBL_RMT_CONTROL_PANEL CP

                   WHERE FN_GET_DEVIATION(FN_GET_MASTER_INDICATOR(LINK_INDICATOR.INDICATOR_ID),

                                          LINK_INDICATOR.INDICATOR_ID,5) > = CP.CTP_MAX_DEVIATION OR

                         LINK_INDICATOR.INDICATOR_ID = CP.CTP_INDICATOR_ID

                   ORDER BY DEVIATION) FINAL,TBL_RMT_CONTROL_PANEL CP

           WHERE ROWNUM <= CP.CTP_MAX_LIST_INDICATORS OR

                 FINAL.INDICATOR_ID = CP.CTP_INDICATOR_ID

           ORDER BY ROW_NUM) A

Open in new window

0
 
LVL 15

Expert Comment

by:ishando
Comment Utility
Not sure. Though I have noticed in the past when using bind variables that I have at times had to pass in one for each bind variable, even though they had the same name, ie something like

EXECUTE IMMEDIATE EXEC_QUERY USING USER_ROLE_ID, GET_MONTH, USER_ROLE_ID, GET_MONTH;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now