0

I have created below procedure. I am getting ORA:22905-can not access rows from a non-nested table item at last line : INTO JSON_DATA FROM TABLE(InvoiceDetails). What I am doing here is retrieving out put from multiple queries dynamically through ref cursors and then passing the output to JSON.

PROCEDURE Calculate_Fields_Proc (p_invoice_number   IN     NUMBER,
                                     p_region           IN     VARCHAR2, --AMER/APJS/EMEA
                                     p_record_type      IN     VARCHAR2, --Invoice/Credit memo
                                     p_invoice_format   IN     VARCHAR2,
                                     p_o_json_data         OUT CLOB)
    IS
        --TYPE InvoiceDetails IS REF CURSOR;
        InvoiceDetails  SYS_REFCURSOR;
        --InvoiceDetails  InvoiceDetails;
        l_asset_tax_category_count   NUMBER;
        l_category_name              VARCHAR2 (50);
        l_cat_charge_total           NUMBER;
        l_cat_tax_total              NUMBER;
        l_cat_invoice_total          NUMBER;
        l_misc_charge_total          NUMBER;
        l_misc_tax_total             NUMBER;
        l_misc_invoice_total         NUMBER;
        l_inv_charge_total           NUMBER;
        l_inv_tax_total              NUMBER;
        l_inv_total                  NUMBER;
        l_cat_principal_total        NUMBER;
        l_cat_interest_total         NUMBER;
        JSON_ARR            JSON_ARRAY_T := JSON_ARRAY_T ();
        JSON_DATA                    CLOB;
    BEGIN
        OPEN InvoiceDetails FOR
               'SELECT COUNT (DISTINCT asset_tax_category) FROM ile_ar_charg_stage_trx chrg
                WHERE chrg.invoice_seq_id > '
                || p_invoice_number;

    LOOP
        FETCH InvoiceDetails INTO l_asset_tax_category_count;

        EXIT WHEN InvoiceDetails%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE (
            'l_asset_tax_category_count: ' || l_asset_tax_category_count);
    END LOOP;

    OPEN InvoiceDetails FOR
           'SELECT asset_tax_category                     rec_category_name,
     SUM (charge_amount)                    Rec_cat_charge_total,
     SUM (
           NVL (tax_only_column, 0)
         + NVL (pst_tax_only_column, 0)
         + NVL (qst_tax_only_column, 0)
         + NVL (gst_tax_only_column, 0)
         + NVL (hst_tax_only_column, 0))    Rec_cat_tax_total,
     SUM (charge_total)                     Rec_cat_invoice_total
    FROM ile_ar_charg_stage_trx chrg
   WHERE chrg.invoice_seq_id = '
            || p_invoice_number
            || 'AND chrg.charge_type = ''0001''
   GROUP BY asset_tax_category';


    LOOP
        FETCH InvoiceDetails
            INTO l_category_name,
                 l_cat_charge_total,
                 l_cat_tax_total,
                 l_cat_invoice_total;

        EXIT WHEN InvoiceDetails%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE (
               l_category_name
            || ''
            || l_cat_charge_total
            || ''
            || l_cat_tax_total
            || ''
            || l_cat_invoice_total);
    END LOOP;

    OPEN InvoiceDetails FOR
           'SELECT SUM (charge_amount)                    misc_charge_total,
     SUM (
           NVL (tax_only_column, 0)
         + NVL (pst_tax_only_column, 0)
         + NVL (qst_tax_only_column, 0)
         + NVL (gst_tax_only_column, 0)
         + NVL (hst_tax_only_column, 0))    misc_tax_total,
     SUM (charge_total)                     misc_invoice_total
    FROM ile_ar_charg_stage_trx chrg
   WHERE chrg.invoice_seq_id = '
            || p_invoice_number
            || 'AND chrg.charge_type <> ''0001''
   GROUP BY asset_tax_category';


    LOOP
        FETCH InvoiceDetails
            INTO l_misc_charge_total,
                 l_misc_tax_total,
                 l_misc_invoice_total;

        EXIT WHEN InvoiceDetails%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE (
               l_misc_charge_total
            || ''
            || l_misc_tax_total
            || ''
            || l_misc_invoice_total);
    END LOOP;


    OPEN InvoiceDetails FOR
        'SELECT SUM (charge_amount)                    INV_CHARGE_TOTAL,
   SUM (
         NVL (tax_only_column, 0)
       + NVL (pst_tax_only_column, 0)
       + NVL (qst_tax_only_column, 0)
       + NVL (gst_tax_only_column, 0)
       + NVL (hst_tax_only_column, 0))    INV_TAX_TOTAL,
   SUM (charge_total)                     INV_TOTAL
  FROM ile_ar_charg_stage_trx chrg
 WHERE chrg.invoice_seq_id =' || p_invoice_number;


    LOOP
        FETCH InvoiceDetails
            INTO l_inv_charge_total, l_inv_tax_total, l_inv_total;

        EXIT WHEN InvoiceDetails%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE (
               l_inv_charge_total
            || ''
            || l_inv_tax_total
            || ''
            || l_inv_total);
    END LOOP;

    OPEN InvoiceDetails FOR
           'SELECT SUM (NVL (chrg.UD_CHARGE_PRINCIPAL_AMT, 0))
                   Rec_cat_principal_total,
               SUM (NVL (chrg.UD_CHARGE_INTEREST_AMT, 0))
                   Rec_cat_interest_total
          FROM ile_ar_charg_stage_trx chrg
         WHERE chrg.invoice_seq_id ='
                    || p_invoice_number
                    || 'AND charge_type = ''0001''';


    LOOP
        FETCH InvoiceDetails INTO l_cat_principal_total, l_cat_interest_total;


        EXIT WHEN InvoiceDetails%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE (
            l_cat_principal_total || '' || l_cat_interest_total);
    END LOOP;
    
       BEGIN
          SELECT JSON_ARRAYAGG(JSON_OBJECT(*))
          INTO JSON_DATA FROM TABLE(InvoiceDetails);  -- **getting error here**
           
          DBMS_OUTPUT.PUT_LINE (JSON_DATA);
       END;

    CLOSE InvoiceDetails;
