CODEWITHSUNDEEP
oracle-databaseplsqlstored-functions
Marcus
Marcus

Reputation: 3869

Oracle Stored Function not returning result as sql string

I have below Oracle stored procedure which is running fine and returning result for condition IF (V_KPI_TYPE_ID = 13). But when i used condition IF ((V_KPI_TYPE_ID = 12) OR (V_KPI_TYPE_ID = 18)) then its not returning any result.I try to debug my code and try to run the select query where i am putting the result into variable V_SYSTEM_SQL_CHECK and returning the value and the select query is working fine. The value return in this variable will be sql string. And i am using varchar2 data type to store this sql string. So i think the data type i am using is to store sql string into variable is incorrect but not sure

FUNCTION BUILD_ALERT_EMAIL_BODY
(
  IN_ALERT_LOGS_TIMESTAMP IN TIMESTAMP
, IN_ALERT_LOGS_LOG_DESC IN VARCHAR2
, IN_KPI_LOG_ID IN NUMBER
) RETURN VARCHAR2 AS
BODY VARCHAR2(4000) := '';    
V_SYSTEM_SQL_CHECK VARCHAR2(3000) := '';
V_SOAP_SERVICE_NAME VARCHAR2(100) := '';

BEGIN
Select KPI_DEF_ID INTO V_KPI_DEF_ID FROM KPI_LOGS WHERE KPI_LOG_ID = IN_KPI_LOG_ID;  

Select KT.KPI_TYPE_ID INTO V_KPI_TYPE_ID FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD JOIN RATOR_MONITORING_CONFIGURATION.KPI_TYPE KT ON KD.KPI_TYPE = KT.KPI_TYPE_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;

DBMS_OUTPUT.PUT_LINE('KPI_TYPE_ID:' || V_KPI_TYPE_ID);

Select KT.CHECK_SQL INTO V_SYSTEM_SQL_CHECK FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD JOIN RATOR_MONITORING_CONFIGURATION.SYSTEM_SQL_CHECK KT ON KD.SYSTEM_SQL_CHECK_ID = KT.SYSTEM_SQL_CHECK_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;

DBMS_OUTPUT.PUT_LINE('SYSTEM_SQL_CHECK' || V_SYSTEM_SQL_CHECK);

Select SP.SOAP_SERVICE_NAME INTO V_SOAP_SERVICE_NAME FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD JOIN RATOR_MONITORING_CONFIGURATION.SOAP_SERVICE SP ON KD.SOAP_SERVICE_ID = SP.SOAP_SERVICE_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;

    BODY := to_char(IN_ALERT_LOGS_TIMESTAMP,'DD.MM.YYYY HH24:MI') || Chr(13) || Chr(10);       

IF ((V_KPI_TYPE_ID = 12) OR (V_KPI_TYPE_ID = 18)) THEN
    BODY := BODY || 'SQL USED:' || V_SYSTEM_SQL_CHECK || Chr(13) || Chr(10);
  ELSE IF (V_KPI_TYPE_ID = 13) THEN 
    BODY := BODY || 'SOAP_SERVICE:' || Chr(13) || Chr(10);
    BODY := BODY || V_SOAP_SERVICE_NAME || Chr(13) || Chr(10);        
  END IF;
END IF;

BODY := BODY || 'ALERT_DESCRIPTION:' || to_char(IN_ALERT_LOGS_LOG_DESC);

    RETURN BODY;
END BUILD_ALERT_EMAIL_BODY;

When i debug my code then its returning the result as below which is correct :

KPI_TYPE_ID:18
SYSTEM_SQL_CHECK:select CASE WHEN (o.cnt = 0) THEN 0 ELSE round((r.cnt / o.cnt)*100,3) END from (select count(*) as cnt from rate_errors where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR, 'YYYYMMDDHH24MISS') ||'0000' ) r cross join
     (select count(*) as cnt
      from SDR_O2
      where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR, 'YYYYMMDDHH24MISS')||'0000'
    ) o

Upvotes: 0

Views: 593

Answers (2)

JyothiKumar
JyothiKumar

Reputation: 9

Modify the below line in your code 

BODY := to_char(IN_ALERT_LOGS_TIMESTAMP,'DD.MM.YYYY HH24:MI') || Chr(13) || Chr(10);

like this and try-

BODY := 'to_char(' || IN_ALERT_LOGS_TIMESTAMP || ',''DD.MM.YYYY HH24:MI'')' || Chr(13) || Chr(10);

Also modify wherever BODY is assigned with to_char function which is sql.

Upvotes: -1

Utsav
Utsav

Reputation: 8093

just use 

  IF (V_KPI_TYPE_ID = 12 OR V_KPI_TYPE_ID = 18) THEN

Let me know if it still doesn't return result

Upvotes: 1

Related Questions