I have an application that records activity in a table (Oracle 10g). The logging records should be kept for at least 30 days. I expect about 20 million rows to be added to this table every month. The DBA suggested that the table be split in partitions containing one week of data. The weekly maintenance script would then delete the oldes...
Hi, 1st disclaimers: I'm not a programmer, never was had never been taught "higher" math despite the upper statements sometimes I have to work with SQL. Now I need to create a view from a select of my colleagues (who had used four unions looked like he do not know how to use or in the where part...), and now I'm here. Is there a si...
We have a project where data is written to a logging table. Now, when investigating a problem, it would be useful to query for the problem row, and also get the surrounding rows, so that we can easily see what led up to the problem. The log table had a timestamp field, so we can order by that. Essentially I want something like "grep -C"...
Hi. I'm trying to do something and I'm not sure how to do it. I have some data like this: WITH a AS (SELECT theid, thename, thetimestamp FROM mytable) SELECT thename, TRUNC (thetimestamp, 'HH24'), COUNT (theid) FROM a group by thename,trunc(thetimestamp,'HH24') ORDER BY COUNT (theid) desc) which returns me the count grouped by...
Hi, we have a table in an Oracle Database which contains a column with the type Char(3 Byte). Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn't work: OracleCommand command = null; OracleDataReader dataReader = null; string sql = "select * from TEST_TABLE where COLUMN_1 = :COLUMN_1"; try {...
We're discussing the performance impact of putting a common function/procedure in a separate package or using a local copy in each package. My thinking is that it would be cleaner to have the common code in a package, but others worry about the performance overhead. Thoughts/experiences? ...
Hello, I'm studying databases and am currently working on a object-relational DB project and I've encountered a small problem with the number of possible constraints in an object table. I'm using "Database Systems: The Complete Book" by Hector Garcia-Molina (and other authors) as a reference and there's a general SQL example like this: ...
I just came across NULL values in NOT-NULL fields in our test database. How could they get there? I know that NOT-NULL constraints can be altered with NOVALIDATE clause, but that would change table's last_ddl_time in USER_OBJECTS. And that time is less than the date that those records were created. Is there something else I'm overlooking...
I'm doing maintenance work on an existing Rails site and am having some problems stemming from many-to-many associations. It looks like the site was initially built using has_and_belongs_to_many for a few relationships that have since gotten more complicated in the business logic, so I need to use has_many :through instead to support ad...
I need to be able to run an Oracle query which goes to insert a number of rows, but it also checks to see if a primary key exists and if it does, then it skips that insert. Something like: INSERT ALL IF NOT EXISTS( SELECT 1 WHERE fo.primary_key='bar' ) ( INSERT INTO schema.myFoo fo ( primary_key, value1, va...
I've got the following small Groovy script that just does a count of rows in the database for a specific date. import groovy.sql.Sql def today= new GregorianCalendar() def dateString = "${today.get(Calendar.MONTH)+1}/${today.get(Calendar.DAY_OF_MONTH)-1}/${today.get(Calendar.YEAR)}" def sql = Sql.newInstance("jdbc:oracle:thin:bc/bc@ne...
My question is very much like http://stackoverflow.com/questions/1068974/getting-the-return-value-of-a-pl-sql-function-via-hibernate I have a function which does some modifications internally and it returns a value. The original idea was to do something like this: protected Integer checkXXX(Long id, Long transId) throws Exception { ...
Hi everybody, I was wondering, is there a way to make a kind of one to one left outer join: I need a join that matches say table A with table B, for each record on table A it must search for its pair on table B, but there exists only ONE record that match that condition, so when it found its pair on B, it must stop and continue with th...
I have an existing application that I am working w/ and the customer has defined the table structure they would like for an audit log. It has the following columns: storeNo timeChanged user tableChanged fieldChanged BeforeValue AfterValue Usually I just have simple audit columns on each table that provide a userChanged, and tim...
i have one question. I have a accttype (varchar) field in t_data table. I have different length acct numbers in that field. like few are 15 digit and few are 13 digit. I just want to know how many are there 13 digit acct no and how many are there 15 digit acct number and list them separately. can any one write sql query for that.Please....
Hi, Given three tables: a car table, an extras table and a link table, something like: table_car --------- int car_id string make string model table_extras ------------ int extra_id string extra table_car_extras_link --------------------- int car_id int extra_id I'd like to write a PL/SQL stored proc that returns data in the follow...
I have written the following querry to get the last executed SQL statement in the oracle database for a particular session. The SQL text does not contain the actual value of the bind variables. How to get the bind variable values along with the SQL text. SELECT * FROM v$SQLTEXT_WITH_NEWLINES WHERE address = (SELECT prev_sql_addr FROM...
Hi, I have a column called THE_VALUE in a table TABLE_A, that holds data similar to the following, i.e a few sample rows might be: tom:harry, sally, jeff state(vic,nsw), england, qwerty(aaa,bbb, cccc):qaz What I need to do to update this column using Oracle 10g sql and replace all commas, except the ones within the brackets with a co...
I have a master database that is used for OLTP type applications. At the moment we have individual table replication for many tables to another database that is used as a reporting instance. Soon, we are moving to a Data Guard Logical Standby database. What I cannot seem to track down from google is whether I can change the physical s...
I am using NHibernate against a legacy database which uses Char column types (fixed Strings). I am mapping the char columns to strings in properties. Currently my criteria queries are failing if the value against which i am querying is not padded with spaces such that the length of the string is equal to the char column length. I dont wa...