Getting error when executing dbms_job.run [message #218596] |
Thu, 08 February 2007 22:00 |
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 #218957 is a reply to message #218596] |
Mon, 12 February 2007 04:03 |
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.
|
|
|