ora-00900 : invalid sql statement

irvine74
2007-07-31
2013-04-22
  • irvine74
    irvine74
    2007-07-31

    Hi Patrick,

    I am still receiving this error.
    I have three exact the same RDBMS on 9iR2 on my dev and tst it works fine but on prod I get the error.
    only difference is that path 3.0.1 is not installed on prod but the application was working well on tst too without the path.

    The DBA installed apex 3.0 and apexlib following the documentation. He needs to install the patch though.
    I exported the app from test to prod with the export utility.

    I will try your suggestions you made in APEX forum on oracle forums.
    The quickest way to get an answer from you is through this forum?
    Because I will use this one instead then.

    Thanks for the help

    Erwin

     
    • Patrick Wolf
      Patrick Wolf
      2007-07-31

      Hi Erwin,

      yes that will be the quickest way and others can also participate from the answer if they have a similar problem.

      Can you point me to the OTN Forum thread so that I know what the problem was.

      Patrick

       
    • irvine74
      irvine74
      2007-08-01

      Patrick,

      http://forums.oracle.com/forums/thread.jspa?messageID=1962976&#1962976

      this is the OTN post

      Can it have something to do with rights? Because I know on production rights are controlled by the DBA's and I don't know if they followed all right procedures. At first glance it looked ok...

      Erwin

       
      • Patrick Wolf
        Patrick Wolf
        2007-08-01

        Ok, now I remember your problem. Can you answer the questions I have asked on the OTN forum. That would help to narrow down the problem.

        Patrick

         
    • irvine74
      irvine74
      2007-08-06

      Hi Patrick,

      Sorry it took some time (we just had a new baby)

      In reply to your questions:

      1.Are you only getting the error in debug mode?
      No, when I start the  application and navigate to the page (contains a calendar and a report region) I get immediately the following error
      ORA-00900: invalid SQL statement
      Debug:

         1: begin
         2: ---------------------------------------------
         3: -- Get metadata of page
         4: ---------------------------------------------
         5: ApexLib_Page.generateBrowserData;
         6: ---------------------------------------------
         7: -- Get metadata of items and tabular forms.
         8: ---------------------------------------------
         9: ApexLib_Item.generateBrowserData;
        10: ApexLib_TabForm.generateBrowserData;
        11:
        12: ---------------------------------------------
        13: -- Send data for cascading lovs
        14: ---------------------------------------------
        15: ApexLib_Lov.generateBrowserData;
        16:
        17: ---------------------------------------------
        18: -- Send NLS data for date formats and numeric
        19: -- format masks and translated error messages
        20: ---------------------------------------------
        21: ApexLib_Browser.setNlsData;
        22: ApexLib_Browser.setApexLibMessages;
        23:
        24: ---------------------------------------------
        25: -- Active different navigation improvements
        26: ---------------------------------------------
        27: ApexLib_Browser.setLovIconsNonNavigable;
        28: ApexLib_Browser.setLRButtonIconsNonNavigable
        29:   ( pLeftClassName  => 'L'
        30:   , pRightClassName => 'R'
        31:   );
        32: ApexLib_Browser.checkForLovKey;
        33: ApexLib_Browser.checkForUpDownKey;
        34:
        35: ---------------------------------------------
        36: -- Active different UI improvements
        37: ---------------------------------------------
        38: /* optional
        39: ApexLib_Browser.setTextareaProperty
        40:   ( pProperty => ApexLib_Browser.VERTICAL_RESIZEABLE
        41:   );
        42: */
        43: ---------------------------------------------
        44: -- Init browser validation
        45: ---------------------------------------------
        46: ApexLib_Browser.initValidation
        47:   ( pShowRequiredWarning => TRUE
        48:   );
        49: ---------------------------------------------
        50: -- Has to be the last statement!
        51: ---------------------------------------------
        52: ApexLib_Browser.flushJsBuffer;
        53: end;

      2. Is your report query on that page a valid SQL statement? Yes I can run it in sql

      here is the extract
      select
      INTERFACE_DATE,
      INTERFACE_DATE INTERFACE_DATE_DISPLAY,
      HORIZON_BL,
      FEP_BL
      from FEP_INTERFACE_CALENDAR
      where interface_date between last_day(add_months(to_date(:P19_calendar_date,'YYYYMMDD'),-1))+1 AND Last_day(to_date(:P19_calendar_date,'YYYYMMDD'))
      order by 1

      If you comment out the PL/SQL code with /* */ (don't forget to add a NULL; statement at the end to have a valid region) on page 0 "ApexLib - Before footer", are you now getting an error from APEX that the statement isn't valid?

      If I do this and I go back to the page it seems good , but when I try to update in the report (contains two lov to select a date)
      i get the apexlib error on top of the page stating ora-00900: invalid sql statement twice.

      I check sql statementis in the page and they are exactly the same as in the tst environment and ther it is working fine?

      Erwin

       
    • irvine74
      irvine74
      2007-08-06

      Update,

      I removed the page create it again and calendar is working, if i add report region with tabular form I get error again.

      But what I saw on database level is that I don't have a public synonym for this table. Could this trigger the event?

      Erwin

       
    • irvine74
      irvine74
      2007-08-06

      Update,

      I removed the page create it again and calendar is working, if i add report region with tabular form I get error again.
      I see the only difference between both environments is DB 10g and apex 3.0.1 : working
                                                             DB 9i  and apex 3.0 : not working
      both have same verion of apexlib 1.6

      Erwin

       
    • Patrick Wolf
      Patrick Wolf
      2007-08-06

      After you get the error, can you run the page again in debug mode and send me the HTML output of the page you get? In the debug output you should see which procedures are executed and which raises the error.

      You wrote that when you commented out the code it worked at least when you run the page, can you comment out just part of the code to identify which statement actually raises the error.

      Have you installed the framework into it's own schema or into the application schema?

      Thanks
      Patrick

       
    • irvine74
      irvine74
      2007-08-07

      Hi Patrick

      Here is the output from the debug mode (That's apparently the output I posted on the forum ;-) had forgotten it )

      0.26: Computation point: BEFORE_FOOTER
      0.26: Processing point: BEFORE_FOOTER
      0.26: Region: ApexLib - Before footer
      Generate page data(ApexLib_Page.generateBrowserData)
      ...ignore request BU - BA Selection for validation
      ...ignore request Budget product for validation
      ...ignore request Horizon for validation
      ...ignore request Markets for validation
      ...ignore request Product Families for validation
      ...ignore request Product categories for validation
      0.27: Authorization Check: "5061326139502890" User: "BE76500" Component: ""
      ...ignore request T_ADMINSTRATIONFEP for validation
      ...ignore request T_INTERFACE for validation
      ...ignore request T_MAIN_PAGE for validation
      ...ignore request T_STATISTIC REPORTS for validation
      ...done
      Read page items(ApexLib_Item.init)
      ...item P19_CALENDAR_DATE on page 19
      Read regions(ApexLib_Region.init)
      ...region Interface execution calendar isn't unique!
      ...done
      ...item P19_CALENDAR_TYPE on page 19
      ...done
      Generate item data(ApexLib_Item.generateBrowserData)
      ...done
      Read report column mapping(ApexLib_TabForm.init)
      ...processing Tabular Form
      ...ignore validation = NO
      Get column list(ApexLib_Sql.getColumnList)
      ...parse query = 獥汥捴‍੉乔䕒䙁䍅彄䅔䔬ഊ䡏剉婏也䉌Ⰽ੆䕐彂䰍੦牯洠䙅偟䥎呅剆䅃䕟䍁䱅乄䅒ഊ睨敲攠楮瑥牦慣敟摡瑥⁢整睥敮慳瑟摡礨慤摟浯湴桳⡴潟摡瑥⠺倱㥟捡汥湤慲彤慴攬❙奙奍䵄䐧⤬ⴱ⤩⬱⁁乄⁌慳瑟摡礨瑯彤慴攨㩐ㄹ彣慬敮摡牟摡瑥Ⱗ奙奙䵍䑄✩⤍੯牤敲⁢礠楮瑥牦慣敟摡瑥ഊ
      ORA-00900: invalid SQL statement

      suddenly the query ain't working anymore. If I change the columns back to standard report columns instead of the list LOV I get this output

      0.10: Computation point: BEFORE_FOOTER
      0.10: Processing point: BEFORE_FOOTER
      0.10: Region: ApexLib - Before footer
      Generate page data(ApexLib_Page.generateBrowserData)
      ...ignore request BU - BA Selection for validation
      ...ignore request Budget product for validation
      ...ignore request Horizon for validation
      ...ignore request Markets for validation
      ...ignore request Product Families for validation
      ...ignore request Product categories for validation
      0.11: Authorization Check: "5061326139502890" User: "BE76500" Component: ""
      ...ignore request T_ADMINSTRATIONFEP for validation
      ...ignore request T_INTERFACE for validation
      ...ignore request T_MAIN_PAGE for validation
      ...ignore request T_STATISTIC REPORTS for validation
      ...done
      Read page items(ApexLib_Item.init)
      ...item P19_CALENDAR_DATE on page 19
      Read regions(ApexLib_Region.init)
      ...region Interface execution calendar isn't unique!
      ...done
      ...item P19_CALENDAR_TYPE on page 19
      ...done
      Generate item data(ApexLib_Item.generateBrowserData)
      ...done
      Read report column mapping(ApexLib_TabForm.init)
      ...processing Tabular Form
      ...ignore validation = NO
      ...no updateable report region found on page 19
      ...done
      Generate tabform data(ApexLib_TabForm.generateBrowserData)
      ...done
      Analysing page(ApexLib_Lov.generateBrowserData)
      0.12: Region: ApexLib - Handle Error Page
      0.12: Show page tempate footer

      On the 10g DB where the page is working i get this

      0.09: Computation point: BEFORE_FOOTER
      0.09: Processing point: BEFORE_FOOTER
      0.09: Region: ApexLib - Before footer
      Generate page data(ApexLib_Page.generateBrowserData)
      ...ignore request BU - BA Selection for validation
      ...ignore request Budget production for validation
      ...ignore request Horizon for validation
      ...ignore request Markets for validation
      ...ignore request Product Families for validation
      ...ignore request Product categories for validation
      0.09: Authorization Check: "4089823177954200" User: "BE76500" Component: ""
      ...ignore request T_ADMINSTRATIONFEP for validation
      ...ignore request T_INTERFACE for validation
      ...ignore request T_MAIN_PAGE for validation
      ...ignore request T_STATISTIC REPORTS for validation
      ...done
      Read page items(ApexLib_Item.init)
      ...item P19_CALENDAR_DATE on page 19
      Read regions(ApexLib_Region.init)
      ...done
      ...item P19_CALENDAR_TYPE on page 19
      ...done
      Generate item data(ApexLib_Item.generateBrowserData)
      ...done
      Read report column mapping(ApexLib_TabForm.init)
      ...processing Interface execution selection
      ...ignore validation = NO
      Get column list(ApexLib_Sql.getColumnList)
      ...parse query = select INTERFACE_DATE, INTERFACE_DATE INTERFACE_DATE_DISPLAY, HORIZON_BL, FEP_BL from FEP_INTERFACE_CALENDAR where interface_date between last_day(add_months(to_date(:P19_calendar_date,'YYYYMMDD'),-1))+1 AND Last_day(to_date(:P19_calendar_date,'YYYYMMDD')) order by 1
      ......column INTERFACE_DATE mapped to WWV_Flow.g_f01
      ......column INTERFACE_DATE_DISPLAY not mapped - not stateful.
      ......column HORIZON_BL mapped to WWV_Flow.g_f02
      ......column FEP_BL mapped to WWV_Flow.g_f03
      ...array has 0 row(s). Used f03 to count.
      ...done
      Generate tabform data(ApexLib_TabForm.generateBrowserData)
      ...done
      Analysing page(ApexLib_Lov.generateBrowserData)
      0.09: Region: ApexLib - Handle Error Page
      0.09: Show page tempate footer

      you see the query is passed correctly here
      probably this is going wrong in the other instance
      column INTERFACE_DATE mapped to WWV_Flow.g_f01
      ......column INTERFACE_DATE_DISPLAY not mapped - not stateful.
      ......column HORIZON_BL mapped to WWV_Flow.g_f02
      ......column FEP_BL mapped to WWV_Flow.g_f03

      Do you have any clue?

      thanks a lot for your help

       
      • Patrick Wolf
        Patrick Wolf
        2007-08-07

        Strange, there seems to be some CLOB conversion problem or so. Otherwise I could not imagine why it shows the strange characters. When you export your application and look into the export file, how does the SQL statement look there?

        When you run the following query in the SQL Workshop, does it show the correct SQL statement in the source column?

        SELECT ID
             , NAME
             , SOURCE_TYPE
             , SOURCE
          FROM APEXLIB_V_PAGE_REGION REG
        WHERE APPLICATION_ID = <your application id>
           AND PAGE_ID        = 19
           AND SOURCE_TYPE    IN ( 'SQL_QUERY'
                                 , 'UPDATABLE_SQL_QUERY'
                                 , 'FUNCTION_RETURNING_SQL_QUERY'
                                 )
        ;

        The reason why your second attempt with the standard report columns doesn't fail is, because the tabular form isn't detected as changeable tabular form that's why it's skipped by the framework -> nothing to do.

        About my previous posting, "Have you installed the framework into it's own schema or into the application schema?"

        Patrick

         
    • irvine74
      irvine74
      2007-08-07

      Update

      I installed the path to 3.0.1 on the 9i instance but still remain the same problem

      Erwin

       
    • irvine74
      irvine74
      2007-08-07

      Sorry ,

      I overlooked you question : I did install it into it's own schema apexlib (so the old install option)

       
    • irvine74
      irvine74
      2007-08-07

      It does show the sql query correctly when executing your query
      output

      7062145665420635
      Interface execution selection
      UPDATABLE_SQL_QUERY
      select INTERFACE_DATE, INTERFACE_DATE INTERFACE_DATE_DISPLAY, HORIZON_BL, FEP_BL from FEP_INTERFACE_CALENDAR where interface_date between last_day(add_months(to_date(:P19_calendar_date,'YYYYMMDD'),-1))+1 AND Last_day(to_date(:P19_calendar_date,'YYYYMMDD')) order by 1 

      output extract from the export file , looks good not?

      declare
        s varchar2(32767) := null;
        l_clob clob;
        l_length number := 1;
      begin
      s:=s||'select '||chr(10)||
      'INTERFACE_DATE,'||chr(10)||
      'INTERFACE_DATE INTERFACE_DATE_DISPLAY,'||chr(10)||
      'HORIZON_BL,'||chr(10)||
      'FEP_BL'||chr(10)||
      'from FEP_INTERFACE_CALENDAR'||chr(10)||
      'where interface_date between last_day(add_months(to_date(:P19_calendar_date,''YYYYMMDD''),-1))+1 AND Last_day(to_date(:P19_calendar_date,''YYYYMMDD''))'||chr(10)||
      'order by 1'||chr(10)||
      '';

      wwv_flow_api.create_report_region (
        p_id=> 7062145665420635 + wwv_flow_api.g_id_offset,
        p_flow_id=> wwv_flow.g_flow_id,
        p_page_id=> 19,
        p_name=> 'Interface execution selection',
        p_region_name=>'',
        p_template=> 6005055511237599+ wwv_flow_api.g_id_offset,
        p_display_sequence=> 15,
        p_display_column=> 2,
        p_display_point=> 'AFTER_SHOW_ITEMS',
        p_source=> s,
        p_source_type=> 'UPDATABLE_SQL_QUERY',
        p_display_error_message=> '#SQLERRM#',
        p_customized=> '0',
        p_translate_title=> 'Y',
        p_query_row_template=> 6013560755237610+ wwv_flow_api.g_id_offset,
        p_query_headings_type=> 'COLON_DELMITED_LIST',
        p_query_num_rows=> '15',
        p_query_options=> 'DERIVED_REPORT_COLUMNS',
        p_query_show_nulls_as=> '(null)',
        p_query_break_cols=> '0',
        p_query_no_data_found=> 'No data found.',
        p_query_num_rows_type=> 'ROW_RANGES_IN_SELECT_LIST',
        p_query_row_count_max=> '500',
        p_pagination_display_position=> 'BOTTOM_RIGHT',
        p_csv_output=> 'N',
        p_sort_null=> 'F',
        p_query_asc_image=> 'arrow_down_gray_dark.gif',
        p_query_asc_image_attr=> 'width="13" height="12" alt=""',
        p_query_desc_image=> 'arrow_up_gray_dark.gif',
        p_query_desc_image_attr=> 'width="13" height="12" alt=""',
        p_plug_query_strip_html=> 'Y',
        p_comment=>'');
      end;

       
      • Patrick Wolf
        Patrick Wolf
        2007-08-07

        Any chance that I get access to the system where you have the problems? So that I can have a look.
        If not, add some additional debug messages into the package ApexLib_TabForm.init

        at line 212

        vSource := rREG.SOURCE;

        add

        ApexLib_Util.debug('...***query:'||SUBSTR(vSource, 1, 60));

        after the above line to check if the query statement is ok after fetching it. Run the page in debug mode to see the output. I don't really understand it, because Oracle should do a automatic type conversion from CLOB to VARCHAR2 without problems.

        Patrick

         
    • irvine74
      irvine74
      2007-08-07

      output

      0.10: Computation point: BEFORE_FOOTER
      0.10: Processing point: BEFORE_FOOTER
      0.10: Region: ApexLib - Before footer
      Generate page data(ApexLib_Page.generateBrowserData)
      ...ignore request BU - BA Selection for validation
      ...ignore request Budget production for validation
      ...ignore request Horizon for validation
      ...ignore request Markets for validation
      ...ignore request Product Families for validation
      ...ignore request Product categories for validation
      0.10: Authorization Check: "5061326139502890" User: "BE76500" Component: ""
      ...ignore request T_ADMINSTRATIONFEP for validation
      ...ignore request T_INTERFACE for validation
      ...ignore request T_MAIN_PAGE for validation
      ...ignore request T_STATISTIC REPORTS for validation
      ...done
      Read page items(ApexLib_Item.init)
      ...item P19_CALENDAR_DATE on page 19
      Read regions(ApexLib_Region.init)
      ...done
      ...item P19_CALENDAR_TYPE on page 19
      ...done
      Generate item data(ApexLib_Item.generateBrowserData)
      ...done
      Read report column mapping(ApexLib_TabForm.init)
      ...processing Interface execution selection
      ...ignore validation = NO
      ...***query:select INTERFACE_DATE, INTERFACE_DATE INTERFACE_DATE_DISPLAY, HORIZON_BL, FEP_BL from FEP_INTERFACE_CALENDAR where interface_date between last_day(add_mo
      Get column list(ApexLib_Sql.getColumnList)
      ...parse query = 獥汥捴‍੉乔䕒䙁䍅彄䅔䔬ഊ䥎呅剆䅃䕟䑁呅⁉乔䕒䙁䍅彄䅔䕟䑉卐䱁夬ഊ䡏剉婏也䉌Ⰽ੆䕐彂䰍੦牯洠䙅偟䥎呅剆䅃䕟䍁䱅乄䅒ഊ睨敲攠楮瑥牦慣敟摡瑥⁢整睥敮慳瑟摡礨慤摟浯湴桳⡴潟摡瑥⠺倱㥟捡汥湤慲彤慴攬❙奙奍䵄䐧⤬ⴱ⤩⬱⁁乄⁌慳瑟摡礨瑯彤慴攨㩐ㄹ彣慬敮摡牟摡瑥Ⱗ奙奙䵍䑄✩⤍੯牤敲⁢礠ㄍ਀
      ORA-00900: invalid SQL statement

      I wanted to load it on apex.oracle.com but tried to install apexlib but didn't succeed
      http://apex.oracle.com/pls/otn/f?p=4500:1000:1137162169894772

      agc_space
      agctest/agctest

       
    • Patrick Wolf
      Patrick Wolf
      2007-08-07

      Hi,

      I have installed ApexLib into your workspace on apex.oracle.com, but I failed running your application because it seems to have dependencies to other tables which you haven't loaded yet.

      I have mailed you two packages with additional debug infos, which should help to identify when the query string gets corrupt.

      Patrick

       
    • irvine74
      irvine74
      2007-08-07

      this is the output after I applied your packages

      Read report column mapping(ApexLib_TabForm.init)
      ...processing Interface execution selection
      ...ignore validation = NO
      ...***1: select INTERFACE_DATE, INTERFACE_DATE INTERFACE_DATE_DISPLAY, HORIZON_BL, F
      ...***2: select INTERFACE_DATE, INTERFACE_DATE INTERFACE_DATE_DISPLAY, HORIZON_BL, FEP_BL from FEP_INTERFACE_CALENDAR where interface_date between last_day(add_months(to_date(:P19_calendar_date,'YYYYMMDD'),-1))+1 AND Last_day(to_date(:P19_calendar_date,'YYYYMMDD')) order by 1
      ...***3: select INTERFACE_DATE, INTERFACE_DATE INTERFACE_DATE_DISPLAY, HORIZON_BL, F
      ...***4: select INTERFACE_DATE, INTERFACE_DATE INTERFACE_DATE_DISPLAY, HORIZON_BL, FEP_BL from FEP_INTERFACE_CALENDAR where interface_date between last_day(add_months(to_date(:P19_calendar_date,'YYYYMMDD'),-1))+1 AND Last_day(to_date(:P19_calendar_date,'YYYYMMDD')) order by 1
      Get column list(ApexLib_Sql.getColumnList)
      ...***1: 獥汥捴‍੉乔䕒䙁䍅彄䅔䔬ഊ䥎呅剆䅃䕟䑁呅⁉乔䕒䙁䍅彄䅔䕟䑉卐䱁夬ഊ䡏剉婏也䉌Ⰽ੆䕐彂䰍੦牯洠䙅偟䥎呅剆䅃䕟䍁䱅乄䅒ഊ睨敲攠楮瑥牦慣敟摡瑥⁢整睥敮慳瑟摡礨慤摟浯
      ...***2: 獥汥捴‍੉乔䕒䙁䍅彄䅔䔬ഊ䥎呅剆䅃䕟䑁呅⁉乔䕒䙁䍅彄䅔䕟䑉卐䱁夬ഊ䡏剉婏也䉌Ⰽ੆䕐彂䰍੦牯洠䙅偟䥎呅剆䅃䕟䍁䱅乄䅒ഊ睨敲攠楮瑥牦慣敟摡瑥⁢整睥敮慳瑟摡礨慤摟浯湴桳⡴潟摡瑥⠺倱㥟捡汥湤慲彤慴攬❙奙奍䵄䐧⤬ⴱ⤩⬱⁁乄⁌慳瑟摡礨瑯彤慴攨㩐ㄹ彣慬敮摡牟摡瑥Ⱗ奙奙䵍䑄✩⤍੯牤敲⁢礠ㄍ਀
      ...***3: 獥汥捴‍੉乔䕒䙁䍅彄䅔䔬ഊ䥎呅剆䅃䕟䑁呅⁉乔䕒䙁䍅彄䅔䕟䑉卐䱁夬ഊ䡏剉婏也䉌Ⰽ੆䕐彂䰍੦牯洠䙅偟䥎呅剆䅃䕟䍁䱅乄䅒ഊ睨敲攠楮瑥牦慣敟摡瑥⁢整睥敮慳瑟摡礨慤摟浯
      ...***4: 獥汥捴‍੉乔䕒䙁䍅彄䅔䔬ഊ䥎呅剆䅃䕟䑁呅⁉乔䕒䙁䍅彄䅔䕟䑉卐䱁夬ഊ䡏剉婏也䉌Ⰽ੆䕐彂䰍੦牯洠䙅偟䥎呅剆䅃䕟䍁䱅乄䅒ഊ睨敲攠楮瑥牦慣敟摡瑥⁢整睥敮慳瑟摡礨慤摟浯湴桳⡴潟摡瑥⠺倱㥟捡汥湤慲彤慴攬❙奙奍䵄䐧⤬ⴱ⤩⬱⁁乄⁌慳瑟摡礨瑯彤慴攨㩐ㄹ彣慬敮摡牟摡瑥Ⱗ奙奙䵍䑄✩⤍੯牤敲⁢礠ㄍ਀
      ...parse query = 獥汥捴‍੉乔䕒䙁䍅彄䅔䔬ഊ䥎呅剆䅃䕟䑁呅⁉乔䕒䙁䍅彄䅔䕟䑉卐䱁夬ഊ䡏剉婏也䉌Ⰽ੆䕐彂䰍੦牯洠䙅偟䥎呅剆䅃䕟䍁䱅乄䅒ഊ睨敲攠楮瑥牦慣敟摡瑥⁢整睥敮慳瑟摡礨慤摟浯湴桳⡴潟摡瑥⠺倱㥟捡汥湤慲彤慴攬❙奙奍䵄䐧⤬ⴱ⤩⬱⁁乄⁌慳瑟摡礨瑯彤慴攨㩐ㄹ彣慬敮摡牟摡瑥Ⱗ奙奙䵍䑄✩⤍੯牤敲⁢礠ㄍ਀
      ORA-00900: invalid SQL statement
      Debug:

       
    • Patrick Wolf
      Patrick Wolf
      2007-08-12

      Added the workaround for the strange CLOB to VARCHAR2 conversion problem on Oracle 9i into the main code tree.
      Bug# 1772657: Strange CLOB to VARCHAR2 conversion bug in Oracle 9i

      Greetings
      Patrick

       

  • Anonymous
    2010-06-08

    Hi,
    I know this is an *old* thread, but I came across the same issue today, so I Googled "ApexLib invalid SQL".

    Anyway, FYI, the cause appears to be when the report type is "SQL Query (PL/SQL function body return SQL query)"

    For example, with Report Type set to  "SQL Query (PL/SQL function body return SQL query)"

    return 'select ''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'' from dual';

    will generate the error, but with Report Type set to "SQL Query", the following works fine.

    return 'select ''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'' from dual';

    Cheers, and thanks for continuing to develop & support ApexLib!
    Richard