A co-worker just came to me with a puzzling SQL query: (essentially) SELECT LEAST(id) FROM tableA A, tableB B WHERE a.name = b.name(+) The result set returned lists three numbers however: LEAST(id) -------------- 621 644 689 (all being IDs that meet the query as if it lacked the LEAST function all together) Why? =) ...
hi, I want to use the raise_application_error-procedure to stop the login process. I wrote a trigger, that checks the TERMINAL String, if it is right (I know that isn't realy secure, but at first, it is enough) So the Trigger works fine and does what i want, but the raise_application_error causes an rollback and sends not the exception t...
Hi, has anyone successfully compiled a pro*fortran program on windows that connects to Oracle 10g? I can only seem to find information that suggests that pre-compilation of embedded sql is possible against Oracle 8i and earlier. If you have done this please can you post any links you have for compilers, oracle downloads/articles etc a...
I need to add timezone information to a db table with user maintained locations. The data will be accessed mostly from Java code but there is also some PL/SQL and Win32 (Delphi) code which needs to understand the timezone information. It seems straight forward to use the id from java.util.TimeZone. Java can easily convert that (obviousl...
Shortly before saving a DateTime to the datebase I'm casting a DateTime into an OracleTimeStampTZ. This converts the DateTime into an OracleTimeStampTZ in the current timezone, which is cool, that's what I want. Except the timezone is represented as a time off set (+13, since I'm in NZ). Now I'm a little afraid that this is going to bit...
I am trying to create a Basic pay (BP) table with CREATE TABLE bp ( bpid VARCHAR(5), FOREIGN KEY (bpid) REFERENCES designation(desigid), upperlimit DECIMAL(10,2) NOT NULL, lowerlimit DECIMAL(10,2) NOT NULL, increment DECIMAL(10,2) NOT NULL CONSTRAINT llvalid CHECK (upperlimit > lowerlimit) ); As yo...
I'm new to oracle and having a problem with one of my SQL Queries. There are 2 Users: User1 and User2: Tab1 Tab2 -------- -------- EmpNo EmpNo EmpName EmpName ContactNo Salary Location User2 has all privileges in User1.Tab1, and there is no f...
Using the Oracle to_char(number) function, is it possible to append ascii characters to the returned string? Specifically, I need to add a percentage character to the returned string. "select to_char(89.2244, '999G999G999G999G990D00') from dual" --> returns "89.22". I need a format pattern that returns "89.22%". I am using this throug...
I was looking to explore the spatial capabilities of PostGIS or SQL Server 2008. Maybe Oracle as well. I was wondering if there are any good tutorials out there exploring the spatial capabilities of these database in particular or of this type of capability in general. Does anyone know of any? Book recommendations would work as well. ...
I need to find the centroid of a polygon in Oracle Spatial that exists inside of the polygon itself. I cannot use the sdo_geom.sdo_centroid method because this returns a point outside of the shape if the shape resembles a horseshoe. I found sdo_geom.sdo_pointonsurface method, but it returns a point that is just on the edge of the pol...
In oracle, is the named timezone always stored? I have been testing this column within our system, and in some places the timestamp is shown as: 26-FEB-09 11.36.25.390713 AM +13:00 but other times it's: 26-FEB-09 11.36.25.390713 AM Pacific/Auckland If the value is being stored as the former, does that mean the actual timezone is n...
I am trying to print a date in a select statement, but I need to add a letter to the output: to_char(date_updated, 'YYYY-MM-DDTHH:mm:ss') Oracle does not like the T. I just want the T to be output like the colons and the dashes. Can I escape it with a backslash or something? ...
(Database: Oracle 10G R2) It takes 1 minute to insert 100,000 records into a table. But if the table already contains some records (400K), then it takes 4 minutes and 12 seconds; also CPU-wait jumps up and “Free Buffer Waits” become really high (from dbconsole). Do you know what’s happing here? Is this because of frequent table extents...
I've heard that it's a good practice to define your records in PL/SQL by using the %ROWTYPE attribute. This saves typing and allows your package to continue functioning even when a column is added or deleted. (Correct me if I'm wrong!) However, when I am fetching from a cursor that involves a join, I find that I have to fetch into a pro...
I mean: Table PHONE_CODES: ID CODE_NAME PHONE_CODE 1 USA 8101 2 USA 8102 3 PERU 8103 4 PERU_MOB 81031 5 PERU_MOB 81032 And I want via select to get something like this: CODE_NAME ZONE_CODES USA 8101; 8102; PERU 8103 PERU_MOB 81031; 81032; I could get it via the functio...
I want to add two numbers together but when one of those numbers is null then the result is null. Is there a way around this. I could simply do it in the code but I would rather have it done in the query. This is a oracle database. The table structure hours_t type craft regular overtime A 1 5 ...
Hello everyone, Working on improving performance of our decision center, one of the bottlenecks we identify is the DB. So I wonder, does oracle compiles an Execution Plan for it's views? Lets hypothetically assume I have a defined query being used 10000 times during a request. The query looks something like : select A, B, C from aTb...
Hello there, oracle experts. My client of mine wants to deploy an application that has to hold credit card numbers in a database. The client is obviously concerned with security. We are particularly concerend with one painful issue. How can we make sure that only authorized users with a 'business need to know' are allowed to access th...
I want to make an Oracle function to remove 'garbage' from user input values, but there's also a requirement that users may enter Unicode text which I'm supposed to leave as is. REGEXP_REPLACE (search_text, '[^0-9A-Za-z]', '') takes care of non-Unicode, how can I check that varchar2 value contains Unicode characters? Looks like I could...
I can call an ORACLE stored procedure through OCI in a C program by constructing the SQL command for the command, here's a brief snippet from my code: /* build sql statement calling stored procedure */ strcpy ( sql_stmt, "call get_tab_info(:x)" ); rc = OCIStmtPrepare(p_sql, p_err, sql_stmt, (ub4) strlen (sql_...