Home » SQL & PL/SQL » SQL & PL/SQL » Getting error when executing dbms_job.run
Getting error when executing dbms_job.run [message #218596] Thu, 08 February 2007 22:00 Go to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member

BEGIN
DBMS_JOB.SUBMIT(
 job=>:jobno,
 what=>'QUERY_EMP(7788,:g_name,:g_sal,:g_comm);',
next_date=>trunc(sysdate+1)+10/24,
interval=>'trunc(sysdate+1)'
);
commit;
end;
/

PL/SQL procedure successfully completed

SQL> print jobno;

    JOBNO
---------
       21

SQL> variable g_name varchar2(30)
SQL> variable g_sal number
SQL> variable g_comm number
SQL> execute dbms_job.run(21);
BEGIN dbms_job.run(21); END;

*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1

The below one is my procedure code which i used in what parameter

PROCEDURE query_emp
   (p_id1 In emp.empno%TYPE,
    p_name OUT emp.ename%TYPE,
    p_salary OUT emp.sal%TYPE,
    p_comm OUT emp.comm%TYPE)
IS
BEGIN
  SELECT ename,sal,comm
  INTO p_name,p_salary,p_comm
  FROM emp
  WHERE empno=p_id1;
END query_emp;

12 rows selected.

SQL> EXECUTE QUERY_EMP(7788,:G_NAME,:G_SAL,:G_COMM);

PL/SQL procedure successfully completed.

SQL> PRINT G_NAME G_SAL G_COMM

G_NAME
--------------------------------
SCOTT


    G_SAL
---------
     3000


   G_COMM
---------
      500



THANKS FOR YOUR HELP
Re: Getting error when executing dbms_job.run [message #218615 is a reply to message #218596] Fri, 09 February 2007 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
ORA-12011: execution of string jobs failed


Cause: An error was caught in dbms_ijob.run from one or more jobs which were due to be run.

Action: Look at the alert log for details on which jobs failed and why.

Re: Getting error when executing dbms_job.run [message #218686 is a reply to message #218596] Fri, 09 February 2007 05:23 Go to previous messageGo to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

Thanks for your clue. I found the fallowing errors



ORA-12012: error on auto execute of job 21
ORA-01008: not all variables bound



I also tried to solve the issue. But i couldn't. Please help me.


Thanks in advance
Re: Getting error when executing dbms_job.run [message #218828 is a reply to message #218686] Sat, 10 February 2007 22:24 Go to previous messageGo to next message
babu_nalla
Messages: 1
Registered: February 2007
Junior Member


Search in google to know what error it is.


nalla
Re: Getting error when executing dbms_job.run [message #218957 is a reply to message #218596] Mon, 12 February 2007 04:03 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hi,
as you do not mind crashing your job each time it is regularly runs, you are just curious, why it does not work when you manually invoke it.

Try to figure how the dbms_job is implemented. I think it simply finds the job in the internal evidence and then runs the 'what' command, most probably through 'EXECUTE IMMEDIATE'.

SQL> set serveroutput on
SQL> variable p_str char(10);
SQL> exec :p_str := 'hallo'

PL/SQL procedure successfully completed

SQL> exec dbms_output.put_line( :p_str )
hallo

PL/SQL procedure successfully completed

SQL> exec execute immediate 'begin dbms_output.put_line( :p_str ); end;'
BEGIN execute immediate 'begin dbms_output.put_line( :p_str ); end;'; END;

*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 1


SQL> 

Note it throws the same error you achieved.

Realize the difference between SQLPLUS (EXEC[UTE]) and PL/SQL (EXECUTE IMMEDIATE, DBMS_JOB).
And the fact you cannot run SQLPLUS commands (moreover to get its environment variables) in PL/SQL package.
Previous Topic: PL/SQL struck in the way need some help
Next Topic: Upgrading jdk from 1.4.2 to 1.5 for oracle client.
Goto Forum:
  


Current Time: Sun Dec 08 07:25:15 CST 2024