Is a combination of SCJP, SCWCD and Oracle programmer certification useful for a fresher to get off campus placement? I am fresher B.E. (Computer Science) passed out from Thapar University, Patiala in India. It's one of the top 20 colleges for engineering in India. I got one offer from GlobalLogic Pvt. Limited on campus and one tempora...
Is there a SQL equivalent to #define? I am using NUMBER(7) for ID's in my DB and I'd like to do something like: #define ID NUMBER(7) Then I could use ID in the creation of my tables: CREATE table Person ( PersonID ID, Name VARCHAR2(31) ) This will allow me to easily change the type of all of my IDs if I realize I need more...
I have a table that looks like this: A 1 A 2 B 1 B 2 And I want to produce a result set that looks like this: A 1 2 B 1 2 Is there a SQL statement that will do this? I am using Oracle. Related questions: Returning multiple rows from a single row My question is close to the opposite of this question. Use LINQ to concatenate...
The tablespace in Oracle 10g is almost 100% used. Size (MB) = 571,768.0 Used (MB) = 571,534.0 I just deleted (and committed) thousands of records in a table that belongs to a schema associated with that tablespace. Surprisingly, no space was freed up according to the Tablespaces page on Enterprise Manager. Question: is there anything...
I am trying to call a stored procedure over a database link. The code looks something like this: declare symbol_cursor package_name.record_cursor; symbol_record package_name.record_name; begin symbol_cursor := package_name.function_name('argument'); loop fetch symbol_cursor into symbol_record; exit w...
I'm trying to load some data using sql loader. Here is the top of my control/data file: LOAD DATA INFILE * APPEND INTO TABLE economic_indicators FIELDS TERMINATED BY ',' (ASOF_DATE DATE 'DD-MON-YY', VALUE FLOAT EXTERNAL, STATE, SERIES_ID INTEGER EXTERNAL, CREATE_DATE DATE 'DD-MON-YYYY') BEGINDATA 01-Jan-79,AL,67.39940538,1,23-Jun-2009 ...
The CLOB is XML data that is > 8k (sometimes > 32k). Any suggestions? ...
I want to efficiently check if a table contains any rows that match <condition A> and do not match <condition B>, where the conditions are arbitrary. In Oracle, this almost works: select count(*) from dual where exists ( select * from people where (<condition A>) and not (<condition B>) ); -- returns zero if all rows that match <...
It seems that on some of the servers that we have, the cost of hash joins, group by's and order by's is too low compared to the actual cost. I.e. often execution plans with index range scans outperform the former, but on explain plan the cost shows up as higher. Some further notes: I already set *optimizer_index_cost_adj* to 20 and it...
Hi i have a "small" problem , iam using join to get some names for OS Architectures. SELECT "OSARCH".*, "OS".* FROM "OSARCH" INNER JOIN "OS" ON OSARCH.OSARCH_ID = OS.OSARCH_ID The result is something like this : OSARCH_ID OSARCH -- ------- 22 Powerpc 22 Powerpc 22 Powerpc 28 x86 28 x86...
There are many instances where we are not happy with the decisions that Oracle's cost-based-optimizer makes regarding the query execution plan. Using hints, less-than-straightforward query transformations, index reorganization and instance parameters we then try to coax it into doing what we think makes more sense. It is very much taking...
I have a java webapp which needs to upload files via http and then store them on the server. The files need to be associated with specific records in an Oracle database, so there will be a table in the database storing the reference to the associated record, and a reference to the file, along with other descriptive data such as title etc...
Does Oracle have built-in string character class constants (digits, letters, alphanum, upper, lower, etc)? My actual goal is to efficiently return only the digits [0-9] from an existing string. Unfortunately, we still use Oracle 9, so regular expressions are not an option here. Examples The field should contain zero to three letters...
Hi, Post Update: I have tracked down the problem at the command "ExecuteNonQuery". That's the one that fails during an update or hangs during an insert. Trying a simple example using plain ADO.NET and their transactions works perfect. Also... it works great on my local home computer connection an Oracle Express edition. Pointing it agai...
I'm trying to display a DATE field fetched from a DB2 instance. In Oracle I'd use something like: to_char(v_date, 'YYYY-MM-DD') What's the equivalent in AS400 DB2? ...
Package is very very basic. Loops through a cursor, and updates 2 values where the record_ids are equal. What's an appropriate unit test for this sort of procedure? I'm going to add some skeleton code because the answers so far, while good, tie to the crux of my issue here: What do I test? PROCEDURE set_shift_times_to_null( RETVAL OUT...
Ok, this seems simple but I can't find a solution to save my life. I am trying to do a very simple INSERT query on an Oracle DB. I can log into the DB in TOAD with the same credentials as I use in the code and run the INSERT with no problem, so as near as I can tell there are no permissions issues with the credentials and the query itsel...
Hi i am looking for a function same group_concat of mysql in oracle or some functionality ...
I'm attempting to detect duplicated/repeated values within a hierarchical table. Consider the following (slightly contrived) example: SELECT * FROM emp START WITH mgr IN (SELECT empno FROM emp WHERE ename = 'JONES' UNION ALL SELECT empno FROM emp WHERE ename = 'JONES') CONNECT BY PRIOR empno = mg...
I am encountering an interesting issue with an application that was migrated from Oracle 9i to 10g. Previously, we had a problem when a field contained double quotes since Oracle recordsets encapsulated fields in double quotes. Example: "field1"||"field2"||"field "Y" 3"||"field4" Since the move to 10g, I believe that the Oracle client-...