Add Currency Sign £, $ to certain fields ORACLE
Hi Geniuses I want to display the dollar or pound sign in my fields that hold job_salary any one knows? how to ...
oracle
Oracle analytic functions for "the attribute from the row with the max date"
I'm refactoring a colleague's code, and I have several cases where he's using a cursor to get "the latest row that matches some predicate": His technique is to write the join as a cursor, order it by the date field descending, open the cursor, get the first row, and close the cursor. This requires calling a cursor for each row of the r...
Calculate Percentage help
Hi this code gives me employee salaries and manager salaries. SELECT E.EMP_FNAME AS MANAGER, E.EMP_SALARY, D.DEPT_NO, A.EMP_FNAME AS EMPLOYEE, A.EMP_SALARY FROM EMPLOYEE E, EMPLOYEE A, DEPARTMENT D WHERE E.EMP_NIN = A.EMP_MANAGER AND A.EMP_MANAGER = D.EMP_MANAGER; How can i only show the employees that have a salary within 10% of...
Enforcing business rules using a Procedure in Oracle
How do you write a procedure which shows that one field's value cannot be higher than another field's value, in terms of numbers. Say. an employee'a salary can't be higher than his manager's salary. I've never done one before ...
Oracle Schema Design: Seperate Schema with I/O Overhead?
We are designing database schema for a new system based on Oracle 11gR1. We have identified a main schema which would have close to 100 tables, these will be accessed from the front end Java application. We have a requirement to audit the values which got changed in close to 50 tables, this has to be done every row. Which means, it is...
Oracle guid to other table
At the moment I'm writing a small conversion program, it will convert the primary key strategy to the using of GUIDs in stead of integers. This is a simple client induced requirement and I can't change that. I've added a substitute pk candidate of the RAW(16) to every table in the database and filled each record with a SYS_GUID(). I di...
Refactoring PL/SQL triggers - extract procedures
Hello, we have application where database contains large parts of business logic in triggers, with a update subsequently firing triggers on several other tables. I want to refactor the mess and wanted to start by extracting procedures from triggers, but can't find any reliable tool to do this. Using "Extract procedure" in both SQL Devel...
advanced select in Stored Procedure
Hey i got this Table: CREATE TABLE Test_Table ( old_val VARCHAR2(3), new_val VARCHAR2(3), Updflag NUMBER, WorkNo NUMBER ); and this is in my Table: INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('1',' 20',0,0); INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('2',' 20',0,0); I...
plsql show query
Hi all, I have a small problem using oracle pl sql. I have a sql file with some cursor, etc, and the treatement fail but with no details. I have an idea about the problem (a function with parameters) but I would like to see the parameter for each call, to be able to debug, to see exactly with wich parameter fail. This is the message: D...
oracle drop index if exits
How do you drop an index only if it exists? It seems simple but I did found anything on the net. The idea is to drop it only if it exists, because if not, I will have an error and my process stops. I found this to find if the index exists: select index_name from user_indexes where table_name = 'myTable' and index_name='myIndexName' ...
Including the SRID in SQLLDR file exported from Intergraph Geomedia
The company I work for uses Intergraph Geomedia, and we have some data in an Access database using Geomedia's native geometry stored in a blob format. Geomedia has an export capability that outputs to a SQLLDR format for Oracle that I'd like to use, but I can't get it to include the SRID in the loader file. It just puts a space. Anyon...
Simple Oracle File repository with folder hierarchy
I have an application that stores large amount of files (XML and binary) in folder hierarchies. Currently the main method is storing them in file system or using a legacy CMS, which we want to get rid of. The CMS supports Oracle and a customer wants to keep the files in Oracle because of enterprise policies (backup etc.) The question i...
What Date Format Should I Send When Using Oracle.DataAcess.
Converting from usind Micorsofts Syste.Data.OracleClient to what I believe is called Oracles ODT (Oracle.DataAccess 10.2.0.100). When I try and send a date I get this error "ORA-1858: a non-numeric character was found where a numeric was expected". This code worked great using System.Data.OracleClient. cmd.Parameters.Add(New OraclePar...
Oracle - timed sampling from v$session_longops
I am trying to track performance on some procedures that run too slow (and seem to keep getting slower). I am using v$session_longops to track how much work has been done, and I have a query (sofar/((v$session_longops.LAST_UPDATE_TIME-v$session_longops.start_time)*24*60*60)) that tells me the rate at which work is being done. What I'd l...
Porting join from Oracle to Postgres
INSERT INTO MISSION_OBJECTIVE( MSN_INT_ID, MO_INT_ID, MO_MSN_CLASS_NM, MO_MSN_CLASS_CD, MO_MSN_TYPE, MO_PRIORITY, MO_COMMENT, MO_START_DT, MO_END_DT, ASP_AIRSPACE_NM, MO_OBJ_LOCATION, MO_ALO_LEG_ID, MO_ALO_ARRIVE_LOC) SELECT '1025', '1', 'AIRDROP', 'ADP', 'LAPES', NULL, COALESCE( NULL, ' '), TO_TIMESTAMP( '1002260900', 'YYMMDDHH24MI'), T...
Copying 6000 tables and data from sqlserver to oracle ==> fastest method?
i need to copy the tables and data (about 5 yrs data, 6200 tables) stored in sqlserver, i am using datastage and odbc connection to connect and datstage automatically creates the table with data, but its taking 2-3 hours per table as tables are very large(0.5 gig, 300+columns and about 400k rows). How can i achieve this the fastes as at...
Rails offen lost connection to oracle database
I sometimes got this error from my log/production.log file: #<ActiveRecord::StatementInvalid: OCIError: ORA-03114: not connected to ORACLE: select decode(table_name,upper(table_name),lower(table_name),table_name) name from all_tables where owner = sys_context('userenv','session_user')>, How to solve this? How to rescue this and reconn...
How to convert number(16,10) to date in oracle
Hi, I'm trying to read the Borland Starteam application oracle database and I noticed that they represent their date as a number(16,10) column. I think it is not timestamp or epoch. For instance, I have the number: 37137.4347569444, how can I read it as date? I saw that the database has a stored procedure, CONVERT_DATE: CREATE OR REPL...
Membership.ValidateUser always returns false after upgrade to VS 2010 / .NET 4.0
Not sure whether this pertains to VS 2010 or to the upgraded framework, but... we are using the Oracle membership provider to authenticate users. Prior to the upgrade everything worked fine, but now Membership.ValidateUser(user, password) returns false despite valid credentials. There is no exception thrown, so it's hard to determine wha...
What is the correct SQL for a trigger to copy a record to an identical table?
I have two tables TABLE1 and TABLE2 with identical structures. I need a trigger to copy a record after insert from TABLE1 to TABLE2. What's the proper SQL for this? ...