Hello there, I have optimized a complex Oracle statement using temporary table like this : original : SELECT data FROM table WHERE ..complex statement..; optimized (I can't use WITH keyword, because I deal with < Oracle9i) : CREATE GLOBAL TEMPORARY TABLE temptab (x NUMBER, y DATE) ON COMMIT DELETE ROWS; INSERT INTO temptab SELECT ...
I am attempting to more fully document our database packages as an API. What we would like is something like JavaDocs for PL/SQL. I have seen a couple tools out there (pldoc, plsqldoc) but would like to know from people who use them how they compare. ...
I have a basic tree structure of entities. The tree can be a maximum of 5 nodes deep, but may be N nodes wide. I have mapped this relationship in table similar to what is shown below: myID | myDescription | myParentID I am starting out with a known object, which could translate to having a starting "myID". Now I want to get all th...
Hi, I know that in Oracle I can get the generated id (or any other column) from an inserted row as an output parameter. Ex: insert into foo values('foo','bar') returning id into :myOutputParameter Is there a way to do the same, but using ExecuteScalar instead of ExecuteNonQuery? I don't want to use output parameters or stored proced...
Short version: How can I map two columns from table A and B if they both have a common identifier which in turn may have two values in column C Lets say: A --- 1 , 2 B --- ? , 3 C ----- 45, 2 45, 3 Using table C I know that id 2 and 3 belong to the same item ( 45 ) and thus "?" in table B should be 1. What query could do ...
I manage to identify duplicate records from two different databases: select * from taskperformance a, taskperformance@dm_prod b where a.activityin = b.activityin and a.completiondate = b.completiondate How can I delete duplicated records from b? I tried: delete taskperformance@dm_prod where exist ( select * from ...
I have two arrays of values like X,Y,Z and 1,2 There is a table A with two columns.I want to validate that in table A records with all the combination exists irrespective of duplicates. e.g. X 1 Y 1 Z 1 X 2 Y 2 Z 2 Thanks in advance! ...
I have a trigger to check for a single column in a table, when there is an update (AFTER UPDATE) in that column my trigger is called and then I call a stored procedure from within my trigger in order to perform some business logic coded in Java. So far, so good. Now things are getting more complicated, there is a new requirement that im...
I have an Oracle function GetEmployeeDetails which saves all the employee details into a temporary table TempEmployeeDetails table. I have to call the function followed by a select query on the temporary table. The function call succeeds but the select query throws the following error. "BEGIN :Output := MyPackage.GetEmployeeDetails(" ...
Hello i have a query to an Oracle System which involves a view which joins other tables by apliying an TO_NUMBER() to the tables primary key. If i do the query using TOAD the query is very fast (1 sec for 800 regs). If i do the same query in a java program by JDBC with a String literal (not a parametrized query), the time is good too. ...
On Oracle 10g we need to insert records from a view into a table to support a dumb client application that does not have sort or ORDER options itself. Is there any way to control the order in which our INSERT statement adds records to the destination table? ...
Here's the sample SQL: SELECT xml_data.field FROM (SELECT sys.XMLType(source_table.data).extract('//source_node/text()') AS field FROM source_table )xml_data GROUP BY to_clob(xml_data.field) The error only appears when I use GROUP BY to do a count. When it is removed I get the error ORA-22806: not an object or REF 22806. 00000 ...
I am using Oracle 11g client, with ODP.NET. I am trying to add conditional Transaction handling. Dim ds As New DataSet() Dim txn As OracleTransaction Dim _beginTransaction as Bolean = true Using conn As New OracleConnection(ConnString) Try conn.Open() If _beginTransaction Then ...
I have a legacy Alpha server with a RDB database. This db is replicated in a MySQL db with many bad php scripts that drops all the tables and takes everything from the Alpha. This works very slow and is becoming unmaintainable. Is there a better way to fix this than programming again the scripts? Anything like MySQL Migration Toolkit? ...
When using one of the various JDBC template methods I am confused on how to iterate/scroll over large result sets (which won't fit into memory). Even without a direct exposure of an Iterable interface I would at least expect instances of RowCallbackHandler to get called while the query is executing not after it's finished (or the heap ov...
I have a table with a lot of repeated data that I'd like to refactor as 3 tables. Current structure looks like: meeting_desc meeting_date topic_desc ... And the data in the current_table looks like: meeting1,2/3/2009,abc meeting1,2/3/2009,efg meeting1,2/3/2009,xyz meeting2,4/5/2009,aaa meeting2,4/5/2009,bbb I would like to create ...
I created a simple Oracle type: create or replace TYPE MY_TYPE AS OBJECT (ID NUMBER(30), NAME VARCHAR2(20)); Then, I created a second table type: create or replace TYPE MY_TYPE_TABLE AS TABLE OF MY_TYPE; Finally, I created a simply function: create or replace FUNCTION my_function(line_id IN NUMBER) RETURN MY_TYPE_TABLE AS retu...
I am sort of new with Oracle. We are having lot of issues. We use two main clients for running queries. SqlDeveloper (provided by Oracle) and PL/SQL Developer 7.1. We are trying to debug a stored procedure. Problems are:- There are some Stored Procedures that are compiled with somename.SPNAME meaning they are in a different schema...
What is the best way to randomize the time part for a DATE column, using Oracle 10g? For example, the date portion for the data was set as follows: UPDATE table_name SET column_ts = SYSDATE - 120 + MOD(ROWNUM, 35) I would like the time portion to have a different value for each row. ...
I am trying to fetch results from an oracle SP which is returning cursors. I have the following code which does not seem to work... declare VARIABLE csr1 REFCURSOR; declare VARIABLE csr2 REFCURSOR; declare VARIABLE csr3 REFCURSOR; declare VARIABLE csr4 REFCURSOR; declare VARIABLE csr5 REFCURSOR; EXEC getReportData('PUB',:csr1,:csr2,:cs...