Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-900 Invalid Sql Statement.

AlokKumarMay 20 2008 — edited May 20 2008
Hi,

I've got the strange problem, please go through with my findings.


BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production


chry_pm_stg@PUBINT> SELECT data_utils.column_list('CHRY_PM_STG.PARTS@EPC_TARGET', 0, 0, NULL,1, NULL, NULL, 0) FROM DUAL;

DATA_UTILS.COLUMN_LIST('CHRY_PM_STG.PARTS@EPC_TARGET',0,0,NULL,1,NULL,NULL,0)
-----------------------------------------------------------------------------
PARTID,PARTNO,PARTNAME,PARTLANGID


BANNER
------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

chry_pm_aj@W9ITEST> SELECT data_utils.column_list('CHRY_PM_AJ.PARTS@EPC_TARGET', 0, 0, NULL,1, NULL, NULL, 0) FROM DUAL;
SELECT data_utils.column_list('CHRY_PM_AJ.PARTS@EPC_TARGET', 0, 0, NULL,1, NULL, NULL, 0) FROM DUAL
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "CHRY_PM_AJ.DATA_UTILS", line 492
ORA-06512: at line 1

In the above code, Data Utils is a package and column_list is a function.As you can clearly see, the above code works well in first case, but in the second case, the same code does return an error, howerve the database version is not same.

hare krishna
Alok

Comments

fac586
Answer 
van Baal HR wrote:
I want to query the APEX data dictionary for the pages I have created. The authorisation concept here dictates, that the pages created with APEX, must be granteable to roles and therefore I need to query the dictionary. Unfortunately I do not know what tables to access. Some stay empty, even when I connect to the database as DBA 
Does anybody have a tip, what I can do here?

Many of the APEX views contain restrictions based on the database user being the parsing schema for the application, or a schema associated for the workspace. Some are dependent on being queried from an active APEX session.

The views can be interactively explored using the APEX Views utility in the Application Builder, and the dictionary is extensively documented in the APEX_DICTIONARY metadata view:

select * from apex_dictionary where apex_view_name = '<view name>'

Marked as Answer by van Baal HR · Sep 27 2020
van Baal HR

The query, that gave me the wanted information is:

SELECT *

  FROM apex_application_pages

WHERE application_id = 103

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 17 2008
Added on May 20 2008
#general-database-discussions
7 comments
6,405 views