Hi. I have an windows application in VB.NET (2.0) and Oracle Database, the connections are handled by System.Data.OracleClient. My oracle client is 10g. In the server side, the AutoCommit is off. The application uses transtactions (isolationLevel: ReadCommitted) for certain operations. The problem is that in some machines (and only in...
Hello everybody, I'm working on a piece of sql that I want to optimize. I have inside a bunch of cursors. I'm wondering if I can use something else instead of cursors. I'm thinking using some kind of variables, filling them, and for the rest of the treatment avoiding the DB connection (I have a complex treatment). For instance I have ...
Are there any techniques that would allow a row set like this WITH base AS ( SELECT 1 N FROM DUAL UNION ALL SELECT 2 N FROM DUAL UNION ALL SELECT 3 N FROM DUAL UNION ALL SELECT 6 N FROM DUAL UNION ALL SELECT 7 N FROM DUAL UNION ALL SELECT 17 N FROM DUAL UNION ALL SELECT 18 N FROM DUAL UNION ALL SE...
CREATE TABLE DIALOGUE_TABLE(EXPIRE_TIME TIMESTAMP); Following code snippet is inside stored proc :- PO_EXPIRETIME :- OUT PARAM of procedure a varchar2 SELECT TO_CHAR(SYS_EXTRACT_UTC(EXPIRE_TIME)) INTO PO_EXPIRETIME FROM DIALOGUE_TABLE; When I run Stored Proc from server using EXEC and print PO_EXPIRETIME time stamp is pr...
How do I cure the cause of the occasional exception IllegalArgumentException occurred while calling setter that Hibernate throws when my program attempts to load a user by name from the database? Am I incorrectly mapping column USER_RV in table User to class Integer instead of BigDecimal or some other integer type? Note that the same a...
As said in the title, I want to select the number of events per team and per year. The select statement below works fine but doesn't give me exactly what I am looking for. SELECT Team.team_id, TO_CHAR(Event.START_DATE_TIME, 'yyyy') AS year, count(event_id) AS events FROM Team LEFT OUTER JOIN Event ON Event.team_id = Team.team_id...
Hi, I may be asking something silly but I don't see any sql*plus or any GUI kind of interface to connect my Oracle server remotely. I tried SQL Plus but it's a command line interface, don't know what to do with that. ...
When I am executing the package I get an error message: error in the emp_test ORA-06530: Reference to uninitialized composite Can u explain how I can initialized an object type in a package? CREATE OR REPLACE TYPE emp_obj AS OBJECT ( emp_no number, salary number, job varchar2(20) ); CREATE OR REPLACE PACKAGE BODY e...
Most forums cite minor differences in speed, backup, etc. It's about time someone tell how the two differ when it comes to GUI data validation. Do this 2 Database always depend on java(or other software), or do they have the ability to create a user interface the accepts only valid input. Things like: positive numbers only, age between 1...
Hello, I would like to run a PL/SQL procedure involving 80,000,000 records. This PL/SQL procedure deletes about 80,000,000 records, backupping them in a GLOBAL TEMPORARY TABLE created with the clause ON COMMIT PRESERVE ROWS. How can I know how many records can contain this GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS? What is the si...
Hi, In my DB, I store various version numbers, like the following : OBJNAME Fix_6.0.0a.1 Fix_6.0.0a.2 I would like to sort them not according to last version element (the number behind the last .character. How do I write such SQL statement ? I guess it's something like SELECT SUBSTR(INSTR(OBJNAME, ".", -1)) as LAST_VERSION, OBJNAME ...
Suppose I have a set of ID . For each ID , I will insert many records to many different tables based on the ID .Between inserting difference tables, different business checks will be called . If any checking fail , all the records that are inserted based on this ID will be ROLLBACK .This bulk insert action is done through using PL/SQL ....
HI all, I have used Oracle Advanced Security to encrypt data during data transfer. I have successfully configured ssl with below parameters and I have restarted the instance. I am retrieving data from a java class given below. But I could read the data without decrypting, the data is not getting envrypted. Could you please help. Environ...
The question is how to return the l_array as refcursor, Since the interface i am using can handle cursor easily rather than an array of record. Plz help create or replace package sample TYPE r_type is record( code number; description varchar2(50)); TYPE tr_type IS TABLE OF r_type; l_rarray tr_type ; ind number:=0; PROCEDURE...
When I commit a transaction in Oracle how can I get the earliest SCN that contains the transactions changes? ...
After I issue a COMMIT WRITE NOWAIT in Oracle, how can I find out when the transaction has become durable? ...
I have one Mview which is created already. Now I want to refresh it using dbms_refresh.refresh() for evry 5 mins automatically. ( not using dbms_mview.refresh ) How this can be achieved ? ...
I have a client running a WebLogic 11g install on a Windows Server machine who wishes to implement Apache-style mod_rewrite-like functionality to translate requests for http://easyurl.com to http://super.complicated.com/with/this/junk?here=and_more. I have scoured the Internet for advice, but all I can find are other people who are askin...
Here I have a query like below: SELECT field FROM table WHERE value IN ('val1', 'val2', 'val3', ... 'valn') Let's say there are 2000 values inside the IN clause, the value doesn't exist in other table. Do you have any idea to speed up this operation? The question is open to accept any kind of methods.. Thanks! ...
What are known reliable tools for syncing huge amounts of data between Oracle DB instances in live environment? Requirements are that the host with live data is running in a live environment, i.e. the database is updated. Receiving host is offline, and will go online only when data sync is complete. Most of the data is stored in blob c...