Home » SQL & PL/SQL » SQL & PL/SQL » Oracle lookup function in loop returns rowtype how to get fields from rowtype (Oracle 12g)
| Oracle lookup function in loop returns rowtype how to get fields from rowtype [message #665205] |
Fri, 25 August 2017 11:19  |
 |
jokrasa
Messages: 14
Registered: March 2017
|
Junior Member |
|
|
I have a procedure that runs a select ( I tested that is good returns 56 records )
then when I run a curser I want to pass 3 fields to a function ( see above ) that will
lookup/select a record from a table that contains 15 million records ( 10 years worth ).
It returns a rowtype that I want to then extract the fields from this rowtype record to
run an insert with both the records from the 1st select and the additional fields acquired
from the lookup function.
If I run the procedure the console prints out my test msgs but when I try to run
select * bulk collect into v_tab_proc_claim_recs from v_processed_claim;
it doesn't compile due to Error(97,65): PL/SQL: ORA-00942: table or view does not exist
as if either of these are not Tables.
Am I doingthis right... how can I do it, why can't it see theTable I'm trying to extract to ?
Should I do this some other way..
Thanks for any help/suggestions 
The function is below....
create or replace function get_processed_claim_rec(
p_provider VARCHAR2,
p_rx VARCHAR2,
p_record_no NUMBER
)
return i_idb.processed_claim%rowtype
as
l_claim_record i_idb.processed_claim%rowtype;
begin
select * into l_claim_record from i_idb.processed_claim
where source_date = p_provider
AND rx = p_rx
AND rec_no = p_record_no;
return(l_claim_record);
end;
And the procedure is....
create or replace PROCEDURE import_mailer_data
AS
-------------------------------
/**
for the lookup table
**/
v_processed_claim i_idb.processed_claim%rowtype;
TYPE proc_claim_recs IS TABLE OF v_processed_claim%ROWTYPE INDEX BY PLS_INTEGER;
v_tab_proc_claim_recs proc_claim_recs;
--------------------------------
CURSOR myCursor
IS
SELECT *
from
(
SELECT
j.create_date as open_date,
case when (j.create_date < (sysdate - 20) )
then 'POD'
else 'REG'
end as priority,
c.division,
c.unit,
--p.refill as days_supply,
--p.din_name,
'CM_JOHN' as log_code,
c.first_name,
c.last_name,
--p.UNLISTED_compound,
--p.intervention_code,
--p.substitution,
--p.confirm,
c.PROVIDER,
c.rx,
c.DISPENSE_DATE,
c.DIN,
c.QTY,
c.DIN_COST_PAID,
c.DISP_FEE_PAID,
c.PAID_AMOUNT,
c.SOURCE_DATE,
c.RECORD_NO,
c.RELATIONSHIP,
c.INSURER_NO,
c.GROUP_NO,
c.CERTIFICATE,
c.BIRTH_DATE,
c.USER_ID,
--p.rej_code --v_seq_no
rank() over
(
partition by c.provider, c.rx, c.record_no Order by c.provider desc, c.rx desc
) as RNK
FROM AUDITCOLLECTIONS.MAILER_CLAIMS c,
AUDITCOLLECTIONS.MAILER_JOBS j
WHERE MAILER_JOB_DETAIL_ID IN
(SELECT MAILER_JOB_DETAIL_ID
FROM AUDITCOLLECTIONS.MAILER_JOB_DETAILS
WHERE MAILER_JOB_ID IN
( SELECT MAILER_JOB_ID FROM AUDITCOLLECTIONS.MAILER_JOBS
)
)
AND ( c.PROVIDER, c.rx, c.record_no ) NOT IN
( SELECT provider, rx, rec_no FROM AUDITCOLLECTIONS.COLLECTION_AUDIT_STAGING
)
AND j.create_date > (sysdate - 30)
AND c.provider = '2010500042'
) A_Latest
where A_Latest.RNK = 1;
BEGIN
v_report_id := audit_load.create_loaded_report(v_report_type_id);
FOR curRec IN myCursor
LOOP
BEGIN
dbms_output.put_line ('===>>>> PRINTING TEST1 = ');
v_processed_claim := get_processed_claim_rec(curRec.PROVIDER, curRec.RX, curRec.RECORD_NO);
select * bulk collect into v_tab_proc_claim_recs from v_processed_claim;
END LOOP;
audit_load.update_status_to_loaded(v_report_id);
END import_mailer_data;
|
|
|
|
| Oracle lookup function in loop returns rowtype how to get fields from rowtype [message #665206 is a reply to message #665205] |
Fri, 25 August 2017 11:19  |
|
jokrasa
Messages: 14
Registered: March 2017
|
Junior Member |
|
|
I have a procedure that runs a select ( I tested that is good returns 56 records )
then when I run a curser I want to pass 3 fields to a function ( see above ) that will
lookup/select a record from a table that contains 15 million records ( 10 years worth ).
It returns a rowtype that I want to then extract the fields from this rowtype record to
run an insert with both the records from the 1st select and the additional fields acquired
from the lookup function.
If I run the procedure the console prints out my test msgs but when I try to run
select * bulk collect into v_tab_proc_claim_recs from v_processed_claim;
it doesn't compile due to Error(97,65): PL/SQL: ORA-00942: table or view does not exist
as if either of these are not Tables.
Am I doingthis right... how can I do it, why can't it see theTable I'm trying to extract to ?
Should I do this some other way..
Thanks for any help/suggestions
The function is below....
create or replace function get_processed_claim_rec(
p_provider VARCHAR2,
p_rx VARCHAR2,
p_record_no NUMBER
)
return i_idb.processed_claim%rowtype
as
l_claim_record i_idb.processed_claim%rowtype;
begin
select * into l_claim_record from i_idb.processed_claim
where source_date = p_provider
AND rx = p_rx
AND rec_no = p_record_no;
return(l_claim_record);
end;
And the procedure is....
create or replace PROCEDURE import_mailer_data
AS
-------------------------------
/**
for the lookup table
**/
v_processed_claim i_idb.processed_claim%rowtype;
TYPE proc_claim_recs IS TABLE OF v_processed_claim%ROWTYPE INDEX BY PLS_INTEGER;
v_tab_proc_claim_recs proc_claim_recs;
--------------------------------
CURSOR myCursor
IS
SELECT *
from
(
SELECT
j.create_date as open_date,
case when (j.create_date < (sysdate - 20) )
then 'POD'
else 'REG'
end as priority,
c.division,
c.unit,
--p.refill as days_supply,
--p.din_name,
'CM_JOHN' as log_code,
c.first_name,
c.last_name,
--p.UNLISTED_compound,
--p.intervention_code,
--p.substitution,
--p.confirm,
c.PROVIDER,
c.rx,
c.DISPENSE_DATE,
c.DIN,
c.QTY,
c.DIN_COST_PAID,
c.DISP_FEE_PAID,
c.PAID_AMOUNT,
c.SOURCE_DATE,
c.RECORD_NO,
c.RELATIONSHIP,
c.INSURER_NO,
c.GROUP_NO,
c.CERTIFICATE,
c.BIRTH_DATE,
c.USER_ID,
--p.rej_code --v_seq_no
rank() over
(
partition by c.provider, c.rx, c.record_no Order by c.provider desc, c.rx desc
) as RNK
FROM AUDITCOLLECTIONS.MAILER_CLAIMS c,
AUDITCOLLECTIONS.MAILER_JOBS j
WHERE MAILER_JOB_DETAIL_ID IN
(SELECT MAILER_JOB_DETAIL_ID
FROM AUDITCOLLECTIONS.MAILER_JOB_DETAILS
WHERE MAILER_JOB_ID IN
( SELECT MAILER_JOB_ID FROM AUDITCOLLECTIONS.MAILER_JOBS
)
)
AND ( c.PROVIDER, c.rx, c.record_no ) NOT IN
( SELECT provider, rx, rec_no FROM AUDITCOLLECTIONS.COLLECTION_AUDIT_STAGING
)
AND j.create_date > (sysdate - 30)
AND c.provider = '2010500042'
) A_Latest
where A_Latest.RNK = 1;
BEGIN
v_report_id := audit_load.create_loaded_report(v_report_type_id);
FOR curRec IN myCursor
LOOP
BEGIN
dbms_output.put_line ('===>>>> PRINTING TEST1 = ');
v_processed_claim := get_processed_claim_rec(curRec.PROVIDER, curRec.RX, curRec.RECORD_NO);
select * bulk collect into v_tab_proc_claim_recs from v_processed_claim;
END LOOP;
audit_load.update_status_to_loaded(v_report_id);
END import_mailer_data;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 19:14:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
