cursor calling a procedure problem

[kmuller]

My questions is reletively simple. I have a cursor that fetches some data. After each fetch it calls a procedure, and passes the selected data to it. Everything runs fine when I run it from SQL PLUS. How do I execute this in the Unix environment? Would I have a scheduling tool kick off the cursor (which is saved as an .sql)? I think I am overthinking the problem and confusing myself. Thanks for the help.

 

[carp]

Yes, you could set it up as a chron job that calls a script that logs into SQLPlus and executes the script. However, there is an inherent security problem there.

On the other hand, if you add enough code to turn this into a procedure (and that wouldn't take much!), you could then execute it from within the database by using dbms_job.

 

[kmuller]

Thanks for the answer. What is the best way to add the cursor into the stored procedure? Do you just add the cursor after the BEGIN in the stored procedure???

CREATE OR REPLACE PROCEDURE KEVIN
....

BEGIN


DECLARE

v_part_no bp_kev_test.part_no%TYPE;
v_class bp_kev_test.class%TYPE;
v_part_desc bp_kev_test.part_desc%TYPE;
v_add_date bp_kev_test.add_date%TYPE;

CURSOR kev_cursor IS
SELECT part_no, class, part_desc, add_date
from kevtest;

BEGIN

OPEN kev_cursor;

LOOP

FETCH kev_cursor into v_part_no, v_class, v_part_desc, v_add_date;

link_test(v_part_no, v_class, v_part_desc, v_add_date);

EXIT WHEN kev_cursor%NOTFOUND;

END LOOP;

CLOSE kev_cursor;

END;

 

[carp]

Almost.



CREATE OR REPLACE PROCEDURE KEVIN AS
v_part_no bp_kev_test.part_no%TYPE;
v_class bp_kev_test.class%TYPE;
v_part_desc bp_kev_test.part_desc%TYPE;
v_add_date bp_kev_test.add_date%TYPE;

CURSOR kev_cursor IS
SELECT part_no, class, part_desc, add_date
from kevtest;
BEGIN

OPEN kev_cursor;
LOOP
FETCH kev_cursor into v_part_no, v_class, v_part_desc, v_add_date;
link_test(v_part_no, v_class, v_part_desc, v_add_date);
EXIT WHEN kev_cursor%NOTFOUND;
END LOOP;
CLOSE kev_cursor;

END;