oracle 如何将参数传递给 DBMS.SUBMIT 的过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4828651/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass parameters to procedure at DBMS.SUBMIT?
提问by Pawel Batko
I have such a procedure:
我有这样一个程序：
create or replace procedure addJobTest (
opisArg varchar2
)as
begin
insert into JobsTest(opis) values (opisArg);
end addJobTest;
I'm trying to use it with DBMS.SUBMITpassing 'ala123' argument
我正在尝试DBMS.SUBMIT通过传递 'ala123' 参数来使用它
Declare
jobInsertNo number;
BEGIN
DBMS_JOB.SUBMIT (number,
'addJobTest('||''''||'ala123'||''''||');',
SYSDATE,
'SYSDATE + (10/(24*60*60))');
COMMIT;
END;
but i get an error. It says
但我收到一个错误。它说
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:
How should I pass a varchar argument to addJobTest procedure at DBMS.SUBMIT?
我应该如何将 varchar 参数传递给 addJobTest 过程DBMS.SUBMIT？
回答by Vincent Malgrat
I've found a small typo in your PL/SQL block but other than that your code works:
我在您的 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
回答by Alan
DECLARE
jobInsertNo NUMBER;
BEGIN
DBMS_JOB.SUBMIT(jobInsertNo, /* instead of number */
q'{addJobTest('ala123');}',
SYSDATE,
'SYSDATE + (10/(24*60*60))');
COMMIT;
END;
