How to copy the data from Excel to oracle?
How to copy the data from Excel to oracle? ...
oracle
Does Oracle have an equivalent of SQL Server's table variables?
In SQL Server, you can declare a table variable (DECLARE @table TABLE), which is produced while the script is run and then removed from memory. Does Oracle have a similar function? Or am I stuck with CREATE/DROPs that segment my hard drive? ...
How can I get rid of dynamic SQL
I have the following dynamic SQL in one of my package bodies OPEN ccur for 'select c.category from test_category c where c.deptid='||PI_N_Dept || ' and c.category not in ('|| sExcludeCategories ||')'; sExcludeCategories will contain a set of integers separated by comma. I would like to eliminate this dynamic SQL ...
Oracle 9i: How can I determine, using metadata, whether or not an index is clustered?
The question pretty much sums this up, but I'll provide some more details. I can almost safely assume that any primary key index in an Oracle database is clustered. But I'm not one to assume. Besides, a user might have created a clustered index that wasn't the primary key. If that's the case, I'd really like to know. So, in the interes...
Modulo arithmetic on dates in SQL
I have a system which defines repeating patterns of days. Each pattern has a base date (often a few years in the past, when the pattern was created) and a day count (which loops), so for example it might define a pattern for a seven day period: Table: Pattern ID | BaseDate | DayCount ----------------------------- 1 | 01/02/2005 | 7 ...
JDBC Thin Driver: Invalid Packet Lenght [sic]
I have encountered a strange "Invalid Packet Lenght" (that is how the error is spelled) error when I run an automated bulk test on some of my Java code and I hope that someone has either encountered this error before or can point me in the right direction. I do not encounter this error when testing my code via JUnit unit tests or from...
How can I constrain multiple columns to prevent duplicates, but ignore null values?
Here's a little experiment I ran in an Oracle database (10g). Aside from (Oracle's) implementation convenience, I can't figure out why some insertions are accepted and others rejected. create table sandbox(a number(10,0), b number(10,0)); create unique index sandbox_idx on sandbox(a,b); insert into sandbox values (1,1); -- accepted ins...
Importing two dmp files into single schema
I have two dmp files to imported into the same schema in my DB. They are each 20GB (size was probably the reason why they were exported as two dump files). How can I do a successive import into the schema. Does impdp command have an option to allow me to import more than one file? ...
Where are these functions are stored in Oracle database?
Where is the function sysdate stored, and in what package, e.g: select sysdate from dual; select systimestamp from dual; Also, take this query: select sys.login_user,sys.database_name ,sys.sysevent from dual; what is sys here? Is it a package? where is this package stored? can I view the source(text) in this package please provide...
What is Oracle Native web services?
Native web services is a new feature of the XML DB technology. In google i found that it`s very close to SOA. Can anyone simply explain: 1) what is the main usage of Native web services 2) what is the main difference of XML DB 11g and previous XML DB releases. Thanks. ...
How to see Oracle Table Logs ?
I look at table properties in Oracle and it has "LOGGING: YES". how can i query that log or if its in file were can I find it? ...
Execute sql statement via JDBC with CLOB binding
I have the following query (column log is of type CLOB): UPDATE table SET log=? where id=? The query above works fine when using the setAsciiStream method to put a value longer than 4000 characters into the log column. But instead of replacing the value, I want to append it, hence my query looks like this: UPDATE table SET log=log||...
What is the fastest way to load data into a ORACLE database with .NET?
I currently have a daily process that loads a large amount of data from a TXT file into a ORACLE database, using a shell script that calls sql_loader. I want to migrate that to a .NET service, but don't want to rely on executing sql_loader from my service. What is the best (and fastest) way to accomplish that? ...
Oracle: OALL8 is in an inconsistent state
As part of upgrading JRun, we are moving from a 1.4 JVM to a 1.6 JVM. Now I am getting a really strange oracle db error: "OALL8 is in an inconsistent state". I have pinned down the problem to insert queries that do not use bind variables at all - all inline parameters. If I run the query without any bind variables, I get the above error....
JVM crashes when trying to connect to Oracle using OCI
I have three machines set up as follows: CompA: Running Oracle server 10.2.0.3 CompB: Running Oracle server 10.2.0.4 and my client code CompC: Running client code only On the client code on both CompB and CompC, connecting to either Oracle DB works flawlessly using the Thin driver. I am trying to connect to each Oracle DB from the c...
What does the "sys" prefix mean in the following?
select sys.database_name,sys.sysevent,sys.login_user from dual; what is sys in this query? Can I see the other functions that are in sys? What is the query for doing so? In what situations is this sys useful? Where will be the information about sys is stored in database? ...
Getting ORA Oracle error code using PHP function oci_connect?
The PHP function oci_connect (which connects to an Oracle database) just returns false if it fails, which at the moment I handle like this: $connection = oci_connect($username, $password, $database); if (!$connection){ return $result = "Trouble connecting to the Oracle Database"; } But really I'd like to have the actual ORA error cod...
ORACLE PL/SQL: SELECT INTO variable, two statements, add variables
I have a variable called c_kilometers. I have a cursor that grabs a bunch of records that have those kilometers. I need to run two separate SELECT statements in the cursor that simply grab a kilometer from one table based on values in the cursor, and run another SELECT doing the same thing on another table. SELECT t.kilometers INTO c_ki...
SQL create a temporary 'mapping' table in a select statement
I'm building up results by joining tables select t1.*, t2.col2 from t1, t2 where t1.col1=t2.col1 Is there a way to create a temporary 'mapping' table 'inline' in a select statement for instances where the t2 table doesn't exist? So something like select t1.*, tempt2.col2 from t1, (<create temp table>) tempt2 where ... I'm using O...
Return ROWID Parameter from insert statement using JDBC connection to oracle
I can't seem to get the right magic combination to make this work: OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource(); ods.setURL("jdbc:oracle:thin:app_user/pass@server:1521:sid"); DefaultContext conn = ods.getConnection(); CallableStatement st = conn.prepareCall("INSERT INTO tableA (some_id) VALUES (1) RETURNING ROWID INTO...