Search results for query: *

Hi everyone. I (like many others) used to frequent "Ask Tom" for much useful insight and assistance with all things oracle. The site has hitherto been nigh on unbeatable for evidence-based clear answers to sometimes murky questions. However, Tom hasn't answered a question for over three months...

Diego, We have a similar problem in my place of work. It was originally decided (against my advice) to store files in the operating system, instead of the database. This was supposed to be both easier and quicker but has in the end, turned out to be a liability. When it comes to DR and/or...

Crystal, I don't know anything about business objects universes, but since a function is a type of stored procedure, I don't understand the restriction. If you're receiving parameters in a stored procedure, then the universe must presumably be able to respond to the stored procedure - correct...

crystal, I believe that your approach to this in not the best. Whilst it is possible to use dynamic table names in queries, it's often extremely hard to debug anything. Can you give an over view of what you're trying to achieve - an English language narrative of what you're trying to...

er, um, impugn anyone? Regards T

Slice, When confronted with this sort of issue, I often try using SQL. PL/SQL is a great language, and I use it almost daily, but for sheer flat out speed, you just can't beat SQL. In the table in question have you considered creating a target column which is the replace of the source clob...

I suppose inspecting its parameters, dropping it and then recreating it with the new name and identical parameters is out of the question? Is it mandatory to rename, or will achieving the same effect as renaming suffice? Regards T

Dave, Can you post create table statements and insert statements so I can try to reproduce your issue? If not, then I suggest a divide and conquer approach. The second query has no join conditions in the from clause, so I'm guessing you're getting lots of unexpected records returned. I...

The most obvious one is to check that the user in question has select privilege on all the tables involved in the query. Regards T

but don't let it go unsaid that the obvious answer is to use a stored procedure interface - that guarantees that SQL injection will never occur. Failing that, use sql statements with bind variables. Cut it off at source, don't try to catch it once it's happened. Regards T

Andy, In general, all code should be in a package. This enables encapsulation of the code, and improves security. It enables the declaration (in the package specification) of constants and variables with restricted scope. It also enables you to develop a library of associated functions and...

Hi there. I've just read through your code which seems to be quite good, so I only have a couple of general items to mention. First of all, if this isn't in a package, create one and put it in there. Second, in the package specification you should declare some constants to make the code more...

What are you trying to do with this stored procedure? It just runs a select statement. What is the error you are receiving? Regards T

Santa, the problem wasn't splitting a table or tables, it was splitting the entire database! I didn't split any table, I produced four separate databases with identical structures. Then the data from each functional part was moved out into its own database and worked on. At the end, four...

Trust you to pose a good question. The reason was that one database fulfilled four (in fact more) separate business functions. Due to bad design, there was no functional isolation in the database, and so I had to artificially create some, by splitting the data in to four sets. This enabled...

Santa, just to kick things around a bit, I disagree with your 'never changes' item to do with PK's. For all the reasons you mention (and more) I always use integer surrogate PK's. However, I advise developers that primary keys are subject to change without notice, and that they should code...

I concur with the word NEVER, by design they should never change. However, there are circumstances where this is required (e.g. when data from two different databases is to be merged), and that's when the use of a surrogate key is vital, as it enables keys to be changed without altering data...

zand, on the assumption that version numbers always increase, you simply don't need an isactive field. Just choose the max of the version number. Does the table store two version numbers, i.e. the old and the new. If so, it's even easier, just choose the second value. Regards T

Niall, first of all, and to be clear about sql scholar's response, I assumed that you would understand that since every foreign key references a PK (or in Oracle a column guaranteed to be unique) that this automatically included FK's. However, I didn't explicitly say that, so kudos to...

Niall, I've had to do precisely this in Oracle, but the method works in SQL Server. First of all, if you don't have integer surrogate primary keys then add them to all your tables, in both databases. Pick one database and multiply all its PK values by 10, and add 1. That means that every...