Home » SQL & PL/SQL » SQL & PL/SQL » using Dbms_Job to call the procedure (Oracle 11g, Win XP)
using Dbms_Job to call the procedure [message #405685] Fri, 29 May 2009 01:27 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I have a OWB Mapping which would create a package. I want to schedule a job to run the Procedure in the package,

How do i write DBMS_JOB for calling the following block, Please suggest.

DECLARE
   p_status             VARCHAR2 (200);
   p_deptno             NUMBER;
   p_max_no_of_errors   VARCHAR2 (200);
   p_commit_frequency   VARCHAR2 (200);
   p_operating_mode     VARCHAR2 (200);
   p_bulk_size          VARCHAR2 (200);
   p_audit_level        VARCHAR2 (200);
   p_purge_group        VARCHAR2 (200);
BEGIN
   p_deptno := 20;
   p_max_no_of_errors := NULL;
   p_commit_frequency := NULL;
   p_operating_mode := NULL;
   p_bulk_size := NULL;
   p_audit_level := NULL;
   p_purge_group := NULL;
   owbsys.wb_workspace_management.set_workspace ('ASHOK_WORKSPACE',
                                                 'ASHOK_BL'
                                                );
   map_filter_parameter.main (p_status                => p_status,
                              p_deptno                => p_deptno,
                              p_max_no_of_errors      => p_max_no_of_errors,
                              p_commit_frequency      => p_commit_frequency,
                              p_operating_mode        => p_operating_mode,
                              p_bulk_size             => p_bulk_size,
                              p_audit_level           => p_audit_level,
                              p_purge_group           => p_purge_group
                             );
   DBMS_OUTPUT.put_line ('P_STATUS = ' || p_status);
END;


map_filter_parameter.main is the procedure which i should call on a everyday basis ( Daily job scheduling).

If i were use the DBMS_JOB as following, what to pass to "WHAT" Parameter,

DBMS_JOB.SUBMIT( 
   job       OUT    BINARY_INTEGER,
   what      IN     VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE, 
   interval  IN     VARCHAR2 DEFAULT 'NULL',
   no_parse  IN     BOOLEAN DEFAULT FALSE,
   instance  IN     BINARY_INTEGER DEFAULT ANY_INSTANCE,
   force     IN     BOOLEAN DEFAULT FALSE);


Regards,
Ashoka BL

[Updated on: Fri, 29 May 2009 01:34]

Report message to a moderator

Re: using Dbms_Job to call the procedure [message #405688 is a reply to message #405685] Fri, 29 May 2009 01:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you want to run a block of code multiple times, you would typically create a stored procedure from it.
So, change your anonymous block to a (named) procedure and call that.
Re: using Dbms_Job to call the procedure [message #405691 is a reply to message #405685] Fri, 29 May 2009 01:50 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Thanks Frank, that's what i did as following, but i am getting one Error.

While executing the anonymous block i am not getting error. But if i put the same set of code in a procedure i am getting an error.

anonymous block
DECLARE
   p_status             VARCHAR2 (200);
   p_deptno             NUMBER;
   p_max_no_of_errors   VARCHAR2 (200);
   p_commit_frequency   VARCHAR2 (200);
   p_operating_mode     VARCHAR2 (200);
   p_bulk_size          VARCHAR2 (200);
   p_audit_level        VARCHAR2 (200);
   p_purge_group        VARCHAR2 (200);
BEGIN
   p_deptno := 20;
   p_max_no_of_errors := NULL;
   p_commit_frequency := NULL;
   p_operating_mode := NULL;
   p_bulk_size := NULL;
   p_audit_level := NULL;
   p_purge_group := NULL;
   owbsys.wb_workspace_management.set_workspace ('ASHOK_WORKSPACE',
                                                 'ASHOK_BL'
                                                );
   map_filter_parameter.main (p_status                => p_status,
                              p_deptno                => p_deptno,
                              p_max_no_of_errors      => p_max_no_of_errors,
                              p_commit_frequency      => p_commit_frequency,
                              p_operating_mode        => p_operating_mode,
                              p_bulk_size             => p_bulk_size,
                              p_audit_level           => p_audit_level,
                              p_purge_group           => p_purge_group
                             );
   DBMS_OUTPUT.put_line ('P_STATUS = ' || p_status);
END;


StoredProcedure
CREATE OR REPLACE PROCEDURE executemapping (i_deptno dept.deptno%TYPE)
IS
   p_status             VARCHAR2 (200);
   p_deptno             NUMBER;
   p_max_no_of_errors   VARCHAR2 (200);
   p_commit_frequency   VARCHAR2 (200);
   p_operating_mode     VARCHAR2 (200);
   p_bulk_size          VARCHAR2 (200);
   p_audit_level        VARCHAR2 (200);
   p_purge_group        VARCHAR2 (200);
BEGIN
   p_deptno := i_deptno;
   p_max_no_of_errors := NULL;
   p_commit_frequency := NULL;
   p_operating_mode := NULL;
   p_bulk_size := NULL;
   p_audit_level := NULL;
   p_purge_group := NULL;
   owbsys.wb_workspace_management.set_workspace ('ASHOK_WORKSPACE',
                                                 'ASHOK_BL'
                                                );
   map_filter_parameter.main (p_status                => p_status,
                              p_deptno                => p_deptno,
                              p_max_no_of_errors      => p_max_no_of_errors,
                              p_commit_frequency      => p_commit_frequency,
                              p_operating_mode        => p_operating_mode,
                              p_bulk_size             => p_bulk_size,
                              p_audit_level           => p_audit_level,
                              p_purge_group           => p_purge_group
                             );
   DBMS_OUTPUT.put_line ('P_STATUS = ' || p_status);
END;


Error:
PLS-00201: identifier 'OWBSYS.WB_WORKSPACE_MANAGEMENT' must be declared


Do i need to give Permission from OWBSYS Schema ? if so how the same executed in anonymous block.

IF the error get resolved then i shall do like following to schedule the job

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'executemapping (20);'
     ,next_date  => to_date('29/05/2009 12:16:02','dd/mm/yyyy hh24:mi:ss')
     ,interval   => 'TRUNC(SYSDATE+1)'
     ,no_parse   => FALSE
    );
END;
Re: using Dbms_Job to call the procedure [message #405692 is a reply to message #405691] Fri, 29 May 2009 01:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Role-grants are not applicable if you create stored procedures.
Re: using Dbms_Job to call the procedure [message #405694 is a reply to message #405685] Fri, 29 May 2009 02:01 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Frank,

I gave the EXECUTE permission from the OWBSYS Schema, and now i am not getting the error.

Please suggest the following approach is correct,

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'executemapping (20);'
     ,next_date  => to_date('29/05/2009 12:16:02','dd/mm/yyyy hh24:mi:ss')
     ,interval   => 'TRUNC(SYSDATE+1)'
     ,no_parse   => FALSE
    );
END;


I have observed DBMS_JOB.SUBMIT asks for user name and password everytime ( at the time of running), can we automate it,meaning can we make it a background process so that giving the user credentials and other details will be elliminated
Re: using Dbms_Job to call the procedure [message #405696 is a reply to message #405694] Fri, 29 May 2009 02:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ashoka_bl wrote on Fri, 29 May 2009 09:01

I have observed DBMS_JOB.SUBMIT asks for user name and password everytime ( at the time of running)


Can you show us what you mean? Copy-paste from sqlplus?
Re: using Dbms_Job to call the procedure [message #405720 is a reply to message #405685] Fri, 29 May 2009 04:07 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

There was some Invalid jobs running,that's the reason TOAD was asking the user name and password to connect everytime,

Now it is not. Its working fine Smile Thanks for the suggestions
Re: using Dbms_Job to call the procedure [message #405734 is a reply to message #405720] Fri, 29 May 2009 04:31 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Get rid of TOAD if you cannot tell which part of your errors/actions/etc is TOAD and which parts are database- or code-related.
First get a good understanding of what it is you are doing before you use fancy pancy tools.
Previous Topic: Finding matching records from another table
Next Topic: How to stop a running job
Goto Forum:
  


Current Time: Thu Dec 08 00:23:52 CST 2016

Total time taken to generate the page: 0.15856 seconds