1
Need to convert this PL/SQL script to Dynamic SQL Method 2
* copybook - celg02u3.sql SIR 24265 *
* updates dt_deny for all rows in *
* the removal_eligibility_link table for all persons *
* in all stages associated with the victim who has *
* has had a specific legal status change *
EXEC SQL EXECUTE
BEGIN
/* This cursor returns all removal events associated
with a specific list of legal status codes for
victim where eligibility has not already been denied */
:PLSQL-PROCEDURE-NAME :='CELG01U3';
:PLSQL-BLOCK-NUMBER :='Finding legal status rows';
DECLARE CURSOR DENY_LEGAL IS
Select
/*+ INDEX (rem IND_REMOVAL_ELIGIBILITY_LINK_4)
INDEX (ls IND_LEGAL_STATUS_1) */
distinct pe.id_pers_elig_person, ls.id_case,
ls.dt_legal_stat_status_dt, id_removal_event,
pe.dt_pers_elig_start
from person_eligibility pe, legal_status ls,
removal_eligibility_link rem
where pe.id_pers_elig = rem.id_pers_elig
and pe.id_pers_elig_person = ls.id_person
and rem.dt_deny is null
and rem.ind_pers_removed = 'Y'
and ls.cd_legal_stat_status in ('030', '040',
'050', '070', '100', '120', '130', '150')
and ((ls.dt_legal_stat_status_dt > pe.dt_pers_elig_start
or
ls.dt_legal_stat_status_dt = pe.dt_pers_elig_start)
and ls.dt_legal_stat_status_dt < pe.dt_pers_elig_end);
v_deny_cntr number:=0;
v_commit_cntr number:=0;
BEGIN
FOR dl IN DENY_LEGAL
LOOP
UPDATE REMOVAL_ELIGIBILITY_LINK REL
SET REL.DT_DENY = dl.dt_legal_stat_status_dt
WHERE REL.ID_REMOVAL_EVENT = dl.id_removal_event;
DECLARE CURSOR ELIG_PERSON IS
select DISTINCT ID_PERS_ELIG /*added distinct*/
from REMOVAL_ELIGIBILITY_LINK
where ID_REMOVAL_EVENT = dl.id_removal_event;
BEGIN
FOR i IN ELIG_PERSON
LOOP
BEGIN
UPDATE PERSON_ELIGIBILITY
SET DT_DENY = dl.dt_legal_stat_status_dt
WHERE ID_PERS_ELIG = i.id_pers_elig
AND
((dl.dt_legal_stat_status_dt > DT_PERS_ELIG_START
or
dl.dt_legal_stat_status_dt = DT_PERS_ELIG_START)
AND
dl.dt_legal_stat_status_dt < DT_PERS_ELIG_END)
AND
(DT_DENY is Null
or DT_DENY > dl.dt_legal_stat_status_dt);
v_deny_cntr:=v_deny_cntr+SQL%ROWCOUNT;
END;
END LOOP;
END;
v_commit_cntr:=v_commit_cntr+1;
IF v_commit_cntr > 50000 THEN
COMMIT;
v_commit_cntr := 0;
END IF;
END LOOP;
:CELG02U-DENIED :=v_deny_cntr;
END;
EXCEPTION
WHEN OTHERS THEN
:PLSQL-SQL-ERROR-NUMBER :=SQLCODE;
:PLSQL-SQL-ERROR-MESSAGE :=SQLERRM;
:PLSQL-FATAL-ERROR :='Y';
ROLLBACK;
END;
END-EXEC.
If someone can do for me as an example I can do the others I need to do.
More Details:
I need this .sql file to be Dynamic Method SQL Method 2 per Oracle's instructions "Use a Dynamic Method 2 to execute the PL/SQL block. For information on dynamic method 2 please see the Pro*Cobol Precompiler manual."
Here is error when running a .sh that uses these .sql:
Business Case:
this is a known bug in the oracle precompiler for 10g. this problem affects only celg02b cause it is the only program that uses hints in a plsql compiled into a batch program. It may need to be re-written to use dynamic method 2 or use a different precompiler.
PCB-S-00576, PLS-103: Encountered the symbol "INDEX" when expecting one of the following:
( * - + all case mod new null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current distinct max min prior sql stddev sum
unique variance execute the forall merge time timestamp
interval date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quoted SQL string>
celg02u3.sql -
/*+ INDEX (rem IND_REMOVAL_ELIGIBILITY_LINK_4) INDEX (ls IND_LEGAL_STATUS_1) */
celg02i2.sql -
/*+ INDEX (CR IND_CNSRVTRSHP_REMOVAL_2) */
celg02u2.sql -
I believe this describes the problem in detail. We are upgrading to Oracle 10g client and started to experience this problem.
