I have two Schemas
DMS_NS_STG_1,DMS_NS_STG_2
Both are having same tables but with diffrent data. I want to write a generic code piece for a table to get that diffrences out, it can be for multiple tables. There will be a DB link between two DB.
1. What's the differences between CAT and TAB?
2. Why doesn't TAB exist in dict?
3. What the appropriate situation for CAT and TAB?
sys@ORCL> desc cat
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
sys@ORCL> desc tab
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
sys@ORCL> select * from dict where table_name='CAT';
TABLE_NAME COMMENTS
---------- -----------------------------------
CAT Synonym for USER_CATALOG
sys@ORCL> select * from dict where table_name='USER_CATALOG';
TABLE_NAME COMMENTS
-------------------- -----------------------------------------------------------------
USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user
what are the main points that these examples are considered cursors? and why are they called explicit and implicit cursor.
explicit
for x in (select * from emp) loop
dbms_output.put_line('emp no: '||x.empno);
end loop;
implicit
select empno
into vEmpno
from empno
where empname = 'SCOTT';
for all we know that these are not clearly defined on the declaration area as cursor.
we get daily a full dump from our customer. We have to build the differences of tables (deltas) and load them in separate tables.
There some questions, requiremenst and restrictions:
1) Is it possible the merge the dumps ?
2) How to build the differences as the easiest ?
3) The whole process (import, merge, extract) should be run in batch.
4) We have only a solid knowledge and liitle time for doing.
I am planing to write the web-application which use Oracle DB 11g.I would like to understand what are the differences (specially, performance issues, steps of execution, optimizer possibilities) between calling SQL statements and PL/SQL procedures/functions. Which approach is more appropriate, and why?
Examples:
a) WebApp->Call("select * from employees where department_id = ?", 10) ;
b) WebApp->Call("? := mypackage.get_emp(?)", refCursor, 10);
create package mypackage is
function get_emp(dep_id in number) return sys_refcursor is
begin
open cur for select * from employees where department_id = dep_id;
end;
end;==========================================
Requirements: High-concurrency, 100+ db sessions, DB will not be used for business-logic.
we have an issue on a server, where if we insert a value containing foreign characters from SQL+ we see it screwed up from SQLdev, whereas if we do the same insert from SQLdev it's then screwed up in SQL+.
All operations are performed on the DB server itself, SQLdev connection type is TNS and the entry being used is the same as the one used by SQL+.We checked v$nls_parameters and the values contained there are the same from both SQL+ and SQLdev.
We have recently upgraded application (from Oracle Applications 11.5.9 to 12.1.3) and database (from 9.2.0.5.0 to 11.2.0.3.0).Since we are confronting to performances issues, i try to analyse some queries which Explains plans seems strange (in my opinion).Studying one of them i discover the next case (which according to my logic, i can't explain): --
Just bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and mmt.ORGANIZATION_ID = :p2and mmt.TRANSACTION_REFERENCE = :p3--and mmt.SUBINVENTORY_CODE = :p4 PlanSELECT STATEMENT ALL_ROWS Cost: 5 Bytes: 361 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 5 Bytes: 361 Cardinality: 1 1 INDEX RANGE SCAN INDEX XXSPE.XXSPE_MTL_MATERIAL_TRANSAC_N99 Cost: 3 Cardinality: 2-- Nvl on bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and
[code]...
We have the following scenario:
1) We get a daily Oracle 11 Dump
2) We only want to import the current changes in a MySQL database .
For this we need:
a) Is there a tool, script or best practices to get only the differences (delta) ?
b) How to import an Oracle dump or only teh differences into a MySQL database ?
differences of enabling thread as public or private?
lets assume that i have two threads (1, 2) for two instances (1, 2) and each of thread consists of 2 redolog groups - minimum configuration.in case i need add third node i need third thread.
what will be difference if i set this thread as public instead private?
I have an existing view that stores some data. I was asked to create a table based on the columns present in the view. I only copied the table structure from the view.
Now I would need to create a stored procedure that executes the query and stores the data in the table with a snap id and date of snap column. The reason this needs to be done is to show differences over time for historical and trending.
We are trying to use the methods/constructors in the object types and find it more similar to the procedures and functions in the packages. I am wondering how they are different from stored procs and functions and what are the advantages?
View 2 Replies View RelatedHow oracle deals with the setting of init.ora parameter compatible.
e.g. we have
DB Version=10.2.0.4
compatible=10.2.0
what is the possible leak for us ? Is there any description about the features changed in 10.2 up to 10.2.0.4 in all steps - so that we can check out which functions we do not have.
In Oracle database pl/sql 11.2: describe the differences between :
records,
tables, and
tables of records.