Are circular references acceptable in database?
When are circular references acceptable in database? Theoretical and practical, any help is appreciated. ...
oracle
oracle query with multiple subqueries
tryng to select table rows and then use subqueries to generate single row list (item1,item2,item3) anyway sql: select username, concat(firstname || ' ', lastname) as name, email, phone, ( select ltrim(sys_connect_by_path(res, ', '), ', ') from ( select count(*) over() as cnt, row_number() over(orde...
Allowing oracle db login only to specific application?
We want to allow DB access (Oracle) to our users only through our own application - let's call it "ourTool.exe", installed locally on the users computers. Currently, the users must provide username/password whenever they start "ourTool". The provided password password gets decrypted and we use username/decrypted-password to finally log i...
trouble defining weakly defined ref cursor
Hi all, I'm attempting to write a stored proc that takes in a number, n, and returns the first n results for a given query, exclusively locking those n rows. I'm a little new to SQL and I'm having a bit of difficulty matching data types correctly. My package spec looks like this: PACKAGE package IS Type out_result_type is REF CURS...
PostgreSQL -> Oracle replication
I'm looking for a tool to export data from a PostgreSQL DB to an Oracle data warehouse. I'm really looking for a heterogenous DB replication tool, rather than an export->convert->import solution. Continuent Tungsten Replicator looks like it would do the job, but PostgreSQL support won't be ready for another couple months. Are there an...
Oracle EXECUTE IMMEDIATE with variable number of binds possible?
I need to use dynamic SQL execution on oracle where I do not know the exact number of bind variables used in the SQL before runtime. Is there a way to use a variable number of bind-arguments in the call to EXECUTE IMMEDIATE somehow? More specifically, I need to pass one parameter into the unknown SQL but I do not know how often it will...
How do I use CREATE OR REPLACE?
Am I correct in understanding that CREATE OR REPLACE basically means "if the object exists, drop it, then create it either way?" If so, what am I doing wrong? This works: CREATE TABLE foo (id NUMBER, title VARCHAR2(4000) DEFAULT 'Default Title') And this doesn't (ORA-00922: missing or invalid option): CREATE OR REPLACE TABLE foo ...
Oracle and SQLServer function evaluation in queries
Let's say I have a function call on a select or where clause in Oracle like this: select a, b, c, dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) from my_table A similar example can be constructed for MS SQLServer. What's the expected behavior in each case? Is the HASH function going to be called once for each row in the table, o...
How to find Current open Cursors in Oracle
What is the query to find the no. of current open cursors in an Oracle Instance? Also, what is the accuracy/update frequency of this data? I am using Oracle 10gR2 ...
can any body help me abt sql procedures for modifing a csv file from the contents of the table.
Hi i stuck at writing a procedure which have to update the csv file (or delete the old csv file and creating the new csv file with same name and data in table)based on the contents of the table.. Thanks in adv ...
abt oracle procedure agv and deleting the csv file from procedures of oracle
Hi can u give me the info abt how can we pass the arg to the oracle procedures how can i delete the csv file from oracle procedures ...
Customize Oracle AWR report
Oracle creates nicely HTML-formatted workload reports (AWR) that list things like instance statistics, wait events, the top ten heavy SQL statements (by number of executions, number of disk reads, and so on). Are there (easy) ways to customize this report? For example, I want the top 25 SQL, instead of the top ten. ...
Connect VB 6.0 application to Oracle
Hi, Currenly I am using Oracle 9i Lite Client to connect my VB 6.0 application to Oracle database. The Oracle 9i Lite Client is installed on all the user's machine. (around 200) The VB 6.0 application also needs to talk to SQL Server 2000 database I am trying to think for replacement of Oracle 9i Lite client so that I can bring down...
Unable to enlist in a distributed transaction with NHibernate
I'm seeing a problem in a unit test where Oracle is thrown an exception with the message "Unable to enlist in a distributed transaction". We're using ODP.net and NHibernate. The issue comes up after making a certain number of commits to the database inside nested transactions. Annoyingly, this is failing on the continuous integration ser...
Oracle Multiple Schemas Aggregate Real Time View
All, Looking for some guidance on an Oracle design decision I am currently trying to evaluate: The problem I have data in three separate schemas on the same oracle db server. I am looking to build an application that will show data from all three schemas, however the data that is shown will be based on real time sorting and prioritis...
How to populate varray multiple times
I have a script that uses one VARRAY multiple times. But, I can't seem to figure out how to reset the VARRAY after looping through it once. I wrote the following basic script to help me troubleshoot: DECLARE TYPE multi_show_id_type IS VARRAY (60) OF VARCHAR2 (10); multi_show_id multi_show_id_type := multi_show_id_type (); coun...
Null value returned on Count Distinct (pl sql)
Preemptive apologies for the nonsensical table/column names on these queries. If you've ever worked with the DB backend of Remedy, you'll understand. I'm having a problem where a Count Distinct is returning a null value, when I suspect the actual value should be somewhere in the 20's (23, I believe). Below is a series of queries and t...
Hibernate findByCriteria strange behaviour
I have a simple method that just does two lines and tries to return all objects in an oracle database table: DetachedCriteria criteria = DetachedCriteria.forClass(Object.class); return (Collection)getHibernateTemplate().findByCriteria(criteria); However, I got an "ORA-01031: insufficient privileges" error. When I checked the logs for ...
How to construct this query in oracle?
Got table ABC with one column. A date column "created". So sample values are like; created 2009-06-18 13:56:00 2009-06-18 12:56:00 2009-06-17 14:02:00 2009-06-17 13:12:23 2009-06-16 10:02:10 I want to write a query so that the results are: count created 2 2009-06-18 2 2009-06-17 1 2009-06-16 Basically ...
how to check what platform a site is running?
what can i do to check what platform a site is running, whether it's asp.net, and what kind of database engine it's using, and any other tools that are running? (given that it is not my site and i dont have access to it) ...