CODEWITHSUNDEEP
databaseoracle-database
Pawel Batko
Pawel Batko

Reputation: 771

How to pass parameters to procedure at DBMS.SUBMIT?

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.SUBMIT passing 'ala123' argument

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?

Upvotes: 1

Views: 8111

Answers (2)

Alan
Alan

Reputation: 21

DECLARE
     jobInsertNo NUMBER;
  BEGIN
     DBMS_JOB.SUBMIT(jobInsertNo, /* instead of number */
                     q'{addJobTest('ala123');}',
                     SYSDATE,
                     'SYSDATE + (10/(24*60*60))');
  COMMIT;
END;

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

I've found a small typo in your PL/SQL block but other than that your code works:

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

Upvotes: 2

Related Questions