Disclaimer: I'm an SQL newb and this is for a class, but I could really use a poke in the right direction. I've got these three tables: student(_sid_, sname, sex, age, year, gpa) section(_dname_, _cno_, _sectno_, pname) enroll(_sid_, grade, _dname_, _cno_, _sectno_) (primary keys denoted by underscores) I'm trying to write an Oracle...
I have encountered a problem in importing the dump file to a new database. When importing the dump file it requires a new tablespace which does not exist in the database. To create the tablespace I need to hijack some scripts which is readonly.For this reason it is complicated to export the table structure through imp tool of Oracle so ...
Hi guys, I am trying to populate a table with the values taken from a flat file using SQLLDR.The problem is that one of the fields in the table is of CLOB datatype(lets say the field name is COMMENTS). In the control file, i have mentioned the datatype for this field as CHAR(4000), since the usage of CLOB in control file was not working....
Hi I use the oracle.jdbc.pool.OracleDataSource for connection pooling. I would like the pool to check whether the connection was not closed properly and to catch it up. I tried the following: ods = new OracleDataSource(); ods.setConnectionCachingEnabled(true); ods.setConnectionCacheName(CACHE_NAME); Properties cacheProps = new Proper...
I have two tables with same columns. i want to update table1 records whose status is 'Linked' by the corresponding values from table2. table 1 ID STATUS VOUCHER 'T010000020 Not Linked null 'T010000021 Linked null 'T010000024 Not Linked null 'T010000026 Linked null table 2 ID ...
I have a field (column in Oracle) called X that has values like "a1b2c3", "abc", "1ab", "123", "156" how do I write an sql query that returns me only the X that hold pure numerical values = no letters? from the example above would be „123“ and „156“ select X from myTable where ...?? ...
Hi there, I'm analyzing a project in asp.net with mvc and would like to use NHibernate as ORM. All over the net I find books, tutorials, quick start guides to work with SQL and Linq-to-Sql but allmost never for Oracle. What is the best setup? In my project we have a rather small database in Oracle and I prefer to have model that is cr...
When I'm trying to execute the following sql block, I got the following Exception ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol "" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-ident...
NOTICE: Appericiate all the answers, thanks but we already have a sequence.. and can't use the UNIQUE contraints because some items need to have duplicates.. I need to handle this using PLSQL somehow, so based on some criteria (using an if statement) i need to ensure there is no duplicates for that.. And just to confirm, these identifier...
I have this query which is showing the ORA-00979:not a GROUP BY expression error. I'm not a sql pro so please could you give me a hint what's wrong ? SELECT * FROM (SELECT a.*, ROWNUM r___ FROM (SELECT DISTINCT "SMTAPP"."XF_MASTER"."ID" AS "XfMasterEntityId", "SMTAPP"."XP_MASTER_DETAIL"."WIFE...
Hiya, Here I go again with some PLSql.. I want to know, if there's anyway I can use the following function like a select without having to turn it into a function or procedure (so I can see the code from the script where it is contained). The code would be like: DECLARE outpt VARCHAR2(1000) := ''; flow_rI VARCHAR2(50); ...
Hi, I need to select the number (cid) of a customer that has rented the same movie from 2 different branches. My tables are as follows: RENTED (cid, copyid) 12345 99999 12345 88888 COPY (copyid, mid, bid) 99999 444 123 88888 444 456 So one customer (12345) has rented the same move (444) from two different branches (123, 45...
Hello Guys .. i have written a C# application that connects to an Oracle 10g database .. using Oracle Data Access Component 11.2 "ODAC" it works perfectly on my machine and now i want to deploy the application and install it in another "clean machine" that has the .Net Framework only!! and i dont want to install the whole ODAC compo...
I've got two datasources, one Oracle and one Sql Server. Due to circumstances that predate me (as in it was like this when I found it) some columns in the Oracle database contain PKs from lookup tables in the Sql Server database. I am attempting to create a Sql Server Reporting Services report that will combine data from both the Oracle...
Complete Oracle n00b here. I have to maintain a legacy form that was developed using Oracle forms 6i and runs on an Oracle 8i backend. Is there any location on the web from where I can still download Oracle forms 6i? If not then whats the best way to run this form on a newer version of oracle? ...
For security reasons, our oracle db objects normally belong to a different schema than the logged in user. Eg. tables are in xx_core and the user we log in is xx_app_yy . In my persistence.xml I define a orm file so that I can specify the schema name at deploy time eg.: <mapping-file>xx_schema_orm.xml</mapping-file> Then in the xx_s...
In SQL Server I used the following hints inside queries: rowlock (row level locking) updlock (prevents dirty reads) readpast (don't block waiting for a rowlock, go to the first unlocked row) e.g. select top 1 data from tablez with (rowlock,updlock,readpast); Are there equivalent in-query hints for Oracle? ...
Suppose I have two tables Table1 and Table2 with the following data. Column1 Column2 Column3 AAA KKK 9 BBB LLL 7 CCC MMM 9 DDD MMM 5 EEE MMM 7 FFF NNN 9 GGG OOO 1 Column4 Column1 TTT DDD TTT BBB UUU EEE VVV BBB WWW AAA WWW BBB XX...
In Oracle, I can select the top 1 message in a sorted table with select messageid from( select messageid, RANK() over (order by messageid asc) as msg_rank from messages ) where msg_rank=1; And as I discovered in a previous question I can select a row exclusively with select * from messages where rownum < 2 ...
Hi, I have an Oracle (10.2.0.4) database table with a column which is encrypted by dbms_obfuscation_toolkit.DESEncrypt tool kit. Some of our data has been messed up by it getting re-encrypted with another key. I want to do some testing on this data to try and recover it. Therefore, I want to copy the data from our live system and into...