9 Replies Latest reply: Apr 16, 2012 3:17 PM by 930814 RSS

    PLS-00330: invalid use of type name or subtype name

    930814
      I am relatively new to Sql and am in the process of learning, so please bear with me. I am trying to create a trigger for the Invoices table that displays the vendor_name, invoice_number, and payment_total after the payment_total has been increased. I have discovered that I must use a compound trigger due to a mutating-table error and ended up with this:
      CREATE OR REPLACE TRIGGER invoices_after_update_payment
      FOR UPDATE OF payment_total
      ON invoices
      COMPOUND TRIGGER
        TYPE invoice_numbers_table      IS TABLE OF VARCHAR2(50);
        TYPE payment_totals_table       IS TABLE OF NUMBER;
        TYPE vendor_names_table         IS TABLE OF VARCHAR2(50);
        TYPE summary_payments_table     IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
        TYPE summary_names_table        IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
        
        invoice_numbers                 invoice_numbers_table;
        payment_totals                  payment_totals_table;
        vendor_names                    vendor_names_table;
        payment_summarys                summary_payments_table;
        name_summarys                   summary_names_table;
      
        AFTER STATEMENT IS
          invoice_number VARCHAR2(50);
          payment_total NUMBER;
          vendor_name VARCHAR2(50);
        BEGIN
          SELECT i.invoice_number, i.payment_total, v.vendor_name
          BULK COLLECT INTO invoice_numbers, payment_totals, vendor_names
          FROM invoices i JOIN vendors v
            ON i.vendor_id = v.vendor_id
          GROUP BY i.invoice_number;
          
          FOR i IN 1..invoice_numbers.COUNT() LOOP
            invoice_number := invoice_numbers(i);
            payment_total := payment_totals(i);
            vendor_name := vendor_names(i);
            summary_payments_table(invoice_number) := payment_total;
            summary_names_table(invoice_number) := vendor_name;
          END LOOP;
        END AFTER STATEMENT;
        
        AFTER EACH ROW IS
          temp_payment_total NUMBER;
          vendor_name VARCHAR2(50);
        BEGIN
          temp_payment_total := payment_summarys(:new.invoice_number);
          vendor_name := name_summarys(:new.invoice_number);
          IF (:new.payment_total > temp_payment_total) THEN
            DBMS_OUTPUT.PUT_LINE('Vendor Name: ' || vendor_name || ', Invoice Number: ' || :new.invoice_number || ', Payment Total: ' || :new.payment_total);
          END IF;
        END AFTER EACH ROW;
      END;
      /
      The code that I am using to update the table is:
      UPDATE invoices
      SET payment_total = 508
      WHERE invoice_number = 'QP58872'
      At this point, I am getting an error report saying:
      29/7           PLS-00330: invalid use of type name or subtype name
      29/7           PL/SQL: Statement ignored
      30/7           PLS-00330: invalid use of type name or subtype name
      30/7           PL/SQL: Statement ignored
      What does the error code entail? I have looked it up but can't seem to pin it. Any help would be greatly appreciated and I am open to any suggestions for improving my current code.

      I am using Oracle Database 11g Express Edition on Windows 7. I am not sure if it is relevant, but I am also using Sql Developer. If you need any further information, I will do my best to provide what I can.

      Thanks!

      Edited by: 927811 on Apr 15, 2012 11:54 PM

      Edited by: 927811 on Apr 15, 2012 11:56 PM
        • 1. Re: PLS-00330: invalid use of type name or subtype name
          908002
          First of all
          SELECT i.invoice_number, i.payment_total, v.vendor_name
          BULK COLLECT INTO invoice_numbers, payment_totals, vendor_names
          FROM invoices i JOIN vendors v
          ON i.vendor_id = v.vendor_id
          GROUP BY i.invoice_number;

          This query should be changed, either inlude group by i.invoice_number, i.payment_total, v.vendor_name or use aggregate functions.


          summary_payments_table(invoice_number) := payment_total;
          summary_names_table(invoice_number) := vendor_name;

          This should be in IMO

          payment_summarys(invoice_number) := payment_total; -- not sure logic behind this?
          name_summarys(invoice_number):= vneodr_name ; -- assinging varchar array t numbe array??
          • 2. Re: PLS-00330: invalid use of type name or subtype name
            930815
            Hi,

            This error is usually seen when you use some Oracle standard keywords/constants in place of variables. e.g.,

            if date_of_birth > NUMBER then ...

            This will throw the error that you have got since, NUMBER is a keyword in Oracle.

            In your case, it looks like the following line has got the problem ( not sure though ) :

            FOR i IN 1..invoice_numbers.COUNT() LOOP

            COUNT being a standard keyword in Oracle.
            • 3. Re: PLS-00330: invalid use of type name or subtype name
              930814
              Haha As I said, I am in the midst of learning. My reasoning for the two arrays wasn't logical at all, really. I only did that because the only example in the book I am using that was similar to what I was trying to accomplish did the same. I honestly thought it was overly complex for such a simple task, but assumed the author knew better than I.

              I will test your suggestions. Thanks for the help.
              • 4. Re: PLS-00330: invalid use of type name or subtype name
                930814
                I want to go ahead and thank you for your help so far, because after making your suggested changes the trigger compiled. However, I am now getting an error report when I try to update the table.
                Error starting at line 1 in command:
                UPDATE invoices
                SET payment_total = 508
                WHERE invoice_number = 'QP58872'
                Error report:
                SQL Error: ORA-01403: no data found
                ORA-06512: at "AP.INVOICES_AFTER_UPDATE_PAYMENT", line 38
                ORA-04088: error during execution of trigger 'AP.INVOICES_AFTER_UPDATE_PAYMENT'
                01403. 00000 -  "no data found"
                *Cause:    
                *Action:
                So I then updated my update statement as:
                BEGIN
                  UPDATE invoices
                  SET payment_total = 508
                  WHERE invoice_number = 'QP58872';
                EXCEPTION
                WHEN no_data_found THEN
                  DBMS_OUTPUT.PUT_LINE('No data found.');
                END;
                This allowed it to be ran. However, I am not getting any feed back from it even though the payment_total is in fact increasing. Because I have no errors, I am completely unsure of what is wrong.
                • 5. Re: PLS-00330: invalid use of type name or subtype name
                  omaha66
                  >
                  BEGIN
                  UPDATE invoices
                  SET payment_total = 508
                  WHERE invoice_number = 'QP58872';
                  EXCEPTION
                  WHEN no_data_found THEN
                  DBMS_OUTPUT.PUT_LINE('No data found.');
                  END;
                  All this exception handler did was catch the exception and cause it to not raise the exception. So, instead of seeing the error, you are not seeing the error, but the error still occurred. This is poor exception handling, I know you're learning and that is definitely an important topic to address as you learn PL/SQL.

                  You don't say what environment you are working in but dbms_output may not be enabled in your environment, which would cause you to not see output. How you would fix that is dependent on your tool (SQL*Plus, SQL Developer, etc. )

                  I've never tried to display anything from inside a trigger, so can't guarantee that you'd see dbms_output results even with it turned on.

                  If you're not already using them, Steven Feuerstein's PL/SQL books are great for learning PL/SQL.

                  Good luck!
                  • 6. Re: PLS-00330: invalid use of type name or subtype name
                    Peter Gjelstrup
                    Hi,

                    This is always a bad idea:
                    EXCEPTION
                    WHEN no_data_found THEN
                      DBMS_OUTPUT.PUT_LINE('No data found.');
                    END;
                    You gain nothing, you are not really handling the exception, only confusing yourself - You might not see it if serveroutput is not in.


                    This is much better, it even tells you where the exception occured:
                    ORA-06512: at "AP.INVOICES_AFTER_UPDATE_PAYMENT", line 38
                    ORA-04088: error during execution of trigger 'AP.INVOICES_AFTER_UPDATE_PAYMENT'
                    I think you changed your code, but my guess is that line 38 is this:
                    temp_payment_total := payment_summarys(:new.invoice_number);
                    I am not totally into compound triggers, but I think your timing points are messed up (?)

                    You fill the summary_payments_table in the AFTER STATEMENT section, and consumes it in AFTER EACH ROW section.

                    But don't they "fire" as:
                    BEFORE STATEMENT
                    BEFORE EACH ROW
                    AFTER EACH ROW
                    AFTER STATEMENT

                    Regards
                    Peter
                    • 7. Re: PLS-00330: invalid use of type name or subtype name
                      930814
                      I took your advice and removed the exception handling. There is no point in it being there. I also checked the timing points and you are correct. So, I changed my AFTER STATEMENT to BEFORE STATEMENT, which I had been thinking about doing anyways. It just seemed logical to me. That brings me to where I am now. I ran my update again and got back this error, It is the same as the one before, but for a different line (?)
                      Error starting at line 1 in command:
                      UPDATE invoices
                      SET payment_total = 510
                      WHERE invoice_number = 'QP58872'
                      Error report:
                      SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
                      ORA-06512: at "AP.INVOICES_AFTER_UPDATE_PAYMENT", line 30
                      ORA-04088: error during execution of trigger 'AP.INVOICES_AFTER_UPDATE_PAYMENT'
                      06502. 00000 -  "PL/SQL: numeric or value error%s"
                      *Cause:    
                      *Action:
                      Also, to make sure you are clear as to what my code now looks like:
                      SET SERVEROUTPUT ON;
                      CREATE OR REPLACE TRIGGER invoices_after_update_payment
                      FOR UPDATE OF payment_total
                      ON invoices
                      COMPOUND TRIGGER
                        TYPE invoice_numbers_table      IS TABLE OF VARCHAR2(50);
                        TYPE payment_totals_table       IS TABLE OF NUMBER;
                        TYPE vendor_names_table         IS TABLE OF VARCHAR2(50);
                        TYPE summary_payments_table     IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
                        TYPE summary_names_table        IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
                        
                        invoice_numbers                 invoice_numbers_table;
                        payment_totals                  payment_totals_table;
                        vendor_names                    vendor_names_table;
                        payment_summarys                summary_payments_table;
                        name_summarys                   summary_names_table;
                        
                        BEFORE STATEMENT IS
                          invoice_number VARCHAR2(50);
                          payment_total NUMBER;
                          vendor_name VARCHAR2(50);
                        BEGIN
                          SELECT i.invoice_number, i.payment_total, v.vendor_name
                          BULK COLLECT INTO invoice_numbers, payment_totals, vendor_names
                          FROM invoices i JOIN vendors v
                            ON i.vendor_id = v.vendor_id
                          GROUP BY i.invoice_number, i.payment_total, v.vendor_name;
                          
                          FOR i IN 1..invoice_numbers.COUNT() LOOP
                            invoice_number := invoice_numbers(i);
                            payment_total := payment_totals(i);
                            vendor_name := vendor_names(i);
                            payment_summarys(invoice_number) := payment_total;
                            name_summarys(invoice_number) := vendor_name;
                          END LOOP;
                        END BEFORE STATEMENT;
                        
                        AFTER EACH ROW IS
                          temp_payment_total NUMBER;
                          vendor_name VARCHAR2(50);
                        BEGIN
                          temp_payment_total := payment_summarys(:new.invoice_number);
                          vendor_name := name_summarys(:new.invoice_number);
                          IF (:new.payment_total > temp_payment_total) THEN
                            DBMS_OUTPUT.PUT_LINE('Vendor Name: ' || vendor_name || ', Invoice Number: ' || :new.invoice_number || ', Payment Total: ' || :new.payment_total);
                          END IF;
                        END AFTER EACH ROW;
                      END;
                      /
                      Thanks for the help!
                      • 8. Re: PLS-00330: invalid use of type name or subtype name
                        omaha66
                        927811 wrote:
                        I took your advice and removed the exception handling. There is no point in it being there. I also checked the timing points and you are correct. So, I changed my AFTER STATEMENT to BEFORE STATEMENT, which I had been thinking about doing anyways. It just seemed logical to me. That brings me to where I am now. I ran my update again and got back this error, It is the same as the one before, but for a different line (?)
                        Error starting at line 1 in command:
                        UPDATE invoices
                        SET payment_total = 510
                        WHERE invoice_number = 'QP58872'
                        Error report:
                        SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
                        ORA-06512: at "AP.INVOICES_AFTER_UPDATE_PAYMENT", line 30
                        ORA-04088: error during execution of trigger 'AP.INVOICES_AFTER_UPDATE_PAYMENT'
                        06502. 00000 -  "PL/SQL: numeric or value error%s"
                        *Cause:    
                        *Action:
                        Without your table definitions it is hard to pick out the error, but the gist of the message is "You have a character value that you are trying to convert into a numeric value" - ie. you have the string "ABC" and are trying to store it into a numeric variable. Compare your column definitions in your table with the way you have your variables defined.
                        TYPE invoice_numbers_table IS TABLE OF VARCHAR2(50);
                        TYPE payment_totals_table IS TABLE OF NUMBER;
                        TYPE vendor_names_table IS TABLE OF VARCHAR2(50);
                        TYPE summary_payments_table IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
                        TYPE summary_names_table IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
                        Here's a hint - you can use your table and column to declare the datatype:
                        TYPE invoice_numbers_table IS TABLE OF mytablename.mycolumn%TYPE;   (substitute your table and column names)
                        Post your table definitions and some sample data and we'll be able to be of more help.
                        • 9. Re: PLS-00330: invalid use of type name or subtype name
                          930814
                          Okay, that hint is kind of awesome. In fact, it fixed my issue all together. Thank you for that. :) In fact, it fixed my issue all together.

                          Thank you everyone for helping me out. In the future, I hope to be able to return the favor to the community.