Home » SQL & PL/SQL » SQL & PL/SQL » job automatically get offine (merged) (oracle 10.1.0.2.0 on IBM AIX 5.2 .oracle toad 8.5.1)
job automatically get offine (merged) [message #437226] Sat, 02 January 2010 02:43 Go to next message
oradba123
Messages: 86
Registered: June 2009
Location: india
Member




Hi all,


i am using toad 8.5.1 which connects to oracle 10g but in job field there ar few jobs which are working fine but one of job is deactivated automatically after sometime when i make activated by right click on it , it become activate but after some time automaticall deactivate.please could any one has face this problem. please sugget me what should i do. thanks a lot in advance.
Re: toad's job probelm [message #437228 is a reply to message #437226] Sat, 02 January 2010 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It must contain errors, see them in alert.log file.

Regards
Michel
Re: toad's job probelm [message #437231 is a reply to message #437228] Sat, 02 January 2010 03:51 Go to previous messageGo to next message
oradba123
Messages: 86
Registered: June 2009
Location: india
Member



Hi,

thanks fro your quick response yes you are right i got error in alert log file
error are below

ora-12012
ora-06550
pls-00201


but how do i solve it saying that 96 line or cloumn must be declared. you suggestions will be highly appreciated. thank in advance.
Re: toad's job probelm [message #437232 is a reply to message #437231] Sat, 02 January 2010 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-06550: line %s, column %s:\n%s
 *Cause:  Usually a PL/SQL compilation error.
 *Action:

See the code, try to execute it inline, out of a job; nothing can be said without this code.

Regards
Michel
Re: toad's job probelm [message #437233 is a reply to message #437231] Sat, 02 January 2010 03:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And (one of) the compilation error(s) is:

You have something in line 96 that isn't declared. Declare it before using it.
Re: toad's job probelm [message #437234 is a reply to message #437232] Sat, 02 January 2010 04:32 Go to previous messageGo to next message
oradba123
Messages: 86
Registered: June 2009
Location: india
Member


Hi,

there si propre declaration you can check i am copyin the code.
CREATE OR REPLACE PROCEDURE calc_finserv 
IS
   w_dd                  NUMBER;
   w_mm                  NUMBER;
   w_yy                  NUMBER;
   vcn_dd                NUMBER         := 0;
   vcn_mm                NUMBER         := 0;
   vcn_yy                NUMBER         := 0;
   w_ins_salary_amount   NUMBER         := 0;
   w_pay_salary          NUMBER         := 0;
   w_pay_trans           NUMBER         := 0;
   w_pay_rewrd           NUMBER         := 0;
   w_resturan_amount     NUMBER         := 0;
   ins_a                 NUMBER (12, 2);
   next_dgree            NUMBER;
   v_comtens_amount10    NUMBER (12, 2);
   v_comtens_amount15    NUMBER (12, 2);
   v_comtens_amount25    NUMBER (12, 2);
   v_comtens_amount      NUMBER (12, 2);
   P_STOP_DATE           DATE;

   CURSOR cr1
   IS
      SELECT   employee_joining_date, employee_no, employee_typeno,
               employee_house_status, employee_name, employee_salary,
               employee_trans, employee_house_value, employee_cost_no,
               employee_status_code, employee_grade_no,
               employee_nationality_no
          FROM personel
         WHERE employee_status_code <= 3 AND employee_typeno = 0
      ORDER BY employee_cost_no, employee_no;
BEGIN
 
SELECT TRUNC(SYSDATE-1)  
INTO   P_STOP_DATE
FROM   DUAL ;

--P_STOP_DATE := '31/12/2009';
   FOR k IN cr1
   LOOP
      calc_expert (k.employee_joining_date, p_stop_date, w_yy, w_mm, w_dd);
      ins_a := 0;

      IF k.employee_house_status = 1
      THEN
         ins_a := (k.employee_house_value / 12) + k.employee_salary;
      ELSIF k.employee_house_status = 2
      THEN
         IF k.employee_salary <= 12500 AND k.employee_nationality_no <> 1
         THEN
            ins_a := (k.employee_salary * 14) / 12;
         END IF;

         IF k.employee_salary > 12500 AND k.employee_nationality_no <> 1
         THEN
            ins_a := (25000 / 12) + k.employee_salary;
         END IF;

         IF     k.employee_salary <= 25000
            AND k.employee_nationality_no = 1
            AND k.employee_grade_no < 18
            AND k.employee_house_status = 2
         THEN
            ins_a := (k.employee_salary * 14) / 12;
         END IF;

         IF     k.employee_salary > 25000
            AND k.employee_nationality_no = 1
            AND k.employee_grade_no < 18
            AND k.employee_house_status = 2
         THEN
            ins_a := (50000 / 12) + k.employee_salary;
         END IF;

         IF     k.employee_grade_no > 17
            AND k.employee_nationality_no = 1
            AND k.employee_house_status = 2
         THEN
            ins_a := (k.employee_salary * 14) / 12;
         END IF;
      END IF;

      BEGIN
         SELECT TRUNC (term_amount)
           INTO next_dgree
           FROM terminations
          WHERE term_emp_typeno = k.employee_typeno
            AND term_employee_no = k.employee_no
            AND term_code = 2;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            next_dgree := 0;
      END;

      w_ins_salary_amount := NVL (ins_a, 0) + NVL (k.employee_trans, 0);

      IF NVL (w_yy, 0) >= 2 AND NVL (w_yy, 0) < 5
      THEN
         v_comtens_amount15 :=
              (  (  (  (NVL (w_yy, 0) * 365)
                     + (NVL (w_mm, 0) * 30)
                     + (NVL (w_dd, 0))
                    )
                  * 5
                 )
               / 365
              )
            * (NVL (w_ins_salary_amount, 0) / 30);
         v_comtens_amount25 := 0;
         v_comtens_amount10 := 0;
         v_comtens_amount := NVL (v_comtens_amount15, 0);
      ELSE
         IF NVL (w_yy, 0) >= 5 AND NVL (w_yy, 0) < 10
         THEN
            v_comtens_amount15 := (NVL (w_ins_salary_amount, 0) / 30) * 50;
            v_comtens_amount25 :=
                 (  (((NVL (w_yy, 0) - 5) * 20))
                  + (((NVL (w_mm, 0) * 30) + (NVL (w_dd, 0))) * 20) / 365
                 )
               * (NVL (w_ins_salary_amount, 0) / 30);
            v_comtens_amount10 := 0;
            v_comtens_amount :=
                      NVL (v_comtens_amount15, 0)
                      + NVL (v_comtens_amount25, 0);
         ELSE
            IF NVL (w_yy, 0) >= 10
            THEN
               v_comtens_amount15 := (NVL (w_ins_salary_amount, 0) / 30) * 75;
               v_comtens_amount25 := (NVL (w_ins_salary_amount, 0) / 30)
                                     * 150;
               v_comtens_amount10 :=
                    (  (((NVL (w_yy, 0) - 10) * 30))
                     + (((NVL (w_mm, 0) * 30) + (NVL (w_dd, 0))) * 30) / 365
                    )
                  * (NVL (w_ins_salary_amount, 0) / 30);
               v_comtens_amount :=
                    NVL (v_comtens_amount15, 0)
                  + NVL (v_comtens_amount25, 0)
                  + NVL (v_comtens_amount10, 0);
            ELSE
               v_comtens_amount15 := 0;
               v_comtens_amount10 := 0;
               v_comtens_amount25 := 0;
               v_comtens_amount   := 0;
            END IF;
         END IF;
      END IF;

      INSERT INTO quarterfinal
           VALUES (p_stop_date, k.employee_no, k.employee_status_code,
                   k.employee_cost_no, k.employee_salary, ins_a,
                   v_comtens_amount, next_dgree, w_yy, w_mm, w_dd, 0, NULL,
                   NULL);
   END LOOP;
END calc_finserv;
/


please sugget me what i have to do
[EDITED by LF: applied [code] tags]

[Updated on: Sat, 02 January 2010 08:06] by Moderator

Report message to a moderator

Re: toad's job probelm [message #437235 is a reply to message #437234] Sat, 02 January 2010 04:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Step one would be to format the code, so that it is actually readable.

Then run it in SQL*Plus, and fix the compilation errors.
Re: toad's job probelm [message #437237 is a reply to message #437233] Sat, 02 January 2010 04:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why should anyone spend a hour or so checking for compilation errors "by hand" when SQL*Plus can do it in a fraction of a second?
Re: toad's job probelm [message #437238 is a reply to message #437226] Sat, 02 January 2010 05:19 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Even TOAD is able to run SQL code copied into SQL Worksheet tab (it might be named slightly differently; I have only SQL developer, so I cannot check it).

Just curious - is this procedure really created in a job? Why? Is it supposed to change regularly? Seems strange to me. Or completely different code is run in the job? But, why would you not investigate it then?
job automatically get offine [message #438593 is a reply to message #437226] Mon, 11 January 2010 02:29 Go to previous messageGo to next message
oradba123
Messages: 86
Registered: June 2009
Location: india
Member




Hi,

my dbms_job get automaticall offlinen which is schedule monthly.when i make it online by usin toad just click on PLACE ONLINE button then it become online after some time it will be offline i say commit while closin toad also and after that i go to sql and log in thoru sysdba and say


sql>EXEC dbms_job.broken(jobno, FAlSE);
*
ERROR at line 1:
ORA-23421: job number 182 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 245
ORA-06512: at line 1

any help or adivce,thanks a lot in advance.
Re: job automatically get offine [message #438594 is a reply to message #438593] Mon, 11 January 2010 02:39 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to repost the same question.
If you have new information post it in the previous topic.
If you have no new information then you will not have more than you already got.

Did you fix the error in the previous topic?

Quote:
ORA-23421: job number 182 is not a job in the job queue

Either the job does not exist, either you do not own it.

Regards
Michel
Previous Topic: package
Next Topic: Create Materialzed view using WITH clause
Goto Forum:
  


Current Time: Mon Dec 05 21:22:57 CST 2016

Total time taken to generate the page: 0.13781 seconds