OraFAQ Forum: Security » SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW

Home » RDBMS Server » Security » SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW (10g, Windows Server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378288]

Mon, 29 December 2008 16:40

monalip
Messages: 6
Registered: December 2008

Junior Member

Hi,
I am stuck with this problem for weeks and have run out of ideas. Any help is greatly appreciated as I have very limited time now.
1. I have a view myView which is built (programmatically by java code) on view1, view2,..etc. The list of these views increases every day and myView gets refreshed.

2. I have a reporting role 'crystalrep' that needs to read data from myView.

3. I gave
grant select any table to crystalrep
This allows crystalrep to see the myView, but not the data in it (There are 40000 rows in myView) -
(Logged in as crystalrep)
SQL> select count(*) from icm.myView;

COUNT(*)
----------
0
4. After this, I created a new table from icm schema as
create table aTable as select * from myView;

and queried rows from aTable with crystalrep. This shows me 40000 rows.

Why the select on myView shows 0 rows and select on aTable shows 40000? I need the crystalrep to be able to see the data in myView. Please suggest what I should do.

Many thanks

[Updated on: Mon, 29 December 2008 17:06]

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378341 is a reply to message #378288]

Tue, 30 December 2008 01:38

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Without the definition of your view, no one can help. I bet this view contains something referencing USER or current schema or the like.

Regards
Michel

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378416 is a reply to message #378288]

Tue, 30 December 2008 05:55

monalip
Messages: 6
Registered: December 2008

Junior Member

The Oracle database here is the IBM DB2 Content Manager Library Server database. Each time user creates a document type, the CM creates new underlying tables and views for the new document type. The reporting user CRYSTALREP needs read only access to these tables.
The JAVA code to create the ALL_DOCUMENTS view is as below -
(I can not change the code/owners of objects as I am supporting an existaing system)

StringBuffer buffer = new StringBuffer("create view " + viewName + " as ");
int i = 0;

