database - 如何将参数传递给 DBMS.SUBMIT 中的过程？

Question

我有这样一个程序:

    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:

我应该如何将 varchar 参数传递给 DBMS.SUBMIT 中的 addJobTest 过程？

Answer 1

我在您的 PL/SQL block 中发现了一个小拼写错误，但除此之外您的代码有效:

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