Finding jobs currently running or history about the jobs

SELECT job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;

Also one can use the following view to find the history details of job that has run.

SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details;

To find the jobs that haven’t succeeded
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details where status ‘SUCCEEDED’;

ORA-01720: grant option does not exist for a table

We need users of specific role, need select privileges on tables and views owned by schema owners.


However while We try to grant select privileges on some views, I come accross a somewhat particular error as:

paul @ agentdb-live > grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT;
grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT
                         *
ERROR at line 1:
ORA-01720: grant option does not exist for 'MMUSER_GW.DBBL_AGENT_DATA'

The reason for it is that the view VIEW_CUST_AGENT_DIST_INFO owned by AGENTBIP is built on top of the table DBBL_AGENT_DATA owned by someone else ie MMUSER_GW.

AGENTBIP cannot give privileges on these kind of views to someone else ie USER_1 as long as OWNER_VIEW has not the privileges WITH GRANT OPTION for the underlying tables

The solution

paul @ agentdb-live > Grant select on MMUSER_GW.DBBL_CONSUMER_ACCOUNT to AGENTBIP with grant option;

Grant succeeded.

SYSTEM @ agentdb-live > grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT;

Grant succeeded.

Calling webservice from PLSQL

Sometimes for software architectural issue we are in need to call webservice API. Suppose, we are using database for all type of backend processing, in that scenario its very impractical to call Webservice using another application or language. We can easily overcome this scenario by calling webservice from plsql code. For this purpose, we can use the below code:


Its a package containing the Stored Procedure to call a soap service form PL Sql:

Package Spec:

create or replace PACKAGE PKG_ABS_ALERT_WS AS 

  /* TODO enter package declarations (types, exceptions, methods etc) here */ 

 PROCEDURE SP_CALL_SMS_SERVICE(

  P_MOBILE_NUM IN VARCHAR2,

  P_MESSAGE IN VARCHAR2

  );

END PKG_ABS_ALERT_WS;

Package Body:

create or replace PACKAGE BODY PKG_ABS_ALERT_WS AS
  PROCEDURE SP_CALL_SMS_SERVICE(
  P_MOBILE_NUM IN VARCHAR2,
  P_MESSAGE IN VARCHAR2
  ) AS

  vg_funciton_fnc VARCHAR2(256);
  vg_ws_address   VARCHAR2(255);
  l_namespace VARCHAR2(255);
  l_return    VARCHAR2(32767);
  ol_req  soap_api.t_request;
  ol_resp soap_api.t_response;

  BEGIN  
    DBMS_OUTPUT.PUT_LINE('P_MOBILE_NUM: '||P_MOBILE_NUM||'  P_MESSAGE:'||P_MESSAGE );   
     vg_funciton_fnc:= 'ns1:sendSMS';
     vg_ws_address:= 'http://:8080/NotificationService/SMSService?wsdl';
     l_namespace  := 'xmlns:ns1="http://sms.notification.dbbl.com/"';

       --we initilize a new request 
           ol_req := soap_api.new_request(vg_funciton_fnc,l_namespace);
        
       -- we started to add parameters
          
              
          soap_api.add_parameter(ol_req,
                                 'channelName','ABS'
                                 );
                             
          soap_api.add_parameter(ol_req,
                                 'mobileNo',P_MOBILE_NUM
                                  );                               
          soap_api.add_parameter(ol_req,
                                 'messageText',P_MESSAGE
                                 );
          soap_api.add_parameter(ol_req,
                                 'refId',
                                 alrt_seq.nextval);
                                 
        
                       
          -- we call the web service
          ol_resp := soap_api.invoke(ol_req, vg_ws_address, vg_funciton_fnc);
          
          -- we get back the results
          l_return := soap_api.get_return_value(ol_resp,
                                           'return', -- result tag name
                                           'xmlns:m="' || --can be change as "xmlns:n1"
                                           vg_ws_address || '"');
                                           
         DBMS_OUTPUT.PUT_LINE('Output: '||l_return);                          
     
  END SP_CALL_SMS_SERVICE;

END PKG_ABS_ALERT_WS;