CLOB through view

[dragot]

Sorry for my bad english.
I have some trouble. Situation:
I have two schemas A and B. In schema A I have table T and in this table is clob field. Schema B doesn't have any right to the A.T . Then I create view V on table T and grant all privileges on view V to schema B. And then when I try to change the entry in the clob field I get message: TABLE OR VIEW DOESN'T EXIST (all other data type fields can be changed, e.g. varchar, etc.).
When I try the same using SQL statement using e.g. SQLWorkSheet and not the OracleDataSet component, it works fine.
When I grant all priveleges to the table T on schema B (all other things staying unchanged) it works too (but it is not convenient for my situation).
Can anyone please give me the solution.

 

[Marco Kalter]

How do you perform this update in SQLWorksheet? Probably by supplying a string literal, which is limited to 4KB (or 32KB, I'm not sure).

The TOracleDataSet first creates an empty CLOB, and subsequently writes the data through a TLOBLocator. This requires privileges on the underlying table, where the CLOB column is defined. These extra privileges are the solution.

------------------
Marco Kalter
Allround Automations

 

[dragot]

Originally posted by mkalter:
How do you perform this update in SQLWorksheet? Probably by supplying a string literal, which is limited to 4KB (or 32KB, I'm not sure).

The TOracleDataSet first creates an empty CLOB, and subsequently writes the data through a TLOBLocator. This requires privileges on the underlying table, where the CLOB column is defined. These extra privileges are the solution.

But I need the user of schema B not to see all rows and also to have privileges just to some of them and not to the whole table T. That's why I have to use view (or do you see any other possibility?).
I still do not understand why it works when I use the SQL statement and using OracleDataSet component I get only Error message.

 

[Marco Kalter]

If you are using a string literal in SQL*Plus, then is simply passes through the view and is converted to a CLOB on the server. The same statement will work in a TOracleQuery and TOracleDataSet. The size is limited though, so it's not useful for a general solution.

The next version of Direct Oracle Access will support temporary LOB's, which are new in Net8 8.1. Temporory LOB's should remove this restriction.

------------------
Marco Kalter
Allround Automations