5 Replies Latest reply: Oct 24, 2010 11:16 AM by 807644 RSS

    Upgrade Bug from 3.0.1 to 4.0.1 - syntax not accepted now


      I have just upgraded to Apex 4.0.1 (full installation from previous installed 3.0.1) in order to add the Ajax item functionality to my existing 3.0.1 app and getting the following really weird syntax error when I try to build an existing 'On Submit - Before Computations and Validations' process (get error just by clicking Apply Changes without making any modifications to it).

      The error is flaggged for the line below (which constructs a report column title) and subsequent references where APEX 4 doesn't seem to build the &P11_PCT_DRIFT1. syntax which previously worked? Bit lost on this as none of the items have changed at all here it seems to have been caused purely by the upgrade. I also tried to Apply Changes in Mozilla as Apex 4 requires you upgrade IExplorer to 7 however both browsers show the same issue. Any help would be appreciated. Thanks.

      :P11_LTG1_TITLE := 'LTG ' || &P11_PCT_DRIFT1. || '% drift';

      P11_LTG1_TITLE is Hidden
      P11_PCT_DRIFT1 is a Select List

      1 error has occurred
      ORA-06550: line 71, column 30: PLS-00103: Encountered the symbol "&" when expecting one of the following: ( - + case mod new null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specification>

      cursor col_titles_cur(column_name VARCHAR2) is
      select col_title, order_by
      from display_column_titles_v
      where col_name = column_name;
      col_titles_rec col_titles_cur%ROWTYPE;
      tempVal VARCHAR2(2000):= :P11_DISPLAY_COLUMN_ORDER;
      round_selected NUMBER := instr(:P11_DISPLAY_COLUMN_ORDER, 'bfr.round');
      show_summary_only NUMBER := instr(:P11_DISPLAY_COLUMN_ORDER, 'summary');
      currVal VARCHAR2(2000):= '';
      field_pos NUMBER := 0;
      :P11_COURT := :P4_COURT;
      if :P11_COURT != '-1' then
      -- :P11_COURT_IN := replace(:P11_COURT_IN_PARAM,':',',');
      -- :P11_COURT_IN := '' || replace(:P11_COURT,':',''',''') || '';
      -- :P11_COURT_PARAM_IN := '' || replace(:P11_COURT_PARAM_IN,':',''',''') || '';
      :P11_COURT_DISPLAY := '- All Courts -';
      end if;
      :P11_SUBMIT_COUNT := to_char(2);
      :P11_COL1_TITLE := 'null';
      :P11_COL2_TITLE := 'null';
      :P11_COL3_TITLE := 'null';
      :P11_COL4_TITLE := 'null';
      :P11_COL5_TITLE := 'null';
      :P11_COL6_TITLE := 'null';
      :P11_COL7_TITLE := 'null';
      :P11_COL8_TITLE := 'null';
      :P11_COL9_TITLE := 'null';
      :P11_COL10_TITLE := 'null';
      :P11_COL1_VALUE := 'null';
      :P11_COL2_VALUE := 'null';
      :P11_COL3_VALUE := 'null';
      :P11_COL4_VALUE := 'null';
      :P11_COL5_VALUE := 'null';
      :P11_COL6_VALUE := 'null';
      :P11_COL7_VALUE := 'null';
      :P11_COL8_VALUE := 'null';
      :P11_COL9_VALUE := 'null';
      :P11_COL10_VALUE := 'null';
      :P11_COL_ROUND_ID_VALUE := 'null';
      :P11_COL1_GROUPBY := 'null';
      :P11_COL2_GROUPBY := 'null';
      :P11_COL3_GROUPBY := 'null';
      :P11_COL4_GROUPBY := 'null';
      :P11_COL5_GROUPBY := 'null';
      :P11_COL6_GROUPBY := 'null';
      :P11_COL7_GROUPBY := 'null';
      :P11_COL8_GROUPBY := 'null';
      :P11_COL9_GROUPBY := 'null';
      :P11_COL10_GROUPBY := 'null';
      :P11_COL_ROUND_ID_GROUPBY := 'null';
      :P11_COL1_ORDERBY := 'null';
      :P11_COL2_ORDERBY := 'null';
      :P11_COL3_ORDERBY := 'null';
      :P11_COL4_ORDERBY := 'null';
      :P11_COL5_ORDERBY := 'null';
      :P11_COL6_ORDERBY := 'null';
      :P11_COL7_ORDERBY := 'null';
      :P11_COL8_ORDERBY := 'null';
      :P11_COL9_ORDERBY := 'null';
      :P11_COL10_ORDERBY := 'null';
      :P11_LTG1_TITLE := 'LTG ' || &P11_PCT_DRIFT1. || '% drift'; -- *** error flagged for this line, and following 8 lines if this is commented out ***
      :P11_LTG2_TITLE := 'LTG ' || &P11_PCT_DRIFT2. || '% drift';
      :P11_LTG3_TITLE := 'LTG ' || &P11_PCT_DRIFT3. || '% drift';
      :P11_LTB1_TITLE := 'LTB ' || &P11_PCT_DRIFT1. || '% drift';
      :P11_LTB2_TITLE := 'LTB ' || &P11_PCT_DRIFT2. || '% drift';
      :P11_LTB3_TITLE := 'LTB ' || &P11_PCT_DRIFT3. || '% drift';
      :P11_DTB1_TITLE := 'DTB ' || &P11_PCT_DRIFT1. || '% drift';
      :P11_DTB2_TITLE := 'DTB ' || &P11_PCT_DRIFT2. || '% drift';
      :P11_DTB3_TITLE := 'DTB ' || &P11_PCT_DRIFT3. || '% drift';
      if show_summary_only <= 0 then -- do all routine allocating columns only if a summary isnt in selected shuttle list
        • 1. Re: Upgrade Bug from 3.0.1 to 4.0.1 - syntax not accepted now
          Tried changing this to a Text field instead but still get error (P11_PCT_DRIFT1 has Default Value of 100)

          P11_LTG1_TITLE is Hidden
          • 2. Re: Upgrade Bug from 3.0.1 to 4.0.1 - syntax not accepted now
            :P11_LTG1_TITLE := 'LTG ' || &P11_PCT_DRIFT1. || '% drift';
            The syntax error indicates that the <tt>&P11_PCT_DRIFT1.</tt> substitution has not been performed. This might be a 4.0 bug, or might be an intentional change for security reasons. Note the recommendations given in table 7-3 of the documentation, regarding where to use the appropriate item reference syntax. Bind variable or <tt>V</tt> function syntax should be used in PL/SQL code:
            :P11_LTG1_TITLE := 'LTG ' || :P11_PCT_DRIFT1 || '% drift';
            :P11_LTG1_TITLE := 'LTG ' || v('P11_PCT_DRIFT1') || '% drift';
            • 3. Re: Upgrade Bug from 3.0.1 to 4.0.1 - syntax not accepted now
              Thanks very much I will give that a try.
              • 4. Re: Upgrade Bug from 3.0.1 to 4.0.1 - syntax not accepted now
                I've moved from iPad to Mac and tried this out. Now realise that you're seeing this in the builder rather than at runtime.

                To get the APEX Builder to accept this code, check the Do not validate PL/SQL code (parse PL/SQL code at runtime only) checkbox before applying changes and it should be accepted. The substitution is performed at runtime, so there isn't actually any bug or change in APEX 4.0 behaviour here.

                However, it is certainly better practice to use bind variable or <tt>V</tt> function rather than exact substitution syntax in PL/SQL blocks.
                • 5. Re: Upgrade Bug from 3.0.1 to 4.0.1 - syntax not accepted now
                  Thanks very much.

                  Also have an issue relating to forming an IN CLAUSE dynamically within an items LOV definition when using the new Cascading LOV Parent Item(s) functionality.

                  I dont really understand and cant find examples of how you reference the multiple values returned from a multiselect list parent within the IN CLAUSE of a child LOV definition or the syntax.

                  Wondered how would the following Child LOV definition look synctactically for both string values returned from the parent (maybe you have to insert quotes around each of the individual returned strings and replace colons with commas?) and also for numeric values returned from the parent?

                  select a,b .... where col in (PARENT MULTI LOV MULTIPLE VALUES RETURNED)

                  I have a parent Multiselect LOV P4_SERIES which filters another Multiselect LOV P4_SERIES_CHILD and I was previously forming the in clause for the child LOV, P4_SERIES_IN, in a Submit Process by replacing the colons with commas and quotes surrounding each comma then using this syntax to get the IN CLAUSE .... series in ('&P4_SERIES_IN.'). An example of how this breaks down is -

                  P4_SERIES parent multiselect result string1:string2:string3

                  Submit Process contained statement: :P4_SERIES_IN := '' || replace(:P4_SERIES,':',''',''') || '';

                  After submit process P4_SERIES_IN in clause part formed as string1','string2','string3

                  The child LOV, P4_SERIES_CHILD, definition is

                  select .... where (series in ('&P4_SERIES_IN.') or :P4_SERIES = '-1')

                  which when substituting in P4_SERIES_IN parses as

                  select .... where (series in ('value1','value2','value3') or :P4_SERIES = '-1')

                  With the new Cascading LOV Parent Item(s) functionality I am hoping to move the child LOV 'IN CLAUSE' formulation directly in the LOV definition instead of the Submit Process
                  as the Submit Process will no longer be needed because the change of parent lov will cause the child lov SQL to be called to filter its values. The main issue I dont understand is how to form the IN CLAUSE syntactically to get the equivalent of the above. I only got this to work experimentally in the first place apologies for my newness to this syntax and lack of understanding.

                  Need to combine

                  select .... where (series in ('&P4_SERIES_IN.') or :P4_SERIES = '-1')


                  :P4_SERIES_IN := '' || replace(:P4_SERIES,':',''',''') || '';

                  to give

                  select .... where (series in ('string1',string2,'string3') or :P4_SERIES = '-1')

                  I also have a requirement for numeric ids returned from a different parent lov into another child lov which you would presume might be done with (note ',' instead of ''',''')

                  :P4_PARENT_IN := '' || replace(:P4_PARENT,':',',') || '';

                  to give child lov definition

                  select .... where (numcol in (1,2,3) or :P4_PARENT = '-1')

                  This doesnt seem to work for me so the syntax to achieve this is not clear to me.