SELECT NULL, B.CITY FROM B WHERE NOT EXISTS
C. Sustem Level Rollback Statement level rollback
Is there any limitation on no. of triggers that can be created on a table?
Higher version of Oracle doesn't have any limitation of the number of trigger. Earlier 12 number of triggers were fired per table.
We have a trigger on data base.in the trigger body we have created a body using dbms_output.put_line(********) ;this should be firedwhen ever trigger executed;
This will print the message only if the DML is run at SQL prompt and offcourse serveroutput on has been issued.
Database triggers fires whenever a database operation happens. It can happen through any application software developed using form builder, reports builder, Java or various other programming tools. In those cases dbms_output.put_line will not display any message.
What is p-code and sourcecode ?P-code is Pre-complied code stored in Public cache memory of System Global Area after the Oracle instance is started, whereas sourcecode is a simple code of sp, package, trigger, functions etc which are stored in Oracle system defined data dictionary. Every session of oracle access the p-code which have the Execute permission on that objects.
Source code stored in user_objects data dictinary for user defined Store proc, Trigger, Package, Function. DBA_object stores all the db objects in sp. DB. ALL_objects stores all the db objects in sp. schema.
Source code: The code say a PLSQL block that the user types for the exectionP-Code: The source code after -Syntax check, Parse tree generation, Symantic check, and further execution of the parse tree..giving the final P-code ready for data fetch or manipulation ...
Source code is the Text of the procedure.It is accessed from USER_SOURCE data dictionary view.
P-code is Compiled object code which is not accessible.
What is crosstab
Imagine you're trying to create a result set where the rows need to be columns, or vice versa.
In essence, you need to "pivot" rows into columns, or vice versa. That is a very common requirement, and this is where you need to look at a pivot (or crosstab) query to get the job done.
A simple pivot query is accomplished by basically doing the following:
1. Add some kind of count or row number to your query, if necessary for the grouping 2. Then use your (revised) original query as a sub-query
3. Use "decode" to turn rows into columns (ie. a "sparse" matrix).
4. Use "max" to "squash" the multiple rows you moved to columns, into single rows. Don't forget to group by.
(Note: it gets more complicated if you don't know how many columns you'll need).
scott@DEV816> select job, deptno, count(*) 2 from emp
3 group by job, deptno
4 /
JOB DEPTNO COUNT(*) ---
---ANALYST 20 2
CLERK 10 1
CLERK 20 2
CLERK 30 1
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
PRESIDENT 10 1
SALESMAN 30 4
9 rows selected. And you would like to make DEPTNO be a column. We have 4 deptno's in EMP, 10,20,30,40. We can make columns dept_10, dept_20, dept_30, dept_40 that have the values that are currently in the count column. It would look like this: scott@DEV816> scott@DEV816> select job, 2 max( decode( deptno, 10, cnt, null ) ) dept_10, 3 max( decode( deptno, 20, cnt, null ) ) dept_20, 4 max( decode( deptno, 30, cnt, null ) ) dept_30, 5 max( decode( deptno, 40, cnt, null ) ) dept_40 6 from ( select job, deptno, count(*) cnt 7 from emp 8 group by job, deptno ) 9 group by job 10 / JOB DEPT_10 DEPT_20 DEPT_30 DEPT_40 --- ---ANALYST 2
CLERK 1 2 1
MANAGER 1 1 1
PRESIDENT 1
SALESMAN 4
That has pivoted the CNT column by deptno across job.
What is the difference between all_ and user_ tables ?
An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
While A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.
Based on what conditions can we decide whether to use a table or a view or a materialized view ?
Tables we used for entity information physically in our DB.
View is a virtual representation of table data, for security and hiding the table column infor.
we used normally view. we uses for Report & MIS purposes for showing the data from more than two table.
Materalized view is used for remote data access and suitable for transaction related data storage in distributed environment. It stores the data phyisically comparision to normal view. It can refreshes the remote data auto. and forcefully/manually.
--if the refresh mode is Force dbms_mview.refresh('Ashok_mview') What are ref cursors ?
ref cursor is a simple cursor but it is a datatype that allow developers to declare cursor variable
ref cursor has 2
types:-strong ref cursor where we mention the return type with rowtype.
weak cursor where we don't mention return type.the advantage of this is that we can use weak cursor with any query.it is not rowtype bounded.
for ex:-strong ref cursor
type curvar_type is ref cursor return emp%rowtype;
weak
cursor:-type curvar_cursor:-type is ref cursor is
What is the difference between a reference cursor and normal cursor ? Normal Cursor is a Static Cursor.
Refernce Cursor is used to create dynamic cursor.
There are two types of Ref Cursors:
1. Weak cursor and 2.Strong cursor Type ref_name is Ref cursor [return type]
[return type] means %Rowtype
if Return type is mentioned then it is Strong cursor else weak cursor The Reference cursor does not support For update clause.
Describe in brief some of the features of oracle9i.What is LogMiner?
LogMiner is a powerful audit tool for Oracle databases, allowing administrators to easily locate changes in the database, enabling sophisticated data analyses, and providing undo capabilities to rollback logical data corruptions or user errors.
Can e truncate some of the rows from the table instead of truncating the full table.
You can truncate few rows from a table if the table is partitioned. You can truncate a single partition and keep remaining.
CREATE TABLE parttab ( state VARCHAR2(2), sales NUMBER(10,2)) PARTITION BY LIST (state) (
PARTITION northwest VALUES ('OR', 'WA') TABLESPACE uwdata,
PARTITION southwest VALUES ('AZ', 'CA') TABLESPACE uwdata);
INSERT INTO parttab VALUES ('OR', 100000);
INSERT INTO parttab VALUES ('WA', 200000);
INSERT INTO parttab VALUES ('AZ', 300000);
INSERT INTO parttab VALUES ('CA', 400000);
COMMIT;
SELECT * FROM parttab;
ALTER TABLE parttab TRUNCATE PARTITION southwest;
SELECT * FROM parttab;
Can i write plsql block inside expection
Yes you can write PL/SQL block inside exception section. Suppose you want to insert the exception detail into your error log table, that time you can write insert into statement in exception part. To handle the exception which may be raised in your exception part, you can write the PL/SQL code in exception part.
What is PL/SQL table? SNO MARK --- ---1 592 403 ‘A’4 60 Write a single query to I) Sorted Marks II)First mark III) replace the mark ‘A’ with 0(zero)?
SELECT MAX(MARK) FROM (SElect decode(mrk,'A','0',mrk,TO_NUMBER(mrk)) MARK ,sno from table1 order by MARK DESC )
What are the restrictions on Functions ?
Functions called from SQL have special restrictions
• Stored in database
• Must own or have EXECUTE privilege
• When used in SELECT statement - cannot contain DML
• When used in UPDATE or DELETE - cannot SELECT or perform DML on the same table
What is bulk binding please explain me in brief ?
Bulk Bind and Collect features were introduced to reduce the SQL processing overhead by efficient use of collections in PL/SQL code.
The PL/SQL engine executes procedural statements and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.
Two PL/SQL features, Bulk Bind and Bulk collect help in improving performance and utilizing resources effectively from within PL/SQL code. These features reduce context switching, (i.e., multiple SQL statements being executed from the code resulting in calls to SQL engine), and carry out the operation on the concerned object at one go. Since the SQL statements are fired in a bundle, frequent access to SQL engine is reduced.
In cases where the PL/SQL code is being executed on a different terminal than the server itself, this would also result in optimal network usage rather than too frequent network calls to access the SQL engine.
Bulk Collects (Reading data in bulk)
The bulk collect option instructs the SQL engine to bulk bind the output collections before returning them to the PL/SQL engine. This allows us to load data dynamically into collections at one shot for further processing. Bulk collect can be used with FETCH INTO and RETURNING INTO statements.
Syntax:
... bulk collect into collection...
For example, let us assume that we need to load all pending transactions into a temporary table and process them one by one. As part of validation, there is a need to refer to the data in the same table, from time to time, for each transaction being processed. One possible method
to write the code would be to load all of the data in the temporary table to a collection type.
This way, additional queries on the table could be avoided (context switch) and the processing could be carried out in PL/SQL itself. This idea is further improved on by the use of the bulk collect option, as all data is loaded into PL/SQL at the same time.
Bulk used in Cursors declare
cursor cf is select * from emp;
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
v_limit natural := 10;
begin open cf;
fetch cf bulk collect into V limit v_limit;
for j in V.first .. V.last loop
dbms_output.put_line(V(j).ename);
end loop;
end;
Bulk Insert
Create table BI (a number check(a between 5 and 45));
declare
type no_list is table of number index by binary_integer;
v no_list;
bulk_errors exception;
pragma exception_init ( bulk_errors, -24381 );
begin for i in 5..50
loop v(i) := i;
end loop;
forall j in V.first .. V.last save exceptions insert into bi values (V(j));
dbms_output.put_line('Records inserted');
exception
when bulk_errors then
for j in 1..sql%bulk_exceptions.count loop
Dbms_Output.Put_Line ( 'Error from element #' ||
To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||
Sqlerrm(-sql%bulk_exceptions(j).error_code) );
end loop;
end;
Bulk Delete declare
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
begin
delete from emp
returning empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect into V;
for i in V.first .. v.last loop
dbms_output.put_line(V(i).ename);
end loop;
end;
/
BULK COLLECT is explained as;
"The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:
BULK COLLECT INTO collection_name[, collection_name] ..."
and FORALL is defined as
"The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:
FORALL index IN lower_bound..upper_bound sql_statement;
CREATE OR REPLACE PROCEDURE fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100) IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;
CURSOR c IS SELECT *
FROM all_objects;
BEGIN OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
FORALL i IN 1..l_data.COUNT
INSERT INTO t2 VALUES l_data(i);
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END fast_proc;
/
which was subsequently refined in a later answer to;
SQL> create or replace procedure fast_proc is
2 type TObjectTable is table of ALL_OBJECTS%ROWTYPE;
3 ObjectTable$ TObjectTable;
4 begin 5 select
6 * BULK COLLECT INTO ObjectTable$
7 from ALL_OBJECTS;
8
9 forall x in ObjectTable$.First..ObjectTable$.Last 10 insert into t1 values ObjectTable$(x) ;
11 end;
12 /
.
Latest Answer: Hi Meenakshi,It seems that I can get some more detail about TUNING. If I need to tune my query, what are the steps that I need to take....I know nothing about tuning....If you find time, pls give me some idea...Regards,[email protected] ...