Need to find out what is wrong with this script?

create or replace TYPE MGMT_JOB_GUID_ARRAY2 AS TABLE OF RAW(16);
DECLARE
   l_job_ids  MGMT_JOB_GUID_ARRAY2;
BEGIN  
   SELECT job_id  BULK COLLECT    INTO   l_job_ids
   FROM   mgmt_job WHERE job_name like 'EXECMD%';        
   FOR i IN 1 .. l_job_ids.COUNT
    LOOP
        exec mgmt_job_engine.stop_all_executions_with_id('l_job_ids(i)','TRUE')
    END LOOP;
END ;
Rao_SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
The 'exec'  is not pl/sql.  You can use execute immediate  to get the pl/sql block compiled
Try
DECLARE
   l_job_ids  MGMT_JOB_GUID_ARRAY2;
   v_statement varchar2(32767);
BEGIN  
   v_statement := q'{mgmt_job_engine.stop_all_executions_with_id(:job_id,'TRUE')}';  
   -- use placeholder for job_id to prevent unneccessary parsing
   SELECT job_id  BULK COLLECT    INTO   l_job_ids
   FROM   mgmt_job WHERE job_name like 'EXECMD%';        
   FOR i IN 1 .. l_job_ids.COUNT
    LOOP
        -- dbms_output.put_line (v_statement);
        execute immediate v_statement using l_job_ids(i);  -- the using  passes the argument to the first  placeholder
    END LOOP;
END ;
Geert GOracle dbaCommented:
just call without exec ...
why in execute immediate ?

LOOP
  mgmt_job_engine.stop_all_executions_with_id(l_job_ids(i),TRUE)
END LOOP;

Open in new window

flow01Commented:
Oops,  blind spot focussing on replacing the exec. Geert G is right, call the procedure directly.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
To also help answer the question of what is wrong:
stop_all_executions_with_id('l_job_ids(i)','TRUE')

You made l_job_ids(i) a string using single quotes.  It is not longer a variable.

Back to the procedure, I wouldn't use BULK COLLECT and an in-memory table.  It is unecessary.

This is untested but I would use an explicit loop:
DECLARE
	v_statement varchar2(32767);
BEGIN  
   v_statement := q'{mgmt_job_engine.stop_all_executions_with_id(:job_id,'TRUE')}';  
   -- use placeholder for job_id to prevent unneccessary parsing
	for i in ( SELECT job_id FROM  mgmt_job WHERE job_name like 'EXECMD%') loop
		execute immediate v_statement using i.job_id;
    end loop;
end; 
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rao_SAuthor Commented:
Thank you all very much.
I would like to capture the job_ids I am deleting into a file, can you suggest the best method to do so?
slightwv (䄆 Netminder) Commented:
I use sqlplus to run my scripts so I can use DBMS_OUTUT.PUT_LINE and SPOOL.  I believe SQL Developer will honor the spool command.

If you cannot use DBMS_OUTPUT then look into UTL_FILE and write it to a file on the database server.
Helena Markováprogrammer-analystCommented:
Dbms_output can be used in SQL Developer.
slightwv (䄆 Netminder) Commented:
>>Dbms_output can be used in SQL Developer.

I know that can be.  You just need to make sure it is enabled and the dbms_output window is open.

I wasn't sure how it handled spool.
Rao_SAuthor Commented:
Thank you All.
I was able to find documentation to use UTL_FILE.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.