To executing a dynamic query containing functions with Execute immediate
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(70
begin prc_build_control_panel(70
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.PRC_BUILD_CONTROL_P
ORA-06512: at line 1
Kindly help me resolve the error.
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(70
begin prc_build_control_panel(70
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.PRC_BUILD_CONTROL_P
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;
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
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.
ASKER
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.
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
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;
EXECUTE IMMEDIATE EXEC_QUERY USING USER_ROLE_ID, GET_MONTH, USER_ROLE_ID, GET_MONTH;
ASKER
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?