Im using an Oracle database. In my query 100 rows are fetched. If I want to filter rows between rownum 50 and 60, what would be the query? SELECT EMPLID, EFFDT, ACTION, ACTION_REASON from JOB where emplid ='12345' ...
How to fetch an Employees, say 5 latest Action_reason rows which are in an effective dated record, no future rows, should select only current and history rows(effective date <= sysdate). Can I fetch these in single row or will it be 5 rows for an Employee? select emplid, effdt, action_reasons -- we have to build a logic here. -- Should ...
I am looking to return a table of results from an Oracle function. Using a cursor would be easiest, but the application I have to work this into will not accept a cursor as a return value. The alternative is to create a type (likely wrapped in a package) to go along with this function. However, it seems somewhat superfluous to create ...
I am trying to refer to a column name to order a query in an application communicating with an Oracle database. I want to use a bind variable so that I can dynamically change what to order the query by. The problem that I am having is that the database seems to be ignoring the order by column. Does anyone know if there is a particula...
I just tried to do a correlated subquery in the FROM clause of a SELECT statement in Oracle, but I was given an error indicating that I couldn't do the correlation (something to the effect that Obs.pID was not recognized). Should this work? FROM ml.Person Person JOIN ml.Obs ON Person.pID = Obs.pId JOIN (SELECT ObsMax2.pId, ObsMax2.h...
I want to extract only the leaf nodes from an XMLTYPE object in Oracle 10g SELECT t.getStringVal() AS text FROM TABLE( XMLSequence( XMLTYPE( '<xml> <node> <one>text</one> </node> <node> <two>text</two> </n...
When using MySQL MyISAM tables, and issuing an ALTER TABLE statement to add a column, MySQL creates a temporary table and copies all the data into the new table before overwriting the original table. If that table has a lot of data, this process can be very slow (especially when rebuilding indexes), and requires you to have enough free...
Has anyone ever used Oracle Database Migration Verifier tool (DMV)? It's a tool to analyze the source database and compare it against the migrated Oracle database. I have installed the JDBC driver for Microsoft SQL Server. Then, I have edited the property file and run the command java -jar dmv.jar PropertiesFilePath ReportStorePath, w...
I have an Oracle DB, and an ASP Page that has many ListBoxes and DropDownLists. These controls make the user input data to get filtered results on the next page. Once the user clicks search, a string (query) is generated based on the selections of the user. The results page has a datagrid that takes this string and uses it to get data f...
Bit of a newbie to oracle 10g. I have been load testing an application and when I logged into the enterprise console I noticed that my UNDOTBS table space is full. I know that the UNDOTBS table space is used to keep a copy of data that exists before a transaction starts. I thought that this data would be transient and expire once a tran...
I'm using Oracle 10g and Hibernate 2.1 (old version, but not allowed to upgrade to fix). I've got a table with a not nullable column named TIN, varchar2(9). This column is used to stage data loaded from flat files and so can store any string of length 9, including 9 spaces (that is: ' ') if the input file had 9 spaces. What ...
As the title suggests, does anyone know if such a thing exists? ...
Had this question in an interview. ...
I just want to SELECT values into variables from inside a procedure. SELECT blah1,blah2 INTO var1_,var2_ FROM ... Sometimes a large complex query will have no rows sometimes it will have more than one -- both cases lead to exceptions. I would love to replace the exception behavior with implicit behavior similiar to: No rows = no valu...
Anyone have any idea? ...
How to import chinese characters from excel into oracle and also extract chinese characters from oracle into excel? ...
We need to generate dynamic SQL in our Java app. Does anyone know a simple library to do this? In our Java app we have a bunch of where clause filter criteria (database column, operand, value). In other words, we could have a Date instance that we need to use to filter a given datetime Oracle column, a String instance that we need to...
Oracle Internet Directory is Oracle's version of LDAP. It comes with a web-based directory administration interface http://hostname/oiddas By default, all authenticated users can browse the directory to see the basic public profile of all other users. I want to restrict access so that only authorised directory administrators can brows...
When I run the following in an Oracle shell it works fine truncate table table_name But when I try to put it in a stored procedure CREATE OR REPLACE PROCEDURE test IS BEGIN truncate table table_name; END test;/ it fails with ERROR line 3, col 14, ending_line 3, ending_col 18, Found 'table', Expecting: @ ROW or ( or ...
The scenario that created this question: We have a package that is a dependency of another package, sometimes making changes to the "parent" package causes the dependent package to become invalid, but sometimes it doesn't. It has caught us by surprise before. It would be very useful to simply understand what causes invalidation so...