Need to find out what is wrong with this script?

Rao_S
Rao_S used Ask the Experts™
on
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 ;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
flow01IT-specialist

Commented:
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 dba
Top Expert 2009

Commented:
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

flow01IT-specialist

Commented:
Oops,  blind spot focussing on replacing the exec. Geert G is right, call the procedure directly.
Ensure you’re charging the right price for your IT

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

Most Valuable Expert 2012
Distinguished Expert 2018
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

Author

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?
Most Valuable Expert 2012
Distinguished Expert 2018

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-analyst

Commented:
Dbms_output can be used in SQL Developer.
Most Valuable Expert 2012
Distinguished Expert 2018

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.

Author

Commented:
Thank you All.
I was able to find documentation to use UTL_FILE.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial