CODEWITHSUNDEEP
oracle-database
BHAVISH
BHAVISH

Reputation: 11

Passing Dynamic values to a procedure thats being called using dbms_job.Submit

I have a master table (Master_Tab_details) having the columns "Table_name", "Column_name" , This Master table has 180 such records ..For All these tables i have to create the corresponding image tables . I have a procedure written to create the image table , which takes input parameter as "Table_name" and "Column_name" .This Procedure works fine . The requirement is to create the all image tables Parallely , we are using dbms_job.submit to achieve so but when parameterizing its not working , If hardcoded it works fine .

Kindly help . Below shared are the relevant details .

=============== Below is main proceudre used to trigger the parallel execution of the parameterized procedure 

CREATE OR REPLACE PROCEDURE PROC_IMG_BUILD_MASTER
AS
l_job NUMBER;
BEGIN
for o in (select TABLE_NAME,TABLE_PK
from MASTER_TABLE)
loop
dbms_job.submit(job =>l_job, what =>'PROC_IMG_BUILD( ' || o.TABLE_NAME || ',' || o.TABLE_PK || ' );');
end loop;
COMMIT;
COMMIT;
END;

===============================================================

create table master_table (table_name varchar2(100),table_pk varchar2(100))
create table ABC (ABC_ID varchar2(100))
create table XYZ (XYZ_ID varchar2(100))
create table I_ABC (ABC_ID varchar2(100))
create table I_XYZ (XYZ_ID varchar2(100))
insert into master_table VALUES('ABC','ABC_ID');
insert into master_table VALUES('XYZ','XYZ_ID');
COMMIT;

====================================== This procedure is called from the main procedure 

CREATE OR REPLACE PROCEDURE PROC_IMG_BUILD (TABLE_NAME VARCHAR2,TABLE_PK VARCHAR2) AS
STMT VARCHAR2(200);
TRUNC_STMT VARCHAR2(200);
BEGIN
TRUNC_STMT:='TRUNCATE TABLE I_'||TABLE_NAME;
EXECUTE IMMEDIATE TRUNC_STMT;
STMT:='INSERT INTO I_'||TABLE_NAME||' SELECT '||TABLE_PK||' FROM  '||TABLE_NAME||' ;
EXECUTE IMMEDIATE STMT;
COMMIT;
END;

========================================== --***** The below block using Submit works fine, when hardcoded parameters .

DECLARE
l_job NUMBER;
BEGIN
dbms_job.submit(job =>l_job,
what =>'PROC_IMG_BUILD( ''ABC'',''ABC_ID'');');
COMMIT; 
END;
/

==========================

Upvotes: 1

Views: 450

Answers (1)

Boneist
Boneist

Reputation: 23578

The issue is that you have neglected to include single quotes in your what parameter.

I.e. instead of the dbms_job.submit being passed 'PROC_IMG_BUILD( ''ABC'',''ABC_ID'');' as per your hardcoded version, it's being passed 'PROC_IMG_BUILD( ABC,ABC_ID);', hence the error you're getting.

Your procedure should look something like:

CREATE OR REPLACE PROCEDURE proc_img_build_master AS
  l_job NUMBER;
BEGIN
  FOR o IN (SELECT table_name,
             table_pk
        FROM   master_table)
  LOOP
    dbms_job.submit(job => l_job,
                    what => 'PROC_IMG_BUILD( ''' || o.table_name || ''',''' || o.table_pk || ''' );');
  END LOOP;

  COMMIT;
END proc_img_build_master;

Upvotes: 1

Related Questions