END;
1
  • A REF CURSOR isn't a collection object, so you can't use the TABLE operator on it or treat it like a row source. It's simple a cursor (SQL statement), that is, a program. It can only be executed by opening and fetching from it as you are rightly doing elsewhere in your procedure.
    – Paul W
    Commented Oct 10, 2023 at 13:17

1 Answer 1

0

Why are you using ref cursors and dynamic SQL?

Looking through your SQL statements, there is no need for dynamic SQL at all - not to mention you're converting from bind variables to concatenating a literal value, and leaving yourself wide open to SQL injection to boot! Also, you can use conditional aggregation to combine some of the queries, meaning you only need 3 queries after all.

Finally, you can avoid all the looping and output the JSON by taking each query, converting the output into JSON and joining them together.

Thus (and I had to guess at how you wanted your output), your function could be rewritten more efficiently and easier to debug like so:

CREATE OR REPLACE PROCEDURE calculate_fields_proc(p_invoice_number IN NUMBER,
                                                  p_region         IN VARCHAR2, --AMER/APJS/EMEA
                                                  p_record_type    IN VARCHAR2, --Invoice/Credit memo
                                                  p_invoice_format IN VARCHAR2,
                                                  p_o_json_data    OUT CLOB) IS
BEGIN

  SELECT json_serialize(json_object(key 'asset_tax_category_cnt' VALUE qry1.asset_tax_category_cnt,
                                    key 'output_per_category' VALUE qry2.output_per_category,
                                    key 'totals' VALUE qry3.totals) RETURNING CLOB pretty) RESULT
  INTO   p_o_json_data
  FROM   (SELECT json_arrayagg(json_object(*)) asset_tax_category_cnt
          FROM   (SELECT COUNT(DISTINCT asset_tax_category) asset_tax_category_cnt
                  FROM   ile_ar_charg_stage_trx chrg
                  WHERE  chrg.invoice_seq_id > p_invoice_number)) qry1
  CROSS  JOIN (SELECT json_arrayagg(json_object(*)) output_per_category
               FROM   (SELECT asset_tax_category rec_category_name,
                              SUM(CASE
                                    WHEN chrg.charg_type = '0001' THEN
                                     charge_amount
                                  END) rec_cat_charge_total,
                              SUM(CASE
                                    WHEN chrg.charg_type = '0001' THEN
                                     NVL(tax_only_column, 0) + NVL(pst_tax_only_column, 0) + NVL(qst_tax_only_column, 0) + NVL(gst_tax_only_column, 0) +
                                     NVL(hst_tax_only_column, 0)
                                  END) rec_cat_tax_total,
                              SUM(CASE
                                    WHEN chrg.charg_type = '0001' THEN
                                     charge_total
                                  END) rec_cat_invoice_total,
                              SUM(CASE
                                    WHEN chrg.charg_type != '0001' THEN
                                     charge_amount
                                  END) misc_charge_total,
                              SUM(CASE
                                    WHEN chrg.charg_type != '0001' THEN
                                     NVL(tax_only_column, 0) + NVL(pst_tax_only_column, 0) + NVL(qst_tax_only_column, 0) + NVL(gst_tax_only_column, 0) +
                                     NVL(hst_tax_only_column, 0)
                                  END) misc_tax_total,
                              SUM(CASE
                                    WHEN chrg.charg_type != '0001' THEN
                                     charge_total
                                  END) misc_invoice_total
                       FROM   ile_ar_charg_stage_trx chrg
                       WHERE  chrg.invoice_seq_id = p_invoice_number
                       GROUP  BY asset_tax_category)) qry2
  CROSS  JOIN (SELECT json_arrayagg(json_object(*)) totals
               FROM   (SELECT SUM(charge_amount) inv_charge_total,
                              SUM(NVL(tax_only_column, 0) + NVL(pst_tax_only_column, 0) + NVL(qst_tax_only_column, 0) + NVL(gst_tax_only_column, 0) +
                                  NVL(hst_tax_only_column, 0)) inv_tax_total,
                              SUM(charge_total) inv_total,
                              SUM(CASE
                                    WHEN chrg.charg_type = '0001' THEN
                                     chrg.ud_charge_principal_amt
                                  END) rec_cat_principal_total,
                              SUM(CASE
                                    WHEN chrg.charg_type = '0001' THEN
                                     chrg.ud_charge_interest_amt
                                  END) rec_cat_interest_total
                       FROM   ile_ar_charg_stage_trx chrg
                       WHERE  chrg.invoice_seq_id = p_invoice_number)) qry3;
END calculate_fields_proc;
/

See this db<>fiddle for an example of it working.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.