oracle

simple sql query

I have 3 tables as follows. salesman(sid,sname) location(lid,lname) sales_loc(sid,lid) Now i want to print the sid and sname of saleman who has visited all locations. I want a SQL query and I don't want a PL/SQL code. ...

very simple SQL query in oracle 9

I have two tables: emp (empno,ename) address(empno,address) emp can have 0 or more address. I want to print a result as follows: when emp has 0 address then: eno,ename,"no address" when emp has 1 or more address then: enomename ,"address found" I am using oracle 9db ...

Update statement optimization (functions in the where clause)

I feel dumb right now. I got to update 100.000 rows on a database that i don't have direct access to. The total row count of the table is roughtly 500.000 rows. The update just adds one caracter to a field in case it's length is < 3. So Basically: UPDATE X SET VALUE = '0'||VALUE WHERE LENGTH(VALUE) < 3 So i send this update to the DBA...

Delete all but some rows - Oracle

Hi, I want to delete set of the rows from a table. I can decide which rows need to be deleted. I will delete the rows from table only if the count of rows is more than 5 (based on condition). Consider this sample data ID--Col1--Col2-- 1 A X 2 A X 3 A X 4 A X 5 A X 6 A X 7 A X 8 A ...

Oracle C++ linux and more weird stuff

So here is the story. I have this device that uses Linux and more open source tools(btw its an ARM). And I was given the task of creating some magic cashier application with it. I have done it and now my boss have made a new request. He wants me to make that stuff(the device) connect to a remote database(preferably Oracle). So thats wha...

Oracle: How do I determine the NEW name of an object in an "AFTER ALTER" trigger?

Suppose I have an AFTER ALTER trigger on my Oracle database and I rename some database object (ALTER ... RENAME TO ...). Within the trigger, how do I determine the new name of the database object? It seems that the ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME and ORA_DICT_OBJ_TYPE functions all return the old values of the database object. For...

overcoming 'log file sync' by design?

Advice/suggestions needed for a bit of application design. I have an application which uses 2 tables, one is a staging table, which many separate processes write to, once a 'group' of processes has finished, another job comes along a aggregates the results together into a final table, then deletes that 'group' from the staging table. T...

Performance Standalone Procedure vs Packaged Procedure in Oracle

What is the difference of performance between standalone procedure and packaged procedure? Which will be good performance wise and why? Is there any difference in execution of both? ...

Remove deadlock without killing session

Is there any workaround to remove deadlock without killing the session? ...

How to create index in SAP ?

Currently we are interfacing our application with SAP. We are having a lot of performance problems with bapi/rfc approach. So recently we are trying "direct sql" approach to retrieve some data from SAP. Unfortunately, we've found that some of SAP table does not equipped with proper indices for our scenario. We've tried creating index w...

Difference between Static Cursor Vs Dynamic Cursor in oracle

Hi Can any body describe, what are the differences between static cursor and dynamic cursor with examples. ...

Oracle APEX for "Airport" database

Hi, I'm making a database of an imaginary Airport that has a simplified physical model of 10 tables. The final part is to make some kind of web-based GUI for users, I have three general types of them: airport manager, airline representative and user/visitor of the airport website. Currently I'm planning to build the interface with Orac...

Force oracle to use index

Is there any way to force oracle to use index except Hints? ...

CACHE of SQL in oracle

How oracle CACHE a Query (SQL), Query execution contains the following steps. 1. PARSE 2. Execute 3. Fetch in First step oracle checks if query exists in CACHE (Shared Pool) or not (It will be exists if query is identical and based on LRU), if exists then PARSING will be skipped and execution will start. So to make query performance int...

How do I Create a Comma-Separated List using a SQL Query?

I have 3 tables called: Applications (id, name) Resources (id, name) ApplicationsResources (id, app_id, resource_id) I want to show on a GUI a table of all resource names. In one cell in each row I would like to list out all of the applications (comma separated) of that resource. So the question is, what is the best way to do this ...

What is the use of TNS_ADMIN variable in Oracle?

Hi All, Please tell me what is the use of TNS_ADMIN parameter in Oracle? I am working on Unix using oracle database. Is this parameter is required to locate the sqlplus. I am executing a script in which a update query is executed on Oracle Database. The script fails with 127 error code when executed with crontab. The script content...

Does sqlplus uses TNS_ADMIN variable?

Please tell me does sqlplus uses TNS_ADMIN variable on UNIX and AIX? Can we able to locate sqlplus if this variable could not be located? ...

Get a list of all functions and procedures in an Oracle database

I'm comparing three Oracle schemas. I want to get a list of all the functions and procedures used in each database. Is this possible via a query? (preferably including a flag as to whether they compile or not) Ideally it would be great to have a single query with a flag that states whether the function/procedure is in each schema. But ...

Is there any logical reason of having different tablespace for indexes?

Hi Can some let me know why we created different table space for Index and data. ...

Is an user can see only commited data in the same session?

Is there any way to see only committed data only in the same session. Like User A updated table with 10 rows and committed. then again updated 20 rows but not committed, so here User A should only see committed data (10 Updated rows) ...