This feels like a dumb question, but I see the following in the Oracle concepts guide on transaction management: A transaction ends when any of the following occurs: A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause. A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the cur...
I wrote a package to query rows from a table. This select query will call other functions and returns all the rows from table. But when i write a package with all functions and sprocs , my sproc with select statement gives me an error saying i cannot execute without into statement. But if i use into then it will return only one row. How ...
Friends, I am currently investigating a performance problem with our application. The problem is that an operation that should be completed almost instantly is taking several minutes. In case it is of importance the application architecture is Oracle Forms, Oracle Application Server and Oracle Database. All of which are 10.x The cus...
Is there a way to exclusively lock a table for reading in Oracle (10g) ? I am not very familiar with Oracle, so I asked the DBA and he said it's impossible to lock a table for reading in Oracle? I am actually looking for something like the SQL Server (TABLOCKX HOLDLOCK) hints. EDIT: In response to some of the answers: the reason I nee...
We have a massive amount of stored procedures to convert from sql-server 2000 to oracle 10g. Does anyone know of any tool that would achieve this? We used Oracle SQL developer for this but it seems like it is creating extra cursors for each IF/ELSE condition that was on sql server side. Has anyone successfully used a tool that would ...
I have a column in a non-partitioned Oracle table defined as VARCHAR2(50); the column has a standard b-tree index. I was wondering if there is an optimal way to query this column to determine whether it contains a given value. Here is the current query: SELECT * FROM my_table m WHERE m.my_column LIKE '%'||v_value||'%'; I looked at Ora...
I'm running queries that look something like this: INSERT INTO foo (...) VALUES (...) RETURNING ROWID INTO :bind_var SELECT ... FROM foo WHERE ROWID = :bind_var Essentially, I'm inserting a row and getting its ROWID, then doing a select against that ROWID to get data back from that record. Very occasionally though, the ROWID won't b...
hi how i can backup oracle 10g like backup and restore in sql server ? i want to backup tables and data thank's in advance ...
Hi, Similar question to: http://stackoverflow.com/questions/903488/strange-problem-with-jdbc-select-returns-null but people didn't ask for this. My code: public int myMethod(String day) throws SQLException{ String sql = "Select count(*) from MyTable WHERE someColumn = " + day; Connection connection = ConnFactory.get(); PreparedS...
I have 1 table which contain 8 subtables. Each subtable contains another 3 to 5 subtables. Now my question is that is it possible to partition tables by hierarchy? That means i like to keep all the tables info in one table and then create partitioning on them. ...
Can any one help me referring a book for Oracle table partitioning. Oracle 10g complete reference contains the basics but i need elaborate with example. Thank you ...
I'm trying to export a schema in an oracle 10 database using the EXP command. Let's call the schema "myschema" and the tns name "mydb" to protect the names of the innocent. Anyway, here's the command line that I'm using exp myschema/mypassword@mydb file=myschema.dmp grants=y This works when I try to run an export on other instan...
Right now the process that we're using for inserting sets of records is something like this: (and note that "set of records" means something like a person's record along with their addresses, phone numbers, or any other joined tables). Start a transaction. Insert a set of records that are related. Commit if everything was successful, ...
PL/SQL is not my friend recently. I am getting the above error with the following query: SELECT cr.review_sk, cr.cs_sk, cr.full_name, tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt", cs.cs_id, cr.tracking_number from review cr, cs, fact cf where cr.cs_sk = cs.cs_sk and UPPER(cs.cs_id) like '%' || UPPER(i_cs_id) || '%' and row_delete...
This is beset illustrate by an example that I think should work but doesn't: select * from TABLE_A where KEY in ( select substr(DYNAMIC_KEY,3) from TABLE_B where DYNAMIC_KEY like '$$%' and RECORD_ID='104251893783388824'); Basically, the inner select statement return a set of result which had a '$$' prefix. This is use as a lookup ke...
Exact Duplicate of: how to backup oracle 10g by the same user. hi how i can backup my entire database using pl/sql developer ver 7.1 ? (i work with oracle 10g) thank's in advance ...
Please check out the following query. The SQL isn't as bad as it looks. Basically, we have a fact table and some simple joins to some dimension tables. Then we have a join to a derived table, given the alias ACCOUNTS-DIM-DEP SELECT dw_mgr.fa_trans_fct.period, dw_mgr.fa_trans_fct.asset_cost_company_code, dw_mgr.fa_tra...
I'm new to Oracle (I've been using MySQL mainly until now) so this might be a dumb question. But I have created this table (names are not english but ignore that, that is not important): CREATE TABLE Auta ( id_auto NUMBER(5) UNIQUE NOT NULL, typ CHAR(10), specifikacia_typu CHAR(15), SPZ CHAR(8), farba CHAR(20), datum_vyroby DATE, pocet_...
Here is my query: SELECT * FROM Auta WHERE SUBSTR(spz, 1, 2) = (SELECT SUBSTR(spz, 1, 2) FROM Auta WHERE typ = 'BMW' AND specifikacia_typu = 'Z1' LIMIT 1); And when I run it I get this error: ORA-00907: missing right parenthesis I'm getting a little desperate, I've already tried adding parentheses everywhere in the query and I sti...
Hello members, I'm working on the Solaris environment and the DB i'm using is Oracle 10g. Skeleton of what I'm attempting; Write a ksh script to perform the following. I have no idea how to include my sql query within a shell script and loop through the statements. Have therefore given a jist of what I'm attempting, below. Copy file...