I have a rather weak understanding of any of oracle's more advanced functionality but this should I think be possible. Say I have a table with the following schema: MyTable Id INTEGER, Col1 VARCHAR2(100), Col2 VARCHAR2(100) I would like to write an sproc with the following PROCEDURE InsertOrUpdateMyTable(p_id in integer, p_co...
I'm using sql*plus 9.2 on Oracle 10g enterprise. I have created some scripts that do basic inserts using parameters that I pass through the command prompt. It seemed logical that I should be able to run a bunch of inserts in a loop. So I tried the following: --begin DECLARE TYPE va_orgs IS TABLE OF nbr.lien_item.lien_item_name%type; ...
I have a number of tables that use the trigger/sequence column to simulate auto_increment on their primary keys which has worked great for some time. In order to speed the time necessary to perform regression testing against software that uses the db, I create control files using some sample data, and added running of these to the build...
Given a simple (id, description) table t1, such as id description -- ----------- 1 Alice 2 Bob 3 Carol 4 David 5 Erica 6 Fred And a parent-child relationship table t2, such as parent child ------ ----- 1 2 1 3 4 5 5 6 Oracle offers a way of traversing this as a tree with some custom syntax ex...
I have a SQL script that creates a package with a comment containing an ampersand (&). When I run the script from SQL Plus, I am prompted to enter a substitute value for the string starting with &. How do I disable this feature so that SQL Plus ignores the ampersand? ...
Is there a good user interface for authoring Oracle SQL Loader control files? PL/SQL Developer includes a "Text Importer" feature (that reminds one of the Excel import wizard) to import text files into tables, but uses a proprietary format instead of the SQL Loader format. Something like this for the SQL Loader format would be quite help...
Can anyone suggest a good way of detecting if a database is empty from Java (needs to support at least Microsoft SQL Server, Derby and Oracle)? By empty I mean in the state it would be if the database were freshly created with a new create database statement, though the check need not be 100% perfect if covers 99% of cases. My first th...
The age old question. Where should you put your business logic, in the database as stored procedures ( or packages ), or in the application/middle tier? And more importantly, Why? Assume database independence is not a goal. ...
I am looking for a free tool to load Excel data sheet into Oracle database. I tried oracle sql developer, but it keeps throwing NullPointerException. Any ideas? ...
I'm trying to run the following SQL statement in Oracle, and it takes ages to run: SELECT orderID FROM tasks WHERE orderID NOT IN (SELECT DISTINCT orderID FROM tasks WHERE engineer1 IS NOT NULL AND engineer2 IS NOT NULL) If I run just the sub-part that is in the IN clause, that runs very quickly in Oracle, i.e. SELECT DISTINCT orde...
I'm working on databases that have moving tables auto-generated by some obscure tools. By the way, we have to track information changes in the table via some triggers. And, of course, it occurs that some changes in the table structure broke some triggers, by removing a column or changing its type, for example. So, the question is: Is th...
I have a large ADO.Net dataset and two database schemas (Oracle) with different constraints. The dataset will work with either schema, but I want to be able to tell the dataset which schema to use (via connection string) at runtime. Is that even possible? ...
Table: UserId, Value, Date. I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle) Thank in advance! [Update:] Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that ...
Is there a difference in performance (in oracle) between Select * from Table1 T1 Inner Join Table2 T2 On T1.ID = T2.ID And Select * from Table1 T1, Table2 T2 Where T1.ID = T2.ID ? ...
In my tests I need to test what happens when an OracleException is thrown (due to a stored procedure failure). I am trying to setup Rhino Mocks to Expect.Call(....).Throw(new OracleException()); For whatever reason however, OracleException seems to be sealed with no public constructor. What can I do to test this? Edit: Here is exa...
According to select name from system_privilege_map System has been granted: SELECT ANY TABLE (and lots of other * ANY TABLES) Plainly running SQL>select * from the_table; select * from the_table; * ERROR at line 1: ORA-00942: table or view does not exist nets the given response. I can log in as that user and run the s...
I have a stored procedure that consists of a single select query used to insert into another table based on some minor math that is done to the arguments in the procedure. Can I generate the plan used for this query by referencing the procedure somehow, or do I have to copy and paste the query and create bind variables for the input par...
When I try to display the contents of a LOB (large object) column in SQL*Plus, it is truncated. How do I display the whole thing? ...
I have a file. I want to get its contents into a blob column in my oracle database or into a blob variable in my PL/SQL program. What is the best way to do that? ...
I've never used the Global Temporary Tables however I have some questions how they will work in a php environment. How is data shared: Assuming persistent connections to oracle through php using oci8. Is the data tied to a database id? is it done based on the Apache httpd demons? Or is each individual request unique? When is the data...