I have below stored procedure for which i am getting an error as missing keyword. I am trying to run the sql statement by putting them into variable because i have use the database link dynamically in the sql query. I am getting the error while using case statement in merge query. When i am using dbms output line to print the merge query then its printing the complete case statement instead of only ID.
PROCEDURE "EXT_SOAP_MONITORING"(IN_DB_LINK IN varchar2) AS
LAST_SM_ID Number := 0;
LAST_CAPT_DATE DATE;
LAST_SM_ID_MB Number := 0;
LAST_CAPT_DATE_MB DATE;
l_sql VARCHAR2(5000);
l_sql1 VARCHAR2(5000);
DB_CONNECTION_NAME VARCHAR2(100);
BEGIN
Select DB_LINK INTO DB_CONNECTION_NAME FROM RATOR_MONITORING_CONFIGURATION.DB_CONNECTION WHERE DB_LINK = IN_DB_LINK;
--DELETE FROM TEMP_SOAP_MONITORING table before inserting new data into this table
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SOAP_MONITORING';
-- first retrieve the last id (of the newest record) which has been imported at last extraction
--FONIC
SELECT LAST_TASK_ID INTO LAST_SM_ID FROM CAPTURING where DB_TABLE='TEMP_SOAP_MONITORING' and DB ='FONIC_RETAIL';
SELECT CAPTURING_DATE INTO LAST_CAPT_DATE from CAPTURING WHERE DB_TABLE='TEMP_SOAP_MONITORING' and DB ='FONIC_RETAIL';
--MB
SELECT LAST_TASK_ID INTO LAST_SM_ID_MB FROM CAPTURING where DB_TABLE='TEMP_SOAP_MONITORING' and DB ='MB_RETAIL';
SELECT CAPTURING_DATE INTO LAST_CAPT_DATE_MB from CAPTURING WHERE DB_TABLE='TEMP_SOAP_MONITORING' and DB ='MB_RETAIL';
l_sql:=
'merge into TEMP_SOAP_MONITORING TSM
using (
select * from
(select ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE from
SOAP_MONITORING@'||DB_CONNECTION_NAME||' WHERE
ID > (CASE WHEN '||IN_DB_LINK||' = ''FONIC_RETAIL'' THEN ' || LAST_SM_ID || ' ELSE ' || LAST_SM_ID_MB ||' END) AND
WEB_SERVICE_NAME =''RatorWebShopService'' and WEB_METHOD_NAME = ''placeShopOrder'') where rownum <=1000
) data
ON (TSM.ID = data.ID)
when not matched then
insert(ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE,DB_LINK)
values(data.ID,data.REQUEST_XML,data.RESPONSE_XML,data.WEB_SERVICE_NAME,data.WEB_METHOD_NAME,data.CREATE_DATE,data.ERROR_CODE,data.ERROR_MESSAGE,'||DB_CONNECTION_NAME ||')';
DBMS_OUTPUT.PUT_LINE('lsql' || l_sql);
DBMS_OUTPUT.PUT_LINE('lsql' || l_sql);
execute immediate l_sql;
END EXT_SOAP_MONITORING;
Below is my DBMS result:
merge into TEMP_SOAP_MONITORING TSM
using (
select * from
(select ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE from
SOAP_MONITORING@FONIC_RETAIL WHERE
ID > (CASE WHEN FONIC_RETAIL = 'FONIC_RETAIL' THEN 201601071130573261 ELSE 201601071130573261 END) AND
WEB_SERVICE_NAME ='RatorWebShopService' and WEB_METHOD_NAME = 'placeShopOrder') where rownum <=1000
) data
ON (TSM.ID = data.ID)
when not matched then
insert(ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE,DB_LINK)
values(data.ID,data.REQUEST_XML,data.RESPONSE_XML,data.WEB_SERVICE_NAME,data.WEB_METHOD_NAME,data.CREATE_DATE,data.ERROR_CODE,data.ERROR_MESSAGE,FONIC_RETAIL)
As well as the missing whitespace after the THEN that @KevinEsche pointed out, you're also missing an AND (or OR) after the case statement:
<snip>
|| ' END)
WEB_SERVICE_NAME =''RatorWebShopService'' <snip> -- <---- missing and/or before web_service_name
So, looking at the merge statement which was output by your procedure, there are two issues that leap out, coincidentally on the same line:
ID > (CASE WHEN DB_LINK = 'FONIC_RETAIL' THEN201601071130573261 ELSE END) AND
You forgot to add in the space after the THEN in THEN' || LAST_SM_ID, plus you haven't catered for LAST_SM_ID_MB being null.
Here's how I'd write the procedure
procedure ext_soap_monitoring (in_db_link in varchar2)
as
last_sm_id number := 0;
last_capt_date date;
last_sm_id_mb number := 0;
last_capt_date_mb date;
l_sql varchar2(5000);
l_sql1 varchar2(5000);
db_connection_name varchar2(100);
begin
select db_link
into db_connection_name
from rator_monitoring_configuration.db_connection
where db_link = in_db_link;
--DELETE FROM TEMP_SOAP_MONITORING table before inserting new data into this table
execute immediate 'TRUNCATE TABLE TEMP_SOAP_MONITORING';
-- first retrieve the last id (of the newest record) which has been imported at last extraction
--FONIC
select last_task_id, capturing_date
into last_sm_id, last_capt_date
from capturing
where db_table = 'TEMP_SOAP_MONITORING'
and db = 'FONIC_RETAIL';
--MB
select last_task_id, capturing_date
into last_sm_id_mb, last_capt_date_mb
from capturing
where db_table = 'TEMP_SOAP_MONITORING'
and db = 'MB_RETAIL';
l_sql:=
'merge into temp_soap_monitoring tsm
using (select *
from (select id,
request_xml,
response_xml,
web_service_name,
web_method_name,
create_date,
error_code,
error_message
from soap_monitoring@'||db_connection_name||'
where id > (case when :db_connection_name = ''FONIC_RETAIL'' then ' || last_sm_id || ' else ' || last_sm_id_mb ||' end)
and web_service_name = ''RatorWebShopService''
and web_method_name = ''placeShopOrder'')
where rownum <= 1000) data
on (tsm.id = data.id)
when not matched then
insert (id,
request_xml,
response_xml,
web_service_name,
web_method_name,
create_date,
error_code,
error_message,
db_link)
values (data.id,
data.request_xml,
data.response_xml,
data.web_service_name,
data.web_method_name,
data.create_date,
data.error_code,
data.error_message,
:db_connection_name)';
dbms_output.put_line('lsql' || l_sql);
execute immediate l_sql using db_connection_name, db_connection_name;
end ext_soap_monitoring;
/
Note the use of the bind variables to remove the literal placements of the variables in the sql itself. I know you can't avoid using dynamic sql to add in the database link name when you're selecting from the table, but you can use bind variables elsewhere, and this should make your code less prone to sql injection.
Also, see how I've formatted the merge statement - yes, it takes up more room in the variable, but it is one heck of a lot more readable now!