Home » SQL & PL/SQL » SQL & PL/SQL » error in creating DBMS_SCHEDULER job (Oracle 11g)
error in creating DBMS_SCHEDULER job [message #578173] Mon, 25 February 2013 04:26 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have create the following procedure to create individual jobs dynamically on each schema.

CREATE OR REPLACE PROCEDURE APSP.JOB_TEST_RAM
AS
   V_SQL   VARCHAR2 (1000);
BEGIN
   FOR i
   IN (SELECT   gbm.OWNER_NAME owner_name,
                vdb.NAME || '_BU_' || gbm.BU_CODE,
                gbm.bu_id bu_id
         FROM   apps_global.GLOBAL_BU_MAPPING gbm, v$database vdb
        WHERE   OWNER_NAME NOT IN ('APPS_GLOBAL'))
   LOOP
      V_SQL :=
         'BEGIN 
              DBMS_SCHEDULER.CREATE_JOB (
                  job_name => '''
         || i.owner_name
         || '_archive_job'',
      job_class => '''
         || i.owner_name
         || '_job_class'',
                  job_type => ''PLSQL_BLOCK'',
                  job_action => ''begin APSP.POP_TARGET(''APPS_XX'',i.owner,i.buid); end;'',
                  auto_drop => TRUE,
                  comments => '''|| i.owner_name||''',
              enabled => TRUE);
            END;';

      EXECUTE IMMEDIATE V_SQL;
   END LOOP;
END;
/

The procedure is having following arguments.

CREATE OR REPLACE PROCEDURE APSP.POP_TARGET (
 p_schema_name           VARCHAR2,
   p_actual_schema_name    VARCHAR2,
   p_buid                  NUMBER
)

While executing this procedure I am getting the below error.

PLS-00103: Encountered the symbol "APPS_XX" when expecting one of the following:

Please help me.

Thanks in advance .
Re: error in creating DBMS_SCHEDULER job [message #578174 is a reply to message #578173] Mon, 25 February 2013 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
''APPS_XX'' should be ''''APPS_XX'''' (I think).

Regards
Michel
Re: error in creating DBMS_SCHEDULER job [message #578175 is a reply to message #578174] Mon, 25 February 2013 04:55 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your reply.

That's working fine.

What about the variables i.owner,i.buid these variables should be passed dynamically.

Please help me.

If I pass as i.owner,i.buid procedure is taking as it is instead of values for these
variables.

Please help me.

Thanks.
Re: error in creating DBMS_SCHEDULER job [message #578180 is a reply to message #578175] Mon, 25 February 2013 05:43 Go to previous messageGo to next message
cookiemonster
Messages: 11170
Registered: September 2008
Location: Rainy Manchester
Senior Member
Same way you set i.owner_name.

1st rule of debugging dynamic sql - assign the dynamic string to a variable, display the variable using dbms_output or similar. Check you can run the output string in sqlplus.
Re: error in creating DBMS_SCHEDULER job [message #578273 is a reply to message #578180] Tue, 26 February 2013 06:22 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I tried as below

''begin APPS_GLOBAL.ARCHIVE_TARGET_TABLES(''''APPS_XX'''','''''||i.owner_name||''''','''''||i.bu_id||'''''); end;''


But buid is taking in quotations.

Please help me how to avoid quotations.

Thanks

Re: error in creating DBMS_SCHEDULER job [message #578278 is a reply to message #578273] Tue, 26 February 2013 07:29 Go to previous message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove them.
We told you what to do.
Post what you have and what you want.

Regards
Michel
Previous Topic: ORA-01830: date format picture ends before converting entire input string
Next Topic: Sequence Trigger
Goto Forum:
  


Current Time: Thu Nov 20 15:41:28 CST 2014

Total time taken to generate the page: 0.10325 seconds