如何将参数传递给DBMS.SUBMIT上的过程?
问题描述:
我有这样一个过程:如何将参数传递给DBMS.SUBMIT上的过程?
create or replace procedure addJobTest (
opisArg varchar2
)as
begin
insert into JobsTest(opis) values (opisArg);
end addJobTest;
我试图用DBMS.SUBMIT
过客 'ala123' 的说法
Declare
jobInsertNo number;
BEGIN
DBMS_JOB.SUBMIT (number,
'addJobTest('||''''||'ala123'||''''||');',
SYSDATE,
'SYSDATE + (10/(24*60*60))');
COMMIT;
END;
使用它,但我得到一个错误。它说
ORA-06550: line 5, column 33:
PLS-00103: Encountered the symbol "ALA123" when expecting one of the following:
) , * & | = - + </> at in is mod remainder not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
The symbol ", was inserted before "ALA123" to continue.
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
我应该如何通过在DBMS.SUBMIT
一个varchar参数addJobTest程序?
答
我发现一个小错字在你的PL/SQL块但除此之外,你的代码工作:
SQL> CREATE TABLE JobsTest (opis VARCHAR2(20));
Table created
SQL> CREATE OR REPLACE PROCEDURE addJobTest(opisArg VARCHAR2) AS
2 BEGIN
3 INSERT INTO JobsTest (opis) VALUES (opisArg);
4 END addJobTest;
5/
Procedure created
SQL> DECLARE
2 jobInsertNo NUMBER;
3 BEGIN
4 DBMS_JOB.SUBMIT(jobInsertNo, /* instead of number */
5 'addJobTest('||''''||'ala123'||''''||');',
6 SYSDATE,
7 'SYSDATE + (10/(24*60*60))');
8 COMMIT;
9 END;
10/
PL/SQL procedure successfully completed
SQL> select * from jobstest;
OPIS
--------------------
ala123
答
DECLARE
jobInsertNo NUMBER;
BEGIN
DBMS_JOB.SUBMIT(jobInsertNo, /* instead of number */
q'{addJobTest('ala123');}',
SYSDATE,
'SYSDATE + (10/(24*60*60))');
COMMIT;
END;