Paul Campbell

Ranch Hand
+ Follow
since Oct 06, 2007
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Paul Campbell

Yashwant Palkar wrote:We are passing following XML structure as VARCHAR2 parameter to procedure.


We want to insert above XML structure in following tables .
Customer (CustomerID is a primary key)
Address (CustomerID references to Customer.CustomerID)

Does Oracle provide any facility which for this?



http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/HowToLoadLargeXML.html
7 years ago
Oracle stores all DATE data types as a seven digit binary. The format mask you use on insert statement has nothing to do with how it is stored.

As Agador correctly states, you use to_char to convert your date to a string and choose your format mask for display.

If you don't explicitly do this yourself, Oracle will internally call a version of to_char to convert the binary data to a string using the default date format parameter stored in the nls_database_parameter table. Usually this is dd-mon-rr by default.

8 years ago
your stored procedure would need to except a string/varchar as a variable. Your list would be passed as a single string variable. You will need to parse the string to utilize your list.

Here is an example from Tom Kyte's Oracle Page
9 years ago
i suspect it may have to do with your attempting a multi-table update but only referencing one of your 4 tables in the where clause of your update statement statement. This would create a cartesian joining all rows in the other three tables to each row selected in your referenced job master table.

Gerbrand van Dieijen wrote:

Paul Campbell wrote:

Gerbrand van Dieijen wrote:

In any database, you don't actually delete a row, but you execute a DELETE FROM .. WHERE <where_predicate>, the <where_predicate> identifies the row, in case you want to delete a row.

Therefore, Oracle won't throw an exception when you delete a non-existing record/row in a table, it will just don't delete anything.



I'm sorry, but that isn't true. It can be caught.


Just to have the final word, I didn't you can't detect nothing is deleted - I just said with a DELETE FROM won't throw an exception. :-)

Using a stored procedure and plsql code is probably the most efficient solution - Hibernate should be able to handle stored procedures. Don't known about default JPA solutions.



Most of the time... but in the case of a uninitialized nested table or VARRAY... a COLLECTION_IS_NULL error will be thrown.
9 years ago

Gerbrand van Dieijen wrote:

In any database, you don't actually delete a row, but you execute a DELETE FROM .. WHERE <where_predicate>, the <where_predicate> identifies the row, in case you want to delete a row.

Therefore, Oracle won't throw an exception when you delete a non-existing record/row in a table, it will just don't delete anything.



I'm sorry, but that isn't true. It can be caught.

ben oliver wrote:Does Oracle 10g throw an exception when you try to delete a non-existing row ?

I tried in my application it does not cause any problem. But I am using java data mapping, so I don't know if the database itself would throw exception when it tries to delete a row but can't find the row ?



Yes, you could catch this in pl/sql stored procedure by taking advantage of the cursor attribute %NOTFOUND. You would need to do something like the following:



But if it is already deleted... I wouldn't worry about it.

You can also do a select to query for the data and perform exception handling for not returning any data (in PL/SQL you would do the same with an exception block catching the NO_DATA_FOUND exception).
9 years ago

Jan Stefaniuk wrote:Ok, thanks, now it's more clearer. So it's the matter of compile/run time. But anyway, why such a thing is not solved during the compile time? I mean the compiler can see that in fact b1 refers to the Cat object.
Cheers, Jan




this is because java implements "late binding" to support polymorphism. keep in mind this does not apply to static or final methods. it also does not apply to variables you reference directly.
Actually... the compiler doesn't know that the b1 is actually a Cat... it knows it to be of type Being (even though we know it to be a Cat)... so it requires you to enclose b1's suck method within a try/catch block because the compiler knows that Being.suck() throws an Exception.

At run time, Java knows that the b1 Being object is in fact a cat and will show "maaaow" for b1.suck() and "maaaow" for b2.suck().

I'm sure some of the others can give a more eloquent explanation... but that is what actually happens.
I believe your user is tied to existing stream... I will try to look at this tonight after I get in from work.
9 years ago
did you run the SQL trace and determine your constraints?
9 years ago

Raj Ohadi wrote:In oracle, if a field is clob, what's the oracle data type for this ? Is it called "LONG VARCHAR" or what ? Is it possible to define a column in a way that -- if the length is longer than a limit it will be clob; if it is less than that it is regular varchar ?



It is CLOB and it has a 4 gig size limit.

Do not use any Oracle long data types. They are both deprecated and difficult for you to work with after you store your data.

LONG VARCHAR for bit data is a DB2 data type and is not available to you in Oracle as a data type. The only difference between the DB2 Long Varchar and the DB2 VARCHAR is that it does not require you to assign a length to the column. Both data types have the same 32,700 character size limitation.
9 years ago

Jeanne Boyarsky wrote:Matt,
I can't say definitively, but I thought Oracle Text was for searching documents?



You're correct... document search and document classification is its primary purpose. Results can be returned from the LOB in html, xml, or the original document format.

The rendering of the document in its original format (word, pdf, etc.) is an artifact LOB storage and retrieval.
9 years ago

suresh sargar wrote:i want to find particular string from whole database where it is how many times present in database.

e.g i want to find string "xyz" in whole database

can anyone suggest me...?



select count(*)
from table
where mystring = 'xyz'
9 years ago