ora-00900 : invalid sql statement

[irvine74]

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]

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]

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]

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]

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]

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]

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]

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

[Patrick Wolf]

BTW, any chance to reproduce that problem on apex.oracle.com?

You can use http://inside-apex.blogspot.com/2007/04/installing-apexlib-on-apexoraclecom.html to install the ApexLib files on apex.oracle.com

Patrick

[irvine74]

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]

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]

Update

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

Erwin

[irvine74]

Sorry ,

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

[irvine74]

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]

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]

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]

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]

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]

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]

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