I have a set of records indexed by id numbers, I need to convert these record's indexes to a new id number. I have a two column table mapping the old numbers to the new numbers. For example given these two tables, what would the update statement look like? Given: OLD_TO_NEW oldid | newid ----------------- 1234 0987 7698 ...
So i´m starting to write my thesis of my master, next semester (should be done before june), i already have the theme, and i need to write the state of art till february. The main areas are Intelligent systems, Natural Language processing, Semantic Analysis and Data Mining. I am researching for the best books about Natural Language pro...
hi i try to understand procedure..... but still dont..... i need simple sample for procedure. i need procedure that i'll insert Fname and Lname, and i get table with the result search how i can do it ? i need package ????..... or cursor ????........ work on oracle 10g thank's in advance ...
I have two entities, Customer and Order, both of which I have created types for. The SQL type declarations are: CREATE OR REPLACE TYPE "CUSTOMERTYPE" AS OBJECT ( customerId CHAR(6), name VARCHAR2(50), address VARCHAR2(255), telephone CHAR(11) ); CREATE OR REPLACE TYPE "ORDERTYPE" AS OBJECT ( customer REF CustomerType, orderId CHAR(10...
Bakground: I've got a legacy app I'm working on that uses DATE types for most time storage in the database. I'd like to try update some of these tables so that they can utilize time zones since this is causing problems with users in different areas from where the db is(see A below). This is for Oracle 10g. Quetions: 1) Can I migrate ...
I have made stored procedures in oracle. I'm calling it through my asp.net code. The procedure is : PROCEDURE prc_GetNewQuestionNo(iNextQuestionNo IN OUT NUMBER) IS iQuestionNo NUMBER ; BEGIN Select MAX(QUESTIONNO)+ 1 INTO iQuestionNo from tblIFFCOQUESTIONMASTER; iNextQuestionNo:=iQuestionNo; END prc_GetNewQues...
I have two queries from v$sqlarea. For example query 1: select * from employee emp where emp.eid = 5 query 2: select * from employee v where v.eid = 15 Both are exactly the same in structure. but they will be compiled separately each time.. I need to match such queries that vary only by alias names or bind variables. The inbuilt ...
I need to import data from *.dmp file say mydump.dmp. What I'm doing is: imp myuser/mypass file=mydump.dmp log=mylog.log And I'm getting: only a DBA can import a file exported by another DBA I know the DBA credentianls to db but I want to import dump into 'myuser' schema. So how to do it? ...
I’m building a J2EE web application which uses Oracle SSO with an OID back-end as the means for authenticating users. If a user wants to use the application, first he must provide a valid login/password at SSO's login page. When the user is done using the application, he may click on the logout button; behind the scenes, the action ass...
I need to map two tables to a single class, having trouble figuring this out. One table is ROOMS, the other is TRAINERS. The ROOMS table: OOC_UNIT_ID NUMBER(6,0) OOC_START_DT DATE OOC_START_TM DATE OOC_DT_MOD DATE OOC_USER_MOD VARCHAR2(30 BYTE) OOC_END_DT DATE OOC_END_TM ...
I'm trying to find the number of employees joined over a calender year, broken down on a monthly basis. So if 15 employees had joined in January, 30 in February and so on, the output I'd like would be Month | Employees ------|----------- Jan | 15 Feb | 30 I've come up with a query to fetch it for a particular month S...
I need to rename a constraint in an Oracle databse, but I don't know the old name at design-time. What I would like to do is this: declare vOldName string; begin select CONSTRAINT_NAME into vOldName from user_constraints where TABLE_NAME='AGREEMENT' and CONSTRAINT_TYPE='R'; alter table Agreement rename constraint v...
Similar question, but for Oracle. Why would I not always want to choose VARCHAR(MAX)? ...
I'm trying to convert some Oracle SQL queries to work with (in theory) any SQL database. Some of the queries are hierarchical in nature and are written using CONNECT BY. Is there a standard SQL alternative to Oracle's START WITH...CONNECT BY syntax? Or is there some recommended process I should follow to convert the hierarchical queries...
I installed Oracle 11g ODAC 11.1.0.6.21 with Oracle Developer Tools for Visual Studio and built my app. I installed Oracle 11g ODAC 11.1.0.6.21 with Xcopy Deployment on the 32 bit server and it runs great. But now I need to install on a 64 bit server. Where can one find the 64 bit Oracle 11g ODAC? The Oracle client is installed on the...
I'm working on a web interface for modifying Oracle database backup settings. One of the options I want to give users is where to set the flash recovery area. As far as I know, the only way to change this is by executing something like: ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='C:\file\path' SCOPE=BOTH SID='*'; The problem is that if the...
Hi, Question 1 Can anyone tell me if there is any difference between following 2 update statements: UPDATE TABA SET COL1 = '123', COL2 = '456' WHERE TABA.PK = 1 UPDATE TABA SET COL1 = '123' WHERE TABA.PK = 1 where the original value of COL2 = '456' how does this affect the UNDO? Question 2 What about if I update a record in table ...
I am writing a stored procedure for which I need to populate a table based on the data being reported on. In this situation, I will be pulling in three values per day for a certain code in a date range. Say on a certain run of this stored procedure, I have code values X, Y, and Z for a date range as so: select abc.code, abc.da...
hi guys, i'm a pl/sql newbie. now i have a question about oracle type. i saw there are two types of type : CREATE OR REPLACE TYPE "TYPE_NAME1" AS OBJECT ( temp_trans_id number(10), trans_id number(10), resion_id number(10) ) or type new_type_name is record( column1 number, co...
Dear All, I try this command to upload a file (standard.xml) into table "book" the file is very large (>100MB). insert into book values(1,'Complete Data', XMLType(bfilename('XMLDIR', 'standard.xml'), nls_charset_id('AL16UTF8'))); The problem is after I execute the query above, the XML file is not inserted 100% to the column. There i...