if (docTypes != null && docTypes.size() > 0) {

	Iterator it = docTypes.iterator();
	while (it.hasNext()) {

	DocumentType type = (DocumentType) it.next();
	if (i > 0) {
		buffer.append(" UNION ALL ");
	}
	buffer.append("(select ");
	buffer.append("TO_NUMBER('" + type.getTypeUid() + "')"
			+ " as typeid");
	buffer.append(",");
	buffer
	.append("documentid, comments, dealreviewnumber, 
documentstatus, datescanned, userscanned, maker, checker, boid, skid");

	if (type.getParentType()
	.equalsIgnoreCase(Constants.DOC_TYPE_S3)) {
	buffer
	.append(",RECEIVEDFROMCAMUDATE,SECOREUPDATEDDATE,SENTTOCAMUDATE,SENTTOCAMUUSER,RECEIVEDFROMCAMUUSER");
	buffer
	.append(",null as EXPIRYDATE,null as NOTREQUIREDATMARKETDATE,null as RECEIVEDFROMMARKETDATE,
null as RECEIVEDFROMMARKETUSER,
null as SENTTOMARKETDATE,null as SENTTOMARKETUSER,null as DATESIGNED,null as SECOREUPDATEDDATE,
null as SECOREUPDATEDUSER");
} else if (type.getParentType().equalsIgnoreCase(
	Constants.DOC_TYPE_MARKET)) {
		buffer
		.append(",null as RECEIVEDFROMCAMUDATE,null as SECOREUPDATEDATE,
null as SENTTOCAMUDATE,
null as SENTTOCAMUUSER,null as RECEIVEDFROMCAMUUSER");
	buffer
	.append(" ,EXPIRYDATE,NOTREQUIREDATMARKETDATE,RECEIVEDFROMMARKETDATE,RECEIVEDFROMMARKETUSER,
SENTTOMARKETDATE,SENTTOMARKETUSER,DATESIGNED,SECORE UPDATEDDATE,SECOREUPDATEDUSER ");
} else if (type.getParentType().equalsIgnoreCase(
						Constants.DOC_TYPE_SUPPORTING)) {
		buffer
		.append(",null as RECEIVEDFROMCAMUDATE,null as SECOREUPDATEDATE,
null as SENTTOCAMUDATE,
null as SENTTOCAMUUSER,null as RECEIVEDFROMCAMUUSER");
	buffer
	.append(",EXPIRYDATE,null as NOTREQUIREDATMARKETDATE,null as RECEIVEDFROMMARKETDATE,
null as RECEIVEDFROMMARKETUSER,null as SENTTOMARKETDATE,
null as SENTTOMARKETUSER,DATESIGNED,
null as SECOREUPDATEDDATE,null as SECOREUPDATEDUSER");

	}
	buffer.append(" from ");
	buffer.append(type.getItemType());
	buffer.append("001)");
	i++;
}

System.out.println(buffer.toString());
conn = getOracleConnection();
stmt = conn.createStatement();
stmt.execute(buffer.toString());
conn.commit();

The SQL I executed is as below -

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>SQLPLUS icmadmin/password@icmnlsdb

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 30 11:32:54 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE USER "CRYSTALREP" PROFILE "DEFAULT" IDENTIFIED BY "password"  ACCOUN
T UNLOCK;

User created.

SQL> GRANT "CONNECT" TO "CRYSTALREP";

Grant succeeded.

SQL> select count(*) from all_documents;

  COUNT(*)
----------
     40250

SQL> grant select any table to crystalrep;

Grant succeeded.


SQL> connect CRYSTALREP@icmnlsdb
Enter password:
Connected.
SQL> desc icmadmin.all_documents;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 TYPEID                                             NUMBER
 DOCUMENTID                                         VARCHAR2(30)
 COMMENTS                                           VARCHAR2(50)
 DEALREVIEWNUMBER                                   VARCHAR2(35)
 DOCUMENTSTATUS                                     VARCHAR2(30)
 DATESCANNED                                        DATE
 USERSCANNED                                        CHAR(35)
 MAKER                                              VARCHAR2(35)
 CHECKER                                            VARCHAR2(35)
 BOID                                               CLOB
 SKID                                               CLOB
 MARKETS                                            VARCHAR2(4000)
 FILENAME                                           VARCHAR2(100)
 LOCATION                                           CHAR(100)
 RECEIVEDFROMCAMUDATE                               DATE
 SECOREUPDATEDATE                                   DATE
 SENTTOCAMUDATE                                     DATE
 SENTTOCAMUUSER                                     VARCHAR2(35)
 RECEIVEDFROMCAMUUSER                               VARCHAR2(35)
 EXPIRYDATE                                         DATE
 NOTREQUIREDATMARKETDATE                            DATE
 RECEIVEDFROMMARKETDATE                             DATE
 RECEIVEDFROMMARKETUSER                             VARCHAR2(35)
 SENTTOMARKETDATE                                   DATE
 SENTTOMARKETUSER                                   VARCHAR2(35)
 DATESIGNED                                         DATE
 SECOREUPDATEDDATE                                  DATE
 SECOREUPDATEDUSER                                  VARCHAR2(35)

SQL> select count(*) from icmadmin.all_documents;

  COUNT(*)
----------
         0


SQL> connect icmadmin@icmnlsdb
Enter password:
Connected.
SQL> create table myTable as select * from all_documents;

Table created.

SQL> connect crystalrep@icmnlsdb
Enter password:
Connected.


SQL> select count(*) from icmadmin.myTable;

  COUNT(*)
----------
     40250

[EDITED by LF: applied [code] instead of <code> tags]
[Edited by MC: split lines]

[Updated on: Tue, 30 December 2008 06:28] by Moderator

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378419 is a reply to message #378416]

Tue, 30 December 2008 06:23

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Put the definition of the view as it is in all_views and keep your lines in 80 character width.

Regards
Michel

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378431 is a reply to message #378288]

Tue, 30 December 2008 07:23

monalip
Messages: 6
Registered: December 2008

Junior Member

Here's the data from all_views -

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus icmadmin@icmnlsdb

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 30 13:18:14 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set line 80;
SQL> select * from all_views where owner like 'ICMADMIN' and view_name like 'ALL
_DOCUMENTS';

OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------

TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
--------------------------------------------------------------------------------

OID_TEXT_LENGTH
---------------
OID_TEXT
--------------------------------------------------------------------------------

VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SUPERVIEW_NAME
------------------------------
ICMADMIN                       ALL_DOCUMENTS                        10065

OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------

TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
--------------------------------------------------------------------------------

OID_TEXT_LENGTH
---------------
OID_TEXT
--------------------------------------------------------------------------------

VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SUPERVIEW_NAME
------------------------------
(select TO_NUMBER('110048') as typeid,documentid, comments, dealreviewnumber, do


OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------

TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
--------------------------------------------------------------------------------

OID_TEXT_LENGTH
---------------
OID_TEXT
--------------------------------------------------------------------------------

VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SUPERVIEW_NAME
------------------------------


OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------

TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
--------------------------------------------------------------------------------

OID_TEXT_LENGTH
---------------
OID_TEXT
--------------------------------------------------------------------------------

VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SUPERVIEW_NAME
------------------------------


OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------

TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
--------------------------------------------------------------------------------

OID_TEXT_LENGTH
---------------
OID_TEXT
--------------------------------------------------------------------------------

VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SUPERVIEW_NAME
------------------------------


OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------

TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
--------------------------------------------------------------------------------

OID_TEXT_LENGTH
---------------
OID_TEXT
--------------------------------------------------------------------------------

VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SUPERVIEW_NAME
------------------------------


OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------

TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
--------------------------------------------------------------------------------

OID_TEXT_LENGTH
---------------
OID_TEXT
--------------------------------------------------------------------------------

VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SUPERVIEW_NAME
------------------------------


OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------

TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
--------------------------------------------------------------------------------

OID_TEXT_LENGTH
---------------
OID_TEXT
--------------------------------------------------------------------------------

VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SUPERVIEW_NAME
------------------------------


OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------

TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
--------------------------------------------------------------------------------

OID_TEXT_LENGTH
---------------
OID_TEXT
--------------------------------------------------------------------------------

VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SUPERVIEW_NAME
------------------------------

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378433 is a reply to message #378431]

Tue, 30 December 2008 07:30

Littlefoot
Messages: 21761
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, perhaps you see *something*, but I see nothing but bunch of VIEW_THIS and TEXT_THATs. Could you format it in a manner that it would be readable? Who wants to search for information in that mess?

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378434 is a reply to message #378288]

Tue, 30 December 2008 07:39

monalip
Messages: 6
Registered: December 2008

Junior Member

Attached is a csv contining the data from all_views

Attachment: data.csv
(Size: 10.03KB, Downloaded 636 times)

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378440 is a reply to message #378434]

Tue, 30 December 2008 07:56

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Select ONLY text column, format it in a readable way and post it INLINE not in a file.
To do so: read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378451 is a reply to message #378288]

Tue, 30 December 2008 08:25

monalip
Messages: 6
Registered: December 2008

Junior Member

Here's the TEXT column -

(SELECT To_number('110048') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NotRequiredAtMarketDate,
        ReceivedFromMarketDate,
        ReceivedFromMarketUser,
        SentToMarketDate,
        SentToMarketUser,
        DateSigned,
        seCoreUpdatedDate,
        seCoreUpdatedUser
 FROM   Doc_Type_110048001)
UNION ALL
(SELECT To_number('110049') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NotRequiredAtMarketDate,
        ReceivedFromMarketDate,
        ReceivedFromMarketUser,
        SentToMarketDate,
        SentToMarketUser,
        DateSigned,
        seCoreUpdatedDate,
        seCoreUpd AtedUser
 FROM   Doc_Type_110049001)
UNION ALL
(SELECT To_number('110050') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NotRequiredAtMarketDate,
        ReceivedFromMarketDate,
        ReceivedFromMarketUser,
        SentToMarketDate,
        SentToMarketUser,
        DateSigned,
        seCoreUpdatedDate,
        seCoreUpdatedUser
 FROM   Doc_Type_110050001)
UNION ALL
(SELECT To_number('110051') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NotRequiredAtMarketDate,
        ReceivedFromMarketDate,
        ReceivedFromMarketUser,
        SentToMarketDate,
        SentToMarketUser,
        DateSigned,
        seCoreUpdatedDate,
        seCoreUpdatedUser
 FROM   Doc_Type_110051001)
UNION ALL
(SELECT To_number('110052') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NotRequiredAtMarketDate,
        ReceivedFromMarketDate,
        ReceivedFromMa rketUser,
        SentToMarketDate,
        SentToMarketUser,
        DateSigned,
        seCoreUpdatedDate,
        seCoreUpd AtedUser
 FROM   Doc_Type_110052001)
UNION ALL
(SELECT To_number('110053') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NotRequiredAtMarketDate,
        ReceivedFromMarketDate,
        ReceivedFromMa rketUser,
        SentToMarketDate,
        SentToMarketUser,
        DateSigned,
        seCoreUpdatedDate,
        seCoreUpd AtedUser
 FROM   Doc_Type_110053001)
UNION ALL
(SELECT To_number('110054') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NotRequiredAtMarketDate,
        ReceivedFromMarketDate,
        ReceivedFromMa rketUser,
        SentToMarketDate,
        SentToMarketUser,
        DateSigned,
        seCoreUpdatedDate,
        seCoreUpd AtedUser
 FROM   Doc_Type_110054001)
UNION ALL
(SELECT To_number('110055') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NotRequiredAtMarketDate,
        ReceivedFromMarketDate,
        ReceivedFromMa rketUser,
        SentToMarketDate,
        SentToMarketUser,
        DateSigned,
        seCoreUpdatedDate,
        seCoreUpd AtedUser
 FROM   Doc_Type_110055001)
UNION ALL
(SELECT To_number('110056') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NotRequiredAtMarketDate,
        ReceivedFromMarketDate,
        ReceivedFromMa rketUser,
        SentToMarketDate,
        SentToMarketUser,
        DateSigned,
        seCoreUpdatedDate,
        seCoreUpd AtedUser
 FROM   Doc_Type_110056001)
UNION ALL
(SELECT To_number('110057') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        NULL AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110057001)
UNION ALL
(SELECT To_number('110058') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        NULL AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110058001)
UNION ALL
(SELECT To_number('110059') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        NULL AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110059001)
UNION ALL
(SELECT To_number('110060') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        NULL AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110060001)
UNION ALL
(SELECT To_number('110061') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        NULL AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110061001)
UNION ALL
(SELECT To_number('110062') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        ull AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110062001)
UNION ALL
(SELECT To_number('110063') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        NULL AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110063001)
UNION ALL
(SELECT To_number('110064') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        NULL AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110064001)
UNION ALL
(SELECT To_number('110065') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        NULL AS ReceivedFromCamuDate,
        NULL AS seCoreUpdateDate,
        NULL AS SentToCamuDate,
        NULL AS SentToCamuUser,
        NULL AS ReceivedFromCamuUser,
        exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        NULL AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110065001)
UNION ALL
(SELECT To_number('110066') AS TypeId,
        DocumentId,
        Comments,
        DealReviewNumber,
        DocumentStatus,
        DateScanned,
        UserScanned,
        Maker,
        Checker,
        boId,
        Skid,
        Markets,
        FileName,
        Location,
        ReceivedFromCamuDate,
        seCoreUpdatedDate AS seCoreUpdateDate,
        SentToCamuDate,
        SentToCamuUser,
        ReceivedFromCamuUser,
        NULL AS exPiryDate,
        NULL AS NotRequiredAtMarketDate,
        NULL AS ReceivedFromMarketDate,
        NULL AS ReceivedFromMarketUser,
        NULL AS SentToMarketDate,
        NULL AS SentToMarketUser,
        NULL AS DateSigned,
        NULL AS seCoreUpdatedDate,
        NULL AS seCoreUpdatedUser
 FROM   Doc_Type_110066001)

[Updated on: Tue, 30 December 2008 08:28]

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378473 is a reply to message #378451]

Tue, 30 December 2008 09:56

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Are you sure you user has not:
- an object with same name
- a synonym with same name pointing on other thing
and there is no public synonym pointing on other thing?

Regards
Michel

Report message to a moderator

Re: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW [message #378485 is a reply to message #378288]

Tue, 30 December 2008 10:59

monalip
Messages: 6
Registered: December 2008

Junior Member

We are not using any synonyms in our system.
I have rechecked and did not find any synonymns owned by ICMADMIN. Also, there is only one object by name ALL_DOCUMENTS in the ALL_OBJECTS table owned by ICMADMIN.

Report message to a moderator

Previous Topic:	Urgent**Hiding tables from sys/system user
Next Topic:	Row level security

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 31 19:21:03 CDT 2023