I started creating JPA/hibernate mappings for a legacy database based on Oracle. At one (early...) point I have a many-to-many relation between to tables (FOO, BAR, join table with extra fields: FOO_BAR). So I defined three entities, created an embeddable Id class for the join table, strictly following some examples from a good (?!) book...
Firstly I am an oracle newbie, and I don't have a local oracle guru to help me. Here is my problem / question I have some SQL scripts which have to be released to a number of Oracle instances. The scripts create stored procedures. The schema in which the stored procedures are created is different from the schema which contains the ...
During my job, I usually have to copy rows while changing their primary key and giving them a new stamp and maybe changing the foreign key. The problem is I don't want to type all the column names while doing; insert into table_name select pk_seq.nextval, 'foreign-key', col3, col4...col51 from table_name wher...
There is a composite primary key stored in a DB that consists of a date field and a foreign key ID. Normally this would create duplicates however the date field (although it only displays the day, month, year appears to have timestamp information stored as well) My question is how to extract the timestamp information (I think using the ...
A couple of years ago I wrote a small utility to move data from an Oracle db to a Postgres db. I used Java and JDBC to accomplish this because I wanted Java to handle the data formatting for data used in a prepared statement to do the insert. The original version of the utility assumed that the table names and column names were the same...
Table A Id Name 1 Apple 2 Mango 3 Banana Table B Id Locale Name_In_Lang 1 es-ES Apple[Spanish] 1 it-IT Apple[Italian] 2 it-IT Mango[Italian] Let us say if the user requested spanish versions then the query should return all the spanish [es-ES] verions from Table B for every record in T...
I need to insert a record into a table and set the value of a column (e.g. orderid) to a unique #. And return that number used. I thought the process would be to do use a sequence and an insert statement with nextval: insert into ordersTable(orderid) values(ordernums.nextval); But how to I get the number that was used? My thought i...
I am getting a cast error with Linq to a DataTable that was populated with Oracle. bool isTrue = DataTable.AsEnumerable().Any (x => x.Field<int>("MYNUMBERFIELD") == MYNUMBER); In SQL this works fine as expected. In Oracle is fails with a cast error on the cast. In C# code the same thing happens when you do the following: i...
Hi all, I want a tool or solution to find out the affected table on running the procedure|Function or package Given the PL/SQL code. This is require for me to comeup with the better testcase by knowing which all the tables will be affected by running the code and what all the operation performed on them. The solution should even work ...
Why is dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', TRUE) not generating constraints in separate files? At this moment this pl/sql pastes the constraints after the table schema definition files. What flag do I have to use to achieve this separate constraints schema definition directory? ...
What does ODM(Oracle Data Miner) do? Can you give me useful materials or a brief information about this option? Thank you.. ...
I have 2 tables which have many records (say both TableA and TableB has about 3,000,000 records).vr2_input is a varchar input parameters enter by the users and I want to get the most 200 largest "dateField" 's TableA records whose stringField like 'vr2_input' .The 2 tables are joined as the following: select * from( select * from...
Hi, I just want to know whether Oracle forms 11g supports other databases like DB2, SQL Server etc. Also does Oracle forms 11g supports Oracle database 9i or 10g apart from 11g? ...
I'm trying to get CONSTRAINTS from user_objects table like this: select CASE object_type WHEN 'DATABASE LINK' then 'dblinks' WHEN 'FUNCTION' then 'functions' WHEN 'INDEX' then 'indexes' WHEN 'PACKAGE' then 'packages' WHEN 'PROCEDURE' then 'procedures' WHEN 'SEQUENCE' then 'sequences' WHEN 'TABL...
Is it possible to create a table with a column that combines two column values? something like this: create table test1 ( number1 decimal(6,2), number2 decimal(6,2), total decimal(6,2) DEFAULT (number1+number2) ); ...
I have a schema called "CUSTOMERS". In this schema there is a table called RECEIVABLES. There is another schema called "ACCOUNTS". In this schema, there is a table called RECEIVABLES_AC. RECEIVABLES_AC has a public synoym called RECEIVABLES. The table structure of both the tables is exactly the same. If your front-end uses the custom...
SQLite doesn't support nor accept such a query: select * from mytable where col = 'val' for update wait 10; Do you know if there is a way to make SQLite silently skipping the part "for update wait 10" and thus avoid any parsing error. My point is to get Oracle SQL commands barely working against SQLite without having to manually edit t...
If not, are there standards in existence for rules engine storage? or Is there a C# implementation of the Oracle Rules Engine syntax? ...
Hi, I am trying to write a visual studio C# .net app which i can put on a subversion repository. The program runs on oracle drivers (Oracle.dataAccess) and should be easily managed and developed by anybody trying to access it. The problem is that Visual Studio finds the Oracle.DataAccess installed on the developer's computer and some ran...
Hi, I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value. I tried using DBMS_LOB.substr(column) and i get the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small How can i query the CLOB column? ...