Skip to Main Content

SQL & PL/SQL

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!

Getting ORA-01403 in dba_segments query during a trigger

JP KrepsAug 20 2009 — edited Aug 20 2009
Hi, Everyone --

I need to set up a process which will automatically start Oracle auditing on a table when it's created on a particular tablespace.

I've created an "after create on database" DDL trigger which will fire when (ORA_DICT_OBJ_TYPE = 'TABLE').

In this trigger, I use the DBMS_STANDARD package trigger attribute functions in a query against the dba_segments view to check if the new
table has been created in the targeted tablespace. Here' the query:

SELECT DISTINCT s.tablespace_name
INTO tablespace_name
FROM sys.dba_segments s
WHERE s.owner = ORA_DICT_OBJ_OWNER
AND s.segment_name = ORA_DICT_OBJ_NAME
AND s.segment_type IN ('TABLE', 'TABLE PARTITION')
AND s.tablespace_name = target_tablespace_name;

The only purpose of this query is to make sure that that table is contained in the targeted tablespace identified by the "target_tablespace_name" constant variable used in the WHERE clause. If the query runs without error, then my trigger creates a DBMS_SCHEDULER job that executes an "audit" command to start Oracle auditing on the new table.

To test the trigger, within the trigger code, I put the query inside a PL/SQL block with a "WHEN OTHERS" exception clause that will send formatted error detail to the alert log. Then I successfully create a test table on the targeted tablespace. The trigger fires, but the output shows that the query in my trigger failed with an "ORA-01403: no data found" error, indicating that the dba_segments view contains no records showing that the new table is on the targeted tablespace.

But when, in an already opened PL/SQL session, I immediately run the same query (with the appropriate literals in place of the trigger attribute functions) then the query works with the name of the tablespace returned as expected!

I tried an experiment with the trigger running the same query against the dba_tables view instead of the dba_segments view. It worked without error. The only problem is that if your create a partitioned table, the "tablespace_name" column in the dba_table view is NULL (which makes sense when the various table partitions can be contained in different tablepsaces). So, unfortunately, in the case of creating partitioned tables, the purpose of the query would be defeated.

Why does the trigger work when it queries the dba_tables view, but fails when it queries the dba_segments view? Is there a timing issue which causes a lag between the time the table is created and the time that the dba_segments view is updated? Would the trigger fire inside this time lag, thus causing the dba_segments query to fail? Or is there another explanation?

Thanks in advance for any advice you can give me!

Comments

843859
Blob is the datatype you use as database column type to store binary data. You use PreparedStatement#setBinaryStream() to set it in a SQL query and you use ResultSet#getBinaryStream() to obtain it from the SQL result.
796254
i'd recommend storing the path to a file in the database and putting the images themselves on the file system.

%
843859
thank you for your kind answer.
So I did a mistake: 'do not exists a BinaryStream column type!!!!
....and the statement: setBlob(), setBinaryStream() both work with the only Blob colum type...
If I good unsertood, you recomended me to use the setBinaryStream() statement with a blob column, saving
before the file on the file system, and not to store it directly in the database...
Of course, in this case the program have also the duty of saving and the deleting the files on the hard disk.

Could you you show me a sinthetic code example about the sintax to use to indicate the saved file inside the setBinaryStream() statement ?


------
About the other solution: (to use resultSet.setBlob() statement).
(I saw it is used too, I think with java - byte[] type to store directly binary informations in the database).
I could do it trasforming the image in array of byte and store it directly in the database.
It should be possible to do it ?

Perhaps yes, but you didn't suggest me to do it..

-------
Excuse me if I asked you again ...but I would have a complete understanding of the problem....

thank you very much
regards
tonyMrsangelo
843859
Duffymo rather meant that the preferred approach is to write the file to the server's disk system using java.io API and to store its path in the database as a simple varchar field (use setString() and getString() to do that).
843859
thank you to answering me,

as I said, I don't have any experience working with Blob;
I got some information about this on the forum but no example is right for my purpose.
 
"I am working with a standalone application where a dentist, using a camera, can save images and see them again later or delete them too." 
I need to store Blob in the same way that is used to do with string, integer
or float type..., but as I understand to store Blobs it is not the same as store
on a database primitive type of data.
 
I should like have some general information how to do it before to write a 
complex as well as wrong code.. 
To work in this way gives very much disappointment ... :-).

thank you very much
regards

tonyMrsangelo
843859
As said before, use PreparedStatement#setBinaryStream().

If your problem is rather that you don't understand how to use PreparedStatement, then start reading its section in the JDBC tutorial here at Sun.com.
843859
thank you for answering me ,

I am able to use PreparedStatement(), but I should like to know what parameter put inside
the function :
preparedStatement.setBinaryStream(1 , xxx);

What is xxx in my case ??? ....

I understand that, perhaps, I ask a stupid thing ... but I am confuse...

I yet am thinking if I have to store the file on the hard disk passing a link
of the his path to the database..., or if I have to store really the stream inside the database.

in the docomentation I read:
By default drivers implement Blob using an SQL locator(BLOB), which means that a Blob object 
contains a logical pointer to the SQL BLOB data rather than the data itself.
Really I am not sure what this exactly can means and I see they are more than one way to store big objects..

I excuse if I am bothering you but I would not try wrong, ...doing it with database is complex for understand possible errors....

thank you
regards
843859
Just read the API docs whenever you want a clear explanation of the purpose of the class, what methods it all provides and how to use them all.

[http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html].
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 17 2009
Added on Aug 20 2009
4 comments
887 views