First off, I'm a total Oracle noob although I'm very familiar with SQL. I have a single cost column. I need to calculate the total cost, the percentage of the total cost, and then a running sum of the percentages. I'm having trouble with the running sum of percentages because the only way I can think to do this uses nested SUM functions,...
We have an Oracle database that uses Oracle Workspace Manager (OWM) to version several of our tables. Currently we are supporting 4 workspaces in our system. We have now identified the need to change our database schema by introducing a new table to help create a level of abstraction. The current schema looks like the following: ...
Hi, Please help me to achieve the below problem: I am having one "view" in the Oracle database,I want the output of that view and store that output in the .txt file on some other folder in UNIX box. The output which is generated from view is a report and I want to save that report in .txt format in one folder on UNIX box.Oracle is prese...
When I give the command to drop a user i.e. DROP USER 'username' cascade, Does it deletes all the tablespace and datafiles used by that particular user. If not, what is the command to delete all the tablespace / datafiles / disk space that were used by that particular user. ...
Hi, I have several tables where a field is for priority (1 to 5). Problem here is that different projects have been using 5 as highest and some 1 for highest and I going to harmonize this. My easy option is to create a temp table and copy the data over and switch as this table: 1 -> 5 2 -> 4 3 -> 3 4 -> 2 5 -> 1 I'm not that good wit...
hi We have a rac database system. I add a new datafile but I did not choose Oracle Managed File from toad, unfortunately :( So as I understand now, it created datafile in one node. So any session which connects to other node can not find the datafile and gives error message. So how can I move datafile to RAC shared area and change type ...
Helo, I am using oracle 10g and the output coming when SELECT command is in a zig zag format. How I can arrange coulumns in correct order? Is there any command? ...
I am looking for good resources on Oracle Stored Procedure geared towards beginners. I tried the Dev Shed Article and one from Oracle Documentation site but they did not meet my needs. The one form Oracle Documentation site had the overhead of Java example. I tried the Dev Shed one but I keep getting invalid SQL error when I try their ex...
How to resolve this error. I am using Oracle 10g. Do let me know if any further information is requried. ...
I would like to have a block in an Oracle 10g form that would show all of the PDF files in a particular folder on the user's C drive. The user should at least be able to double-click on the file to open it, and ideally be able to delete and rename the files, too. The list of files should show the filename (obviously) as well as the siz...
I suppose I don't necessarily need an actual reference book per se, but I tend to learn as I go. Thus, it would be nice to have some kind of terse but readable book that I can read in chunks either if I need help remembering the syntax of a particular query or if I want to learn about particular database features one at a time. It seem...
I need some pointers on how to diagnose and fix this problem. I don't know if this is a simple server setup problem or an application design problem (or both). Once or twice every few months this Oracle XE database reports ORA-4031 errors. It doesn't point to any particular part of the sga consistently. A recent example is: ORA-04031: ...
Here is the definition of the stored procedure: CREATE OR REPLACE PROCEDURE usp_dropTable(schema VARCHAR, tblToDrop VARCHAR) IS BEGIN DECLARE v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM all_tables WHERE owner = schema AND table_name = tblToDrop; IF v_cnt > 0 THEN EXECUTE IMMEDIAT...
I've been tasked with running the unit tests on a storm backend for oracle so that we can see if the backend is of sufficient quality to use in production. One problem that I'm running into is that I'm getting ORA-08177 (can't serialize access for this transaction) if I connect in serializable mode. The problem goes away when I use rea...
Is there a way to easily/elegantly update multiple columns in a table with record values from a query in Oracle 10g? I have a table (call it Source) which has for simplicities sake, 2 columns, ID and KEY. ID Key ---- ---- 1 1000 2 1000 3 5000 4 1000 .. 101 8000 102 9000 103 7000 104 ...
Hi all, I was wondering if anyone could help me with parsing a full name field. I would like to separate it into lastname, firstname, middle initial, suffix. Here are some inputs for name followed by how I would like for them to be parsed. Parsed Stuff Begins Here------------------------------------- nam...
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 ...
All, I am very new to stored procedures in general but I am struggling especially with those in Oracle. I have created a very simple example of what I am trying to accomplish and I am still getting the same error with this simplified version. The example stored procedure is as follows: CREATE OR REPLACE PROCEDURE ashish_test AUTHID C...
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 ...
Is there any class similar to ProvidersHelper but not for web? I want to instantiate a collection of providers. Actually I'm using CodeSmith & Nettiers, and the db provider is Oracle. It generated a provider section to be added at app.config, and i dont know why it uses System.Web.Configuration classes or why at this section it says SQLC...