The following database view rounds the date back to the closest fiscal year (April 1st): CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS SELECT CASE WHEN to_number(to_char(SYSDATE, 'MM')) < 4 THEN to_date('1-APR-'||to_char(add_months(SYSDATE, -12), 'YYYY'), 'dd-MON-yyyy') ELSE to_date('1-APR-'||to_char(SYSDATE, 'YYYY'...
Hi, I have a Django1.1 project that works with a legacy MySQL db. I'm trying to migrate this project to Oracle (xe and 11g). We have two options for the migration: - Use SQL developer to create a migration sql script. - Use Django fixtures. The schema created with the sql script from sql developer doesn't match the schema created from ...
We've recently hit a snag where a trademark symbol is being copied from one Oracle database to another, but have had it come across as a '?'. We've tracked the issue to the destination database being configured with a character set of 'US7ASCII'. Unfortunately, rebuilding the database to address this is not something we can do at the p...
Hi, I have a global temp table which is set as delete on commit. How does it behave on concurrency issue? I mean what happens if another session wants to use that global temporary table? The answer will probably not be "they share the same data". Now, if my guess is correct :), is the table locked until the first connection commits, o...
This query gives an error: select ep, case when ob is null and b2b_ob is null then 'a' when ob is not null or b2b_ob is not null then 'b' else null end as type, sum(b2b_d + b2b_t - b2b_i) as sales from table where ... group by ep, type Error: ORA-00904: "TYPE": invalid identifier When I run it with group by ep, the...
I want to delete all the records where field name class="10010" from Table A and AentryId = BentryId from Table B. if i delete the entryId 12 which matches className=10010 from Table A and the same time that same id should delete from Table B also. Table A: AentryId className 12 10010 13 10011 14 10010 15 100...
This is a situation I'm generally facing while writing SQL queries. I think that writing the whole column (e.g. long case expressions, sum functions with long parameters) instead of aliases in GROUP BY expressions makes the query longer and less readable. Why doesn't Oracle SQL allow us to use the column aliases in GROUP BY clause? There...
I'm writing a tool to gather customer configuration information. One of the questions I want to answer, what OS is the customer database running on. I haven't found a generic way to find the OS with SQL and I can't create stored procedures on the customer's database. If there is a way, it's probably vendor specific. Suggestions? Thank...
I need to make the "WE8EBCDIC500" character set available in an Oracle XE installation (on my desktop) to import a mainframe-generated file. Running SELECT value FROM v$nls_valid_values WHERE parameter='CHARACTERSET' does not show this character set to be installed and I get KUP-03006: error initializing handle for character set WE8EBC...
Hi, I just wanted to know what you guys think about this. I have an app written in Visual Basic .Net as my front end and and Oracle 11g Standart database as the back-end. So I have like 20 pc's running this app locally. They're all inserting, updating, deleting data on this single database. I want to develop a solution in the case that ...
I need to update a row with a formula based on the largest value of two DATETIME columns. I would normally do this: GREATEST(date_one, date_two) However, both columns are allowed to be NULL. I need the greatest date even when the other is NULL (of course, I expect NULL when both are NULL) and GREATEST() returns NULL when one of the co...
We are getting this exception when we start our cluster (2 managed servers, 1 admin), we have deployed only the ears corresponding to the OBPM 10.3.1 SP1 in a weblogic 10.3. When the server cluster starts, one of the managed servers (the first to start) get overloaded and ran out of connections to the directory DB because of this repeat...
Hello, I am comparing queries my development and production database. They are both Oracle 9i, but almost every single query has a completely different execution plan depending on the database. All tables/indexes are the same, but the dev database has about 1/10th the rows for each table. On production, the query execution plan it pi...
What is the best way to assemble an SQL query with join conditions dynamically? I don't want to hard code the query for each different condition on a webpage or a set of webpages. Is it even feasible? I got as far as being able to assemble simple queries but i got stumped when i needed to insert join conditions, i.e. how to figure out d...
Im having a very strange problem, i have a complicated view that returns incorrect data when i query on a particular column. heres an example: select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy'; this returns the single result 'Testerson, Testy', 'N' however, if i use the query: select empna...
Hey, I did not create the database I'm working with, but I would like to see the details of a check constraint. I know a check constraint on a column is enforcing a set of specific values, and I'd like to know what those values are. For example, if a check constraint is enforcing the character 'Y' and 'N', I want to be able to query t...
In the case of MSFT SQL Server 08, it is: odbcCommand = new OdbcCommand("{call " + SP_NAME + " (?,?,?,?,?,?,?) }", odbcConn); When I try to do the same thing for Oracle, I get: OdbcException: ERROR [HYC00] [Oracle][ODBC]Optional feature not implemented. Feel free to ask for clarification, and please help. I am using .Net 3.5, SQL S...
Why does Oracle 10 R2 not allow use of notational parameters while calling functions in insert statements ? In my app, I'm calling a function in an insert statement. If use notational method of parameter passing, I get an ORA-00907: Missing right parenthesis error message INSERT INTO foo (a, b, c)...
According to wikipedia! http://en.wikipedia.org/wiki/ORDBMS IBM's DB2, Oracle database, and Microsoft SQL Server, make claims to support this technology and do so with varying degrees of success So, are these products true "ORDBMS" like PostgreSQL? or they are they are long way from it? can someone plz! point me to any ...
At the moment I'm trying to make a connection to a local server. Connecting via, say, TOAD works fine. When I try to connect using .NET I get ora-12154. Which puzzles me, since I'm using the connectionstring from my TNSNAMES.ora file: XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myPC)(PORT = 1521)) (CONNECT_DATA = ...