Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Chintan.

Asked: February 09, 2017 - 10:49 pm UTC

Last updated: February 15, 2017 - 4:04 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi,

I am working on Oracle database version is 11.2.0.4.0.
I have PL/SQL packages, which run from few minutes to couple of hours.
My requirement is that for each package execution, it should generate a trace file and once it is generated, it should be put in a table.

So far, I was able to generate trace files using below code:
DECLARE
   l_job_id VARCHAR2(10) := '1234';   -- unique sequence generated value
BEGIN
   dbms_session.set_identifier(l_job_id);
   dbms_monitor.client_id_trace_enable(client_id => l_job_id);
   
   mypackage.proc1;
   
   dbms_monitor.client_id_trace_disable(client_id => l_job_id);
END;
/


It would generate a trace file with client id value as (1234) in the directory set in the parameter 'user_dump_dest':

SELECT value
  FROM V$PARAMETER
 WHERE name = 'user_dump_dest';


When I run below command from SQL Plus, it creates trace file with its name containing job id e.g. in above case 1234.trc, in my directory:

!trcsess output=/mydir/1234.trc clientid=1234 /u01/app/oracle/diag/rdbms/rs5dev/rs5dev/trace/*.trc


But I want to run above command from within my anonymous block i.e. within PL/SQL. I will create Oracle directory for it.
Also, once trace file is generated using above command, I want to put that file in an Oracle table, so that it can be later on downloaded from my Oracle APEX application.

Is above solution achievable? If so, please advice.

Thank you,
Chintan Gandhi

and Connor said...

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Chintan Gandhi, February 14, 2017 - 6:24 pm UTC

Thanks Connor for your response.
I tried above link to create java class. I have also granted java permissions. But I am still not able to execute command 'trcsess' using it. It gives me error -

I have run below code:
User XXRS:

create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;

public class Util extends Object
{
  public static int RunThis(String args)
  {
  Runtime rt = Runtime.getRuntime();
  int        rc = -1;

  try
  {
     Process p = rt.exec(args);

     int bufSize = 4096;
     BufferedInputStream bis =
      new BufferedInputStream(p.getInputStream(), bufSize);
     int len;
     byte buffer[] = new byte[bufSize];

     // Echo back what the program spit out
     while ((len = bis.read(buffer, 0, bufSize)) != -1)
        System.out.write(buffer, 0, len);

     rc = p.waitFor();
  }
  catch (Exception e)
  {
     e.printStackTrace();
     rc = -1;
  }
  finally
  {
     return rc;
  }
  }
}
/

CREATE OR REPLACE PACKAGE XXDM_JAVA_UTIL_PKG
AS
FUNCTION run_cmd (
   p_cmd IN VARCHAR2) 
RETURN NUMBER;

PROCEDURE rc (
   p_cmd IN VARCHAR2);
END XXDM_JAVA_UTIL_PKG;
/

CREATE OR REPLACE PACKAGE BODY XXDM_JAVA_UTIL_PKG
AS

   FUNCTION run_cmd (
      p_cmd IN VARCHAR2) 
   RETURN NUMBER
   AS 
   LANGUAGE JAVA
   NAME 'Util.RunThis(java.lang.String) return integer';

   PROCEDURE rc (
      p_cmd IN VARCHAR2)
   AS
      x number;
   BEGIN
      x := run_cmd(p_cmd);
   END;
END XXDM_JAVA_UTIL_PKG;
/


User SYS
BEGIN
  dbms_java.grant_permission( 'XXRS', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' );
  dbms_java.grant_permission('XXRS', 'SYS:java.lang.RuntimePermission', '*', 'writeFileDescriptor' )
END;
/


User XXRS

variable x number;
set serveroutput on;
exec dbms_java.set_output(100000);
exec :x := XXDM_JAVA_UTIL_PKG.RUN_CMD('trcsess output=/u01/app/oracle/product/custom/orarev/trace/8039.trc clientid=8039 /u01/app/oracle/diag/rdbms/rs5dev/rs5dev/trace/*.trc');


I get below error:

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute) has not been granted to XXRS. The PL/SQL to grant this is dbms_java.grant_permission( 'XXRS', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' )
 at java.security.AccessControlContext.checkPermission(AccessControlContext.java:409)
 at java.security.AccessController.checkPermission(AccessController.java:581)
 at java.lang.SecurityManager.checkPermission(SecurityManager.java:534)
 at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java:210)
 at java.lang.SecurityManager.checkExec(SecurityManager.java:784)
 at java.lang.ProcessBuilder.start(ProcessBuilder.java:476)
 at java.lang.Runtime.exec(Runtime.java:593)
 at java.lang.Runtime.exec(Runtime.java:431)
 at java.lang.Runtime.exec(Runtime.java:328)
 at Util.RunThis(Util:13)

Connor McDonald
February 15, 2017 - 4:04 am UTC

Are you sure your SYS grants worked ? Because the script you have there is missing a ';'

Chintan Gandhi, February 15, 2017 - 6:10 pm UTC

I am sorry, I had to remove my other attempts to give grants. And that is where I accidentally removed ;. I used below SQL to verify if above grants were created successfully.

select *
from DBA_JAVA_POLICY
where grantee = 'XXRS'

Above SQL game me 2 records for the 2 grants I executed.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library