Solved

To executing a dynamic query containing functions with Execute immediate

Posted on 2008-06-15
5
589 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
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 65
statspack purge automate 7 52
how to replace '&' and '()' in sql query for oracle using regex 8 60
grouping on time windows 6 42
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

910 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

21 Experts available now in Live!

Get 1:1 Help Now