In Oracle, what are the pros and cons of using materialized views, and of analytic workspaces? What are the best practices surrounding the use of these features? We have an OLTP system, but would also like to access summary information in reports and interactive decision support tools. ...
Okay. Forgive me, but I'm going to vent a little here. Visual Studio doesn't recognize my TNSnames for oracle so I can't connect to my database. I believe the problem is that I have multiple homes on my machine, one for me (In C:/app/{UserName}/product/11.1.0) and one general one which was already installed on my machine (In C:/oracle/pr...
There is a functionality in Oracle which permits to use a number in parentheses instead of naming a field to order a result. Example : this query order the result by the "id" column : select name, id from table order by (2) What is the name of this functionality and where can I can read documentation on it ? ...
Suddenly all my keyboard shortcuts in the "Run" menu are disabled and I have no idea why. They were working yesterday, but not today. I can click on the toolbar menu options to run these commands (Run, Trace, etc), but they key bindings don't work. I tried this suggestion and even went so far as to re-install it. Still no luck. Anyone ha...
I have the following block of PLSQL that succeeds when ln_length is 4000 characters or less but fails with "ORA-01460: unimplemented or unreasonable conversion requested" when ln_length is > 4000 characters. The block is: DECLARE ls_string VARCHAR2(32767); ls_temp VARCHAR2(32767); ln_length NUMBER := 4000; BEGIN ls_stri...
Please check out the following query. The SQL isn't as bad as it looks. Basically, we have a fact table and some simple joins to some dimension tables. Then we have a join to a derived table, given the alias ACCOUNTS-DIM-DEP SELECT dw_mgr.fa_trans_fct.period, dw_mgr.fa_trans_fct.asset_cost_company_code, dw_mgr.fa_tra...
Hi all. I have a comparison in Oracle for two numbers. 010 and 10. They are equal as long as numeric equality is concerned; however, I need to compare them as String. I tried to_char but it doesn't work. Are there any other functions which would let me do exact comparison of numeric value as string? ------------To clarify everyone's ...
Oracle IDE to visually = wizard (not writing sql) create views? any names? (eg like toad) SQL Server Management Studio = can create views visually for sql server what is same for oracle ...
I try to send an email using utl_smtp with Oracle including norwegian characters (å æ ø). The characters are stored and displayed correctly in the database otherwise, but shows up as question marks in the email. My database character set is WE8MSWIN1252 I have tried different Content-Type mime headers in the email including 'text/plain...
Given the following function: create or replace FUNCTION "GETADDRESSES" RETURN sys_refcursor IS address_cursor sys_refcursor; BEGIN OPEN address_cursor FOR SELECT * FROM Address; RETURN address_cursor; END; I would like to be able to make changes to this result set in Java and post the changes back to the data...
Hello , I am trying to connect to the existing db in oracle with fluentmapping . I got Mapping over CUstomer public CustomerMapping() { Not.LazyLoad(); Id(x => x.Cst_Recid).GeneratedBy.Increment() ; } and i am trying to create session public static ISessionFactory CreateSessionFactory() { retur...
I need to call a stored procedure in Oracle from an orchestration. I am running BizTalk 2006 (not R2). Which way is best? Are there others? BizTalk Adapters for Enterprise Applications BizTalk LOB Adapters Thanks in advance. ...
I'm new to working with analytic functions. DEPT EMP SALARY ---- ----- ------ 10 MARY 100000 10 JOHN 200000 10 SCOTT 300000 20 BOB 100000 20 BETTY 200000 30 ALAN 100000 30 TOM 200000 30 JEFF 300000 I want the department and employee with minimum salary. Results should look like: DEPT EMP SALARY ---- --...
I'm new to Oracle (I've been using MySQL mainly until now) so this might be a dumb question. But I have created this table (names are not english but ignore that, that is not important): CREATE TABLE Auta ( id_auto NUMBER(5) UNIQUE NOT NULL, typ CHAR(10), specifikacia_typu CHAR(15), SPZ CHAR(8), farba CHAR(20), datum_vyroby DATE, pocet_...
I have been asked to research the advantages/disadvantages the two application servers, but am new to the space and am having a terrible time finding an unbiased comparison of the two platforms. I understand that this is a broad question and I hate that I can't give a very specific use case (other than it will be an implementation in an...
What is the time complexity of a function such as count, sum, avg or any other of the built in "math"-functions in mysql, sql server, oracle and others? One would think that calling sum(myColumn) would be Linear? But count(1) isn't, how come and what are the Real time-complexity? In a perfect world I would want sum, avg and count to b...
I would like to know if there is a way to match people between two separate systems, using (mostly) SQL. We have two separate Oracle databases where people are stored. There is no link between the two (i.e. cannot join on person_id); this is intentional. I would like to create a query that checks to see if a given group of people from ...
Hello, I have a .Net webapp that is connecting to an Oracle backend. I have a base page which every page uses where I set my protected override void OnPreInit(EventArgs e) { System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("en-CA"); // Creating the DateTime Information specific t...
Here is my query: SELECT * FROM Auta WHERE SUBSTR(spz, 1, 2) = (SELECT SUBSTR(spz, 1, 2) FROM Auta WHERE typ = 'BMW' AND specifikacia_typu = 'Z1' LIMIT 1); And when I run it I get this error: ORA-00907: missing right parenthesis I'm getting a little desperate, I've already tried adding parentheses everywhere in the query and I sti...
Hey all, I need to use the value of one Select List to populate the value of a second select list, but the items in Select List number two are going to be from a completely different tables depending on what's selected in list number one. Is there a way I can conditionally populate the second list based on the values from the first? S...