I've got the following objects: CREATE FUNCTION CONSTFUNC RETURN INT DETERMINISTIC AS BEGIN RETURN 1; END; CREATE TABLE "FUNCTABLE" ( "ID" NUMBER(*,0) NOT NULL, "VIRT" NUMBER GENERATED ALWAYS AS ("CONSTFUNC"()) NULL ); however, the functable => constfunc dependency is not listed in all_ or user_ dependencies. Is there anywhere I ...
I'm trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle. I have about 10 tables, varying from 4 columns up to 25. I'm not using any constraints/keys so this should be reasonably straight forward. Firstly I generated scripts to get the table structure, then modified them to conform ...
This is another stab into a problem I posted here. Please don't close as duplicate, because it goes in another direction. I'd like to automatically update a database column with an aggregate of another column. There are three tables involved: T_RIDER RIDER_ID TMP_PONYLIST ... T_RIDER_PONY RIDER_ID PONY_ID T_PONY PONY_ID ...
I have the following mapping @Entity @SequenceGenerator(name="sacpSequenceGenerator", sequenceName="SACP_SEQ") public class Sacp { private Integer id; @Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="sacpSequenceGenerator") public Integer getId() { return this.id; } // other setter's a...
I have a number of pl/sql procedures that can take several minutes to run. While developing them, I've added a few print statements to help debug and also provide some feedback and progress indicators. Initially, I ran these on small test sets and output was almost instantaneous. Now that I'm testing with larger test sets that take sever...
I'm trying to find a query which will return me a list of the foreign keys for a table and the tables and columns they reference. I am half way there with SELECT a.table_name, a.column_name, a.constraint_name, c.owner FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS C where A.CONSTRAINT_NAME = C.CONSTRAINT_NAME an...
I am attempting to figure out if a column in oracle is populated from a sequence. My impression of how oracle handles sequencing is that the sequence and column are separate entities and one needs to either manually insert the next sequence value like insert into tbl1 values(someseq.nextval, 'test') or put it into a table trigger. M...
Perhaps I'm retarded, but I can't figure out how to set MaxPooledStatements in Oracle using the Oracle thin JDBC driver. Could someone point me in the right direction? ...
Hey all, I'm trying to create a template for a button in Oracle APEX but I don't seem to have access to the appropriate substitution strings to make it work. For non-templated buttons APEX seems to insert a handler for the onclick event that calls doSubmit('buttonName') Unfortunately, when I go to create a template the only substituti...
I need to update an existing constraint in Oracle database to add a new column there. ALTER TABLE MY_PARTNER_DETAILS MODIFY CONSTRAINT UQ_MY_PARTNER_DETAILS UNIQUE(PARTNER_CODE,PGOOD_CODE,SITE_CODE,PARTNER_PLACEMENT,PARTNER_PARTICIPATION) Gives: Error at line 1 ORA-00933: SQL command not properly ended What's the problem with that? ...
This is one of those "there's gotta be a better way" questions. Let me set up the problem, then I'll give you my hacked solution, and perhaps you can suggest a better solution. Thanks! Lets take this little tidbit of PL/SQL DECLARE TYPE foo_record IS RECORD (foo%type, bar%type); TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_IN...
Oracle SQL can do hierarchical queries since v2, using their proprietary CONNECT BY syntax. In their latest 11g release 2, they added recursive subquery factoring, also known as the recursive with clause. This is the ANSI standard, and if I understand correctly, this one has been implemented by other RDBMS vendors as well. When comparin...
Hey all, This should be something fairly simple and straight-forward but for some reason I can't get it to work. I've created my SProc like so: create or replace procedure zadmit_migrate_data (zadmit_seq_no number) is thisPIDM number; begin select pidm into thisPIDM from saturn.zadmit_core_data where pk_seqno = zadmit_...
We use Oracle on a project and would like to also support MySQL. How close are their SQL dialects? Is it perhaps even possible to use the same SQL source for both without too many gymnastics? Details: We're using iBatis, a persistence manager that cleanly segregates the SQL statements into resource files. But we work at the SQL le...
Problem: A jar file was failed to deploy on a UAT server. Reason: Because when we are trying to rename it,it is showing "cannot rename the file it is been used by another user". step choosen in order to solve the problem log on UAT server(xxx). we need the list of java processes along with the PID on our UAT server which is r...
I have this table: create table demo ( key number(10) not null, type varchar2(3) not null, state varchar2(16) not null, ... lots more columns ... ) and this index: create index demo_x04 on demo(key, type, state); When I run this query select * from demo where key = 1 and type = '003' and state = 'NEW' EXPLAIN PLA...
i have a database about several companies which include their name, price of share, %age change: NAME PRICE % CHANGE ------------------------------------------------- a 67 11 b 23 6 c 456 5.89 d 112 ...
Okay, here's the deal: I have a C#/.NET app accessing an Oracle 8 database, that works well on our 32-bit machines. It works using a v.8 ODBC client, a 9i client, or a 10g XE client. However, nothing seems to work on a 64-bit windows machine. I did successfully install a 64-bit 11g client, only to find it refuses to talk to a v8 datab...
Oracle's table server offers a built in function TRUNC(timestamp,'DY'). This function converts any timestamp to midnight on the previous Sunday. What's the best way to do this in mySQL? Oracle also offers TRUNC(timestamp,'MM') to convert a timestamp to midnight on the first day of the month in which it occurs. In MySQL, this one is st...
I have a table with millions of rows, and I need to do LOTS of queries which look something like: select max(date_field) where varchar_field1 = 'something' group by varchar_field2; My questions are: Is there a way to create an index to help with this query? What (other) options do I have to enhance performance of this query? ...