Solved

To executing a dynamic query containing functions with Execute immediate

Posted on 2008-06-15
5
592 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
ishando earned 500 total points
ID: 21791381
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
ID: 21791433
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
ID: 21791457
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
ID: 21791484
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
ID: 21791505
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Repeat query 13 74
oracle date format checking 7 34
Procedure syntax 5 50
Oracle 11gR2 Middleware: multiple domains with individual admin servers? 4 25
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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