Batch File to Append Date to file name
SAVE EXCEPTIONS
DELETE * FROM emp WHERE sal > 500000/num_tab(i);
EXCEPTION WHEN OTHERS THEN -- this is not in the doco, thanks to JL for pointing this out
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..errors LOOP -- Iteration is
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX; -- Error code is
SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
END LOOP;END;
91.#1 What are the advantages and disadvantages of using PL/SQL or JAVA as the primary programming
tool for database automation.
#2 Will JAVA replace PL/SQL?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database
Proprietary to Oracle and difficult to port to other database systems Data manipulation is slightly faster in PL/SQL than in Java
Easier to use than Java (depending on your background) Java:
Open standard, not proprietary to Oracle
Incurs some data conversion overhead between the Database and Java type systems Java is more difficult to use (depending on your background)
110.
1.What is bulk collect?
2.What is instead trigger
3.What is the difference between Oracle table & PL/SQL table?
4.What R built in Packages in Oracle?
5.what is the difference between row migration & row changing?
1.What
is bulk collect?Bulk collect is part of PLSQL collection where data is stored/ poped up into a variable. example:
declare
type sal_rec is table of number;
v_sal sal_rec;
begin
select sal bulk collect into v_sal from emp;
for r in 1.. v_sal.count loop
dbms_output.put_line(v_sal(r));
end loop;
end;
2.What is instead trigger
instead triggers are used for views.
3.What is the difference between Oracle table & PL/SQL table? Table is logical entity which holds the data in dat file permanently . where as scope of plsql table is limited to the particular block / procedure . refer above example sal_rec table will hold data only till
programme is reaching to end;
There R more then 1000 oracle builtin packges like:
Dbms_output, dbms_utility dbms_pipe ...
5.what is the difference between row migration & row
changing?
Migration: The data is stored in blocks whic use Pctfree 40% and pctused 60% ( normally). The 40% space is used for update and delete statements . when a condition may arise that update/delete statement takes more then pctfree then it takes the space
from anther block. this is called migration.
RowChaining: while inserting the data if data of one row takes more then one block then this row is
stored in two blocks and rows are chained.
insted of triggers: They provide a transparent way of modifying view that can't be modified directly
through SQL,DML statement.
111.Can anyone tell me the difference between instead of trigger, database trigger, and schema trigger? INSTEAD OF Trigger control operation on view , not table. They can be used to make non- updateable views updateable and to override the behvior of view that are updateable. Database triggers fire whenever the database startup or is shutdown, whenever a user logs on or log off, and whenever an oracle error occurs. these tigger provide a means of tracking activity in the database
if we have created a view that is based on join codition then its not possibe to apply dml operations like insert, update and delete on that view. So what we can do is we can create instead off trigger
and perform dml operations on the view.
131. HI,What is Flashback query in Oracle9i...?
Flahsback is used to take your database at old state like a system restore in windows. No DDL and
DML is allowed when database is in flashback condition.
user should have execute permission on dbms_flashback package
for example:
at 1030 am
from scott user : delete from emp;
commit;
at 1040 am I want all my data from emp table then ?
declare
cursor c1 is select * from emp;
emp_cur emp%rowtype; begin dbms_flashback.enable_at_time(sysdate - 15/1440); open c1; dbms_flashback.disable; loop
fetch c1 into emp_cur;
exit when c1%notfound;
insert into emp values(emp_cur.empno, emp_cur.ename, emp_cur.job,
emp_cur.mgr,emp_cur.hiredate, emp_cur.sal, emp_cur.comm,
emp_cur.deptno);
end loop;
commit; end; /
select * from emp;
132. what is the difference between database server and data dictionary
Database server is collection of all objects of oracle
Data Dictionary contains the information of for all the objects like when created, who created etc. Database server is a server on which the instance of oracle as server runs..whereas datadictionary is the collection of information about all those objects like tables indexes views triggers etc in a database..
134. Mention the differences between aggregate functions and analytical functions clearly with examples?
Aggregate functions are sum(), count(), avg(), max(), min()
like:
select sum(sal) , count(*) , avg(sal) , max(sal) , min(sal) from emp;
analytical fuction differ from aggregate function
some of examples:
SELECT ename "Ename", deptno "Deptno", sal "Sal",
SUM(sal)
OVER (ORDER BY deptno, ename) "Running Total",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename) "Dept Total",
ROW_NUMBER()
OVER (PARTITION BY deptno
ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename
SELECT * FROM (
SELECT deptno, ename, sal, ROW_NUMBER()
OVER (
PARTITION BY deptno ORDER BY sal DESC
) Top3 FROM emp
)
WHERE Top3 <= 3
136. what are the advantages & disadvantages of packages ? Modularity,Easier Application Design,Information Hiding,Added Functionality,Better Performance, Disadvantages of Package - More memory may be required on the Oracle database server when using Oracle PL/SQL packages as the whole package is loaded into memory as soon as any object
in the package is accessed.
Disadvantages: Updating one of the functions/procedures will invalid other objects which use different functions/procedures since whole package is needed to be compiled.
we cant pass parameters to packages
137. What is a NOCOPY parameter? Where it is used?
NOCOPY Parameter Option
Prior to Oracle 8i there were three types of parameter-passing options to procedures and functions: IN: parameters are passed by reference
OUT: parameters are implemented as copy-out
IN OUT: parameters are implemented as copy-in/copy-out
The technique of OUT and IN OUT parameters was designed to protect original values of them in case exceptions were raised, so that changes could be rolled back. Because a copy of the parameter set was made, rollback could be done. However, this method imposed significant CPU and memory overhead when the parameters were large data collections—for example, PL/SQL Table or VARRAY types.
With the new NOCOPY option, OUT and IN OUT parameters are passed by reference, which avoids copy overhead. However, parameter set copy is not created and, in case of an exception rollback, cannot be performed and the original values of parameters cannot be restored.
Here is an example of using the NOCOPY parameter option:
TYPE Note IS RECORD( Title VARCHAR2(15), Created_By
VARCHAR2(20), Created_When DATE, Memo VARCHAR2(2000));TYPE Notebook IS
VARRAY(2000) OF Note;CREATE OR REPLACE PROCEDURE Update_Notes(Customer_Notes
IN OUT NOCOPY Notebook) ISBEGIN ...END;
NOCOPY is a hint given to the compiler, indicating that the parameter is passed as a reference and hence actual value should not be copied in to the block and vice versa. The processing will be done accessing data from the original variable. (Which other wise, oracle copies the data from the parameter variable into the block and then copies it back to the variable after processing. This would put extra burdon on the server if the parameters are of large collections/sizes) For better understanding of NOCOPY parameter, I will suggest u to run the following code and see
the result. DECLARE n NUMBER := 10; PROCEDURE do_something ( n1 IN NUMBER, n2 IN OUT NUMBER,
n3 IN OUT NOCOPY NUMBER) IS
BEGIN n2 := 20; DBMS_OUTPUT.PUT_LINE(n1); -- prints 10 n3 := 30; DBMS_OUTPUT.PUT_LINE(n1); -- prints 30 END; BEGIN do_something(n, n, n); DBMS_OUTPUT.PUT_LINE(n); -- prints 20 END;
138. How to get the 25th row of a table.
select *from Empwhererownum<26
minus
select *from Empwhererownum<25
SELECT * FROM EMP A WHERE 25=(SELECT COUNT(*) FROM EMP BWHERE A.EMPNO>B.EMPNO);
139. What is Atomic transaction?
An atomic transaction is a database transaction or a hardware transaction which either completely occurs, or completely fails to occur. A prosaic example is pregnancy - you can't be "halfway
pregnant"; you either are or you aren't
140. What is materialized view?
A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
141. How to change owner of a table?
Owner of a table is the schema name which holds the table. To change the owner just recreate the
142. How can i see the time of execution of a sql statement?
sqlplus >set time on
144. what happens when commit is given in executable section and an error occurs ?please tell me what ha Whenever the exception is raised ..all the transaction made before will be commited. If the exception is not raised then all the transaction will be rolled back.
145. Wheather a Cursor is a Pointer or Reference?
cursor is basically a pointer as it's like a address of virtual memory which is being used storage related to sql query & is made free after the values from this memory is being used 146. What will happen to an anonymus block,if there is no statement inside the block?eg:-declarebeginend
We cant have
declare begin end
we must have something between the begin and the end keywords
otherwise a compilation error will be raised.
147.Can we have same trigger with different names for a table?
eg: create trigger trig1
after insert on tab1;
and
eg: create trigger trig2
after insert on tab1;
If yes,which trigger executes first.
The triggers will be fired on the basis of TimeStamp of their creation in Data Dictionary. The trigger
with latest timestamp will be fired at last.
148.creating a table, what is the difference between VARCHAR2(80) and VARCHAR2(80 BYTE)? Historically database columns which hold alphanumeric data have been defined using the number of bytes they store. This approach was fine as the number of bytes equated to the number of characters when using single-byte character sets. With the increasing use of multibyte character sets to support globalized databases comes the problem of bytes no longer equating to characters.Suppose we had a requirement for a table with an id and description column, where the description must hold up to a maximum of 20 characters.We then decide to make a multilingual version of our application and use the same table definition in a new instance with a multibyte character set. Everything works fine until we try of fill the column with 20 two-byte characters. All of a sudden the column is trying to store twice the data it was before and we have a problem.Oracle9i has solved this problem with the introduction of character and byte length semantics. When defining an alphanumeric column it is now possible to specify the length in 3 different ways: 1. VARCHAR2(20) 2. VARCHAR2(20 BYTE) 3. VARCHAR2(20 CHAR)Option 1 uses the default length semantics defined by the NLS_LENGTH_SEMANTICS parameter which defaults to BYTE. Option 2 allows only the specified number of bytes to be stored in the column, regardless of how many characters this represents. Option 3 allows the specified number of characters to be stored in the column regardless of the number of bytes this equates to.
151. how to insert a music file into the database
LOB datatypes can be used to store blocks of unstructured data like graphic images, video, audio, etc
152. what is diff between strong and weak ref cursors
A strong REF CURSOR type definition specifies a return type, but a weak definition does not. DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak
than emp%rowtype in weak cursor structure is not predetermined -- so we can query with any structure Strong Ref cursor type is less Error prone, because oracle already knows what type you are going to
return as compare to weak ref type.
154. Explain, Is it possible to have same name for package and the procedure in that package. Yes, its possible to have same name for package and the procedure in that package. 159. Without closing the cursor, If you want to open it what will happen. If error, get what is the error If you reopen a cursor without closing it first,PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN.
161. What is PRAGMA RESTRICT_REFERENCES:
By using pragma_restrict_references we can give the different status to functions,Like WNDB(WRITE NO DATA BASE),RNDB(read no data base),Write no package state,read no packge state.W
164. What is difference between PL/SQL tables and arrays? array is set of values of same datatype.. where as tables can store values of diff datatypes.. also
tables has no upper limit where as arrays has.
168. How do you set table for read only access ?
If you update or delete the records in the table, at the same time, no body can update or delete the same records which you updated or deleted because oracle lock the data which u updated or deleted.
Select for update
169. What are the disadvantages of Packages and triggers??
Disadvantages of Packages:
1. You cannot reference remote packaged variables directly or indirectly.. 2. Inside package you cannot reference host variable.. 3. We are not able to grant a procedure in package..
Disadvantages of Trigger:
1. Writing more number of codes..
170. How to disable a trigger for a particular table ? alter trigger <trigger_name> disable
172. how can we avoid duplicate rows. without using distinct command
Using Self join like :
select dup.column from tab a,tab b where a.dup.column=b.dup.column and a.rowid<>b.rowid
This query will return the first row for each unique id in the table. This query could be used as part of
a delete statement to remove duplicates if needed.
SELECT ID
FROM func t1
WHERE ROWID=(SELECTMIN(ROWID)
FROM func WHERE ID = t1.ID) Also: You can use a group by without a summary function SELECT ID
FROM func t1 GROUP BY id
Conceptually, INSTEAD OF triggers are very simple. You write code that the Oracle server will execute when a program performs a DML operation on the view. Unlike a conventional BEFORE or AFTER trigger, an INSTEAD OF trigger takes the place of, rather than supplements, Oracle's usual DML behavior. (And in case you're wondering, you cannot use BEFORE/AFTER triggers on any type of view, even if you have defined an INSTEAD OF trigger on the view.)
CREATE OR REPLACE TRIGGER images_v_insert
INSTEAD OF INSERT ON images_v
FOR EACH ROW
BEGIN
/* This will fail with DUP_VAL_ON_INDEX if the images table || already contains a record with the new image_id.
*/
INSERT INTO images
VALUES (:NEW.image_id, :NEW.file_name, :NEW.file_type,
:NEW.bytes);
IF :NEW.keywords IS NOT NULL THEN
DECLARE
/* Note: apparent bug prevents use of :NEW.keywords.LAST. || The workaround is to store :NEW.keywords as a local
|| variable (in this case keywords_holder.)
*/
keywords_holder Keyword_tab_t := :NEW.keywords;
BEGIN
FOR the_keyword IN 1..keywords_holder.LAST
LOOP
INSERT INTO keywords
VALUES (:NEW.image_id, keywords_holder(the_keyword));
END LOOP;
END;
END IF;
END;
Once we've created this INSTEAD OF trigger, we can insert a record into this object view (and hence into bothunderlying tables) quite easily using:
INSERT INTO images_v VALUES (Image_t(41265, 'pigpic.jpg', 'JPG',
824, Keyword_tab_t('PIG', 'BOVINE', 'FARM ANIMAL')));
This statement causes the INSTEAD OF trigger to fire, and as long as the primary key value (image_id = 41265) does not already exist, the trigger will insert the data into the appropriate tables.
Similarly, we can write additional triggers that handle updates and deletes. These triggers use the predictable clauses INSTEAD OF UPDATE and INSTEAD OF DELETE.
180. what is the difference between database trigger and application trigger? Database triggers are backend triggeres and perform as any event occurs on databse level (ex. Inset,update,Delete e.t.c) wheras application triggers are froentend triggers and perform as any event taken on application level (Ex. Button Pressed, New Form Instance e.t.c) 185. Compare EXISTS and IN Usage with advantages and disadvantages.
exist is faster than IN Command
exist do full table scan...so it is faster than IN
Use Exists whenever possible. EXISTS only checks the existence of records (True/False), and in the case of IN each and every records will be checked.  performace wise EXISTS is better.
189. Which type of binding does PL/SQL use?
it uses latebinding so only we cannot use ddl staments directly
191. Why DUAL table is not visible? Because its a dummy table.
Posted 19th December 2011 by Prafull Dangore 0 Add a comment