Oracle Apex PL/SQL - store value from text field into variable

Question

In an Oracle APEX page, I have some text fields in a region. The text I put in should be saved in a table. I have a select list on the page that lets me determine whether I should use the value from text field A or from text field B. My select list displays the static values Yes and No.

If my select list value is 'Yes', I take value B (new input from the text field and if it is 'No', value A = value B).

Here what I've tried:

declare Value_C varchar2(20) := null;

begin
  if :P1_YN = 'No'
    then :Value_C := P1_Value_A;
  else :Value_C := P1_Value_B
  end if;

  insert into [table_name]
  (ID, PATH, ValueA, ValueB)
  values
  (:P1_ID, :P1_PATH, :P1_Value_A, :Value_C);
end;

When I run this code, however, I get an error

Err-1002 Unable to find item ID for item "Value_C" in application "xxx".

I believe I have focused your question without changing your meaning. If I failed to understand what you were asking, however, please let me know. — Justin Cave
Oh thank you a lot Justin. That's excactly how I want to express myself. — Dave Chen

Justin Cave Justin Cave · Accepted Answer · 2016-11-16T00:26:22

If your intention is to declare a local variable value_c, you wouldn't prefix it with a colon. You would put a colon before P1_Value_A and P1_Value_B when you do the assignment assuming that those are also items on your page. Your code is also missing a semicolon after the assignment in the else block.

declare 
  Value_C varchar2(20) := null;
begin
  if :P1_YN = 'No'
  then 
    Value_C := :P1_Value_A;
  else 
    Value_C := :P1_Value_B;
  end if;

  insert into [table_name]
  (ID, PATH, ValueA, ValueB)
  values
  (:P1_ID, :P1_PATH, :P1_Value_A, Value_C);
end;

would be syntactically correct assuming that all the identifiers that have the colon prefix are items on your page.

Oracle Apex PL/SQL - store value from text field into variable

1 Answers