This discussion is archived
2 Replies Latest reply: Apr 27, 2013 2:08 AM by leckj RSS

update_member_attribute Character to number conversion error

leckj Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points