12 Replies Latest reply: Oct 10, 2013 8:34 AM by Recx Ltd RSS

    ORA-06550: line 1, column 17: PL/SQL: ORA-00936: missing expression ORA-06550: line 1, column 9: PL/SQL: SQL Statement ignored

    sbing52

      Here is tthe code:

       

      begin

      declare
        p varchar2(32767) := null;
        l_clob clob;
        l_length number := 1;
      begin
      p:=p||'F|#OWNER#:QS_SPECIFICHHNO:P2_HHNO:HHNO';

      wwv_flow_api.create_page_process(
        p_id     => 1223125545905315 + wwv_flow_api.g_id_offset,
        p_flow_id=> wwv_flow.g_flow_id,
        p_flow_step_id => 2,
        p_process_sequence=> 1,
        p_process_point=> 'AFTER_SUBMIT',
        p_process_type=> 'DML_FETCH_ROW',
        p_process_name=> 'Fetch Row from QS_SPECIFICHHNO',
        p_process_sql_clob => p,
        p_process_error_message=> 'Unable to fetch row.',
        p_process_success_message=> '',
        p_process_is_stateful_y_n=>'N',
        p_runtime_where_clause=>'WHERE ( ( (HHNO) = P2_HHNO ) )',
        p_process_comment=>'');
      end;
      null;

      end;
      /

       

      I execute it as follows in SQL Developer:

      select * from QS_SPECIFICHHNO

      WHERE ( ( (HHNO) = &P2_HHNO ) )

       

      No problem:  I receive exactyl one row in my result set which is what I expect.

       

      I think (definitely correct me if I am wrong)  Apex is complaining about the following line of code:

      p:=p||'F|#OWNER#:QS_SPECIFICHHNO:P2_HHNO:HHNO';

       

      It is complaining about a missing expession line 1 ccolumn 9  -   |#OWNER#:

       

      This is an Automatic Fetch query.  P2_HHNO is a simple text field where the user enters a primary key value (Household Number)

       

      The household number is used in the where clause of the QS_SPECIFICHHNO view.

       

      Here is the view:

      CREATE OR REPLACE FORCE VIEW "CASETRANS"."QS_SPECIFICHHNO" ("HHNO","CNAME","PDATE","TDATE","AGE","INWORKNO","ONWORKNO","ONCPS","ONYTHSRV","CPA","TMSUCCESS","VOL","D_N","DLQ","TRU","VPA","PA5","PA46","CPC","COOHPA4","COOHPA5","KINPA5","KINPA4","COOH","CKIN") AS

        SELECT HHNO,

               CNAME,

               PDate,

               TDate,

               msaccess_utilities.datediff('d', PDate, TDate, 0) Age,

               InWorkNo,

               OnWorkNo,

               OnCPS,

               OnYthSrv,

               CPA,

               TmSuccess,

               Vol,

               D_N,

               DLQ,

               TRU,

               VPA,

               PA5,

               PA46,

               CPC,

               COOHPA4,

               COOHPA5,

               KINPA5,

               KINPA4,

               COOH,

               CKIN

          FROM CaseTransfers

          ORDER BY CaseTransfers.HHNo,

                   CaseTransfers.PDate DESC

      ;