CODEWITHSUNDEEP
oracle-databasestored-proceduresquery-stringcase-when
Marcus
Marcus

Reputation: 3869

How to call another Procedure in CASE WHEN in Oracle Procedure

I have created many Procedures to extract the data from remote database and stored it in my local database tables. The procedures run successfully when i run it individually. But now i want to create dynamic procedure which will call based on conditions. I have created small procedure for the same but getting an error when i run this procedure as : 

ORA-06512: 
01403. 00000 -  "no data found"

I am calling the procedure as EXT_EXTRACTION(1000161); I think the problem is i am not calling the Procedure in CASE WHEN properly and don't know how to call it correctly.

Here is my procedure;

PROCEDURE "EXT_EXTRACTION"(
   IN_KPI_DEF_ID IN NUMBER DEFAULT 0
) AS 
  ENTITY_CLASS_NAME Number := 0;
  IN_EVENT_ID NUMBER;
BEGIN
  Select EVENT_ID 
    INTO IN_EVENT_ID 
    from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION 
   WHERE KPI_DEF_ID = IN_KPI_DEF_ID;
  CASE 
    WHEN IN_EVENT_ID = 10049 
    THEN EXT_10049_ACTIVATE_OPTION;
  END CASE;

  COMMIT; 
END EXT_EXTRACTION;

Upvotes: 0

Views: 1104

Answers (1)

Dart XKey
Dart XKey

Reputation: 54

Try to surround select in your procedure with exception when no_data_found and look at result. May be this proc work from another user,which have no priveleges to select, or selected object is a view? May be you should create procedure like authid current_user?

Upvotes: 1

Related Questions