2 Replies Latest reply: Apr 27, 2013 4:08 AM by leckj RSS

    update_member_attribute Character to number conversion error

    leckj
      Hi everyone,

      I am really new to APEX, so bear with me. I am looking at how to process collections.

      I have a table created as

      CREATE TABLE "TEST_TABLE"
      (     "SUBJECTCODE" NUMBER(4,0) NOT NULL ENABLE,
           "OPTIONCODE" VARCHAR2(2),
           "SUBJECTDESC" VARCHAR2(30)
      )
      /

      Entered some values...

      Subjectcode Optioncode Subjectdesc
      1 A SUB1
      2 B SUB2
      3 AC SUB3


      I have a button that runs this code:
      {
      DECLARE

      v_member_contents VARCHAR2(200);
      v_rowno NUMBER(1);

      BEGIN


      apex_collection.create_or_truncate_collection('COLL_TEST');

      for subject in (select * from TEST_TABLE )
      loop
      apex_collection.add_member
      (
      p_collection_name => 'COLL_TEST',
      p_n001 => subject.subjectcode,
      p_c001 => subject.subjectdesc,
      p_c002 => subject.optioncode
      );

      end loop;

      --Updating first member of collection,setting c002 to 'X'

      for i in 1..1

      loop

      v_rowno := 1;

      APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
      p_collection_name => 'COLL_TEST',
      p_seq => 1,
      p_attr_number => 2,
      p_number_value => 'X');

      end loop;


      END;

      }

      I am trying to update the first member of the collection and set c002 to 'X'.

      But the UPDATE_MEMBER_ATTRIBUTE always throws up 0RA-06502 PL/SQL Numeric or value error
      Character to Number conversion error.

      I thought that since I am passing a varchar2 to p_number_value ('X') and 2 to p_attr_number,
      it would update the second character column i.e. c002 which is created from a varchar2 column.
      Obviously that's not how it works.
      Please explain what's wrong.

      Thanks.

      Leckraj
        • 1. Re: update_member_attribute Character to number conversion error
          Denes Kubicek
          It seems you mixed the things up:

          "I am trying to update the first member of the collection and set c002 to 'X'."

          c002 is a character column. The value is 'X'. In that case the code would be:
          BEGIN
             FOR i IN 1 .. 1
             LOOP
                v_rowno := 1;
                apex_collection.update_member_attribute
                                                     (p_collection_name      => 'COLL_TEST',
                                                      p_seq                  => 1,
                                                      p_attr_number          => 2,
                                                      p_attr_value           => 'X'
                                                     );
             END LOOP;
          END;
          If your intention is to update the numeric column n001 (you have only one number column used and the parameter p_attr_number => 2 like in your statement, doesn't make sense), then your code has to be:

          BEGIN
             FOR i IN 1 .. 1
             LOOP
                v_rowno := 1;
                apex_collection.update_member_attribute
                                                     (p_collection_name      => 'COLL_TEST',
                                                      p_seq                  => 1,
                                                      p_attr_number          => 1,
                                                      p_number_value         => 1000
                                                     );
             END LOOP;
          END;
          Use your SQL Developer and rund
          DESC apex_collection;
          The package is described properly and you will understand how to use those overloaded procedures.

          Denes Kubicek
          -------------------------------------------------------------------
          http://deneskubicek.blogspot.com/
          http://www.apress.com/9781430235125
          http://apex.oracle.com/pls/apex/f?p=31517:1
          http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
          -------------------------------------------------------------------
          • 2. Re: update_member_attribute Character to number conversion error
            leckj
            Hi Denes,

            Thanks for answering so fast, I spent almost 6 hours looking at signature 1 to 6 of the UPDATE_MEMBER_ATTRIBUTE documentation,
            and and trying a lot of weird things to try to make it work cos I failed to spot that difference.

            In fact I've been working on a tabular form based on a view based on a collection, and since Javascript HTML and the APEX framework
            is all new to me,I've learned the hard way that the collection values are not bound to the form and have to be updated programatically,
            that you cant reference items that declared as 'DO NOT SAVE STATE' in the g_f arrays,to run(maybe) some js code at client-side for validations,
            etc. and I spent some more hours figuring why a javascript code called from the element attribute of a column did not work, only to find you need
            a space between the onfocus and onblur declarations. so thank you.

            I can now use SQL to validate my collection against the ORACLE tables for validations etc, which is what APEX is all about after all.

            Regards
            Leckraj.