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 Go to next message
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 Smile


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 Go to previous messageGo to next message
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 Smile


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; 
Re: Oracle lookup function in loop returns rowtype how to get fields from rowtype [message #665207 is a reply to message #665206] Fri, 25 August 2017 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 25742
Registered: January 2009
Location: SoCal
Senior Member
do NOT do in PL/SQL that which can be done in plain SQL.
Row by rows is slow by slow.

>ORA-00942: table or view does not exist
privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
direct GRANT may be required to avoid error above.
Re: Oracle lookup function in loop returns rowtype how to get fields from rowtype [message #665292 is a reply to message #665207] Tue, 29 August 2017 03:38 Go to previous message
cookiemonster
Messages: 12956
Registered: September 2008
Location: Rainy Manchester
Senior Member
v_processed_claim is a rowtype variable, not a table you can't treat it as a table, and even if you could it would only contain a single record so the bulk collect makes no sense.

Why don't you just modify the query in the procedure to join to processed_claim?
The function isn't helpful here.
Previous Topic: Error Linking to C DLL from PL/SQL
Next Topic: Visualize Processes Vertical
Goto Forum:
  


Current Time: Tue Dec 12 05:33:05 CST 2017

Total time taken to generate the page: 0.08408 seconds