Atchaya's Check Your Oracle Knowledge

Can we insert more than 1 row with 1 qyery in same table/diff tables? Yes, we insert more than 1 row with 1 qyery by using the following method? syntax INSERT ALL INTO tablename1 (fieldlist) VALUES (valuelist) INTO tablename2 (fieldlist) VALUES (valuelist) INTO tablename3 (fieldlist) VALUES (valuelist) SELECT * FROM DUAL; Example: insert all into student (sno, name) values (1, 'Raja') into student (sno, name) values (2 'Ravi') into student (sno, name) values (3, 'Balu') select * from dual; We can use more than 1 table also. insert all into student (sno, name) values (1, 'Raja') into student (sno, name) values (2 'Ravi') into emp (eno, name) values (1, 'Mani') select * from dual; Interview quesitions and answers, objective type questions with answers : http://atchaya.com/ora/orahome.html Oracle forum : http://atchaya.wikidot.com/forum/c-91091/discussion-about-oracle

Write a PL/SQL Program for the following output --*-- -***- ***** -***- --*-- Answer The output based on the value of 'n' declare n number(2) default 20; i number(2); j number(2); h number(2); s number(2); b varchar2(1) default 'I'; begin s := 1; if n mod 2 = 0 then n := n+1; end if; h := n/2 -1; for i in 1..n loop for j in 1..h loop dbms_output.put('-'); end loop; for j in 1..s loop dbms_output.put('*'); end loop; for j in 1..h loop dbms_output.put('-'); end loop; dbms_output.put_line(' '); if n = s then b := 'D'; end if; IF b='I' then s := s+2; h := h-1; else s := s-2; h := h+1; end if; end loop; end;

Evaluate the SQL statement DROP TABLE DEPT Which four statements are true of the SQL statement? A. You cannot roll back this statement. B. All pending transactions are committed. C. Al views based on the DEPT table are deleted. D. All indexes based on the DEPT table are dropped. E. All data in the table is deleted, and the table structure is also deleted. F.All data in the table is deleted, but the structure of the table is retained. G.All synonyms based on the DEPT table are deleted. Answer: A, B, D and E Explanation: A. You cannot roll back DROP TABLE statement. B. All pending transactions related on this table arecommitted. D. If the table is dropped, Oracle automatically drops any index, trigger and constraintassociated with the table as well. E. All data in the table is deleted, and the table structure is also deleted. Incorrect Answers C: All views based on the DEPT table become invalid, but they are not deleted. F: All data in the table is deleted, and the table structure is als...

I have updated some tables and before commit if I will give one DDL command means what will happen? For example CREATE TABLE test(fldname number(10)); insert into test (fldname) values (10); insert into test (fldname) values (20); After that without giving Commit statement, I issued one DDL command CREATE TABLE test1( sno number(10)) What aobut the previous inserts? Is it committed or not? explain. Answer: I checked this and found the following 3 points. Point No. 1: If we give a DDL command with syntax error means it will not commit and gives the error. For example the following statement CREATE TABLE test(fldname number(10), ); note that the comma, gives "invalid identifier.. " error and without committing the INSERT statements. Point No. 2: If the given DDL command executes without any error means it will auto commit the INSERT statements. Point No. 3: If we give a DDL command without any syntax error and that statement not execute also auto commit the INSERT st...

You are running a pl/sql block or procedure called parent In that procedure you called another one procedure namely child and that procedure contains commit statement. After calling the child procedure the transactions done in parent procedure are also committed due to the child procedure commit statement. But you want to rollback or commit the parent procedure whole transactions based on your requirement after the calling procedure child. In this situation, you should add the "pragma autonomus transaction" in child procedure. If you add this statement then it acts as a separate. If you use commit in child procedure it will not affect the parent procedure. Example : CREATE OR REPLACE PROCEDURE parent() AS BEGIN UPDATE tab1 SET fieldname = value WHERE condition; UPDATE tab2 SET fieldname = value WHERE condition; child(); UPDATE tab4 SET fieldname = value WHERE condition; IF parentcondition COMMIT; ELSE ROLLBACK; END IF; END. CREATE OR REPLACE PROCEDURE child() AS PRAGMA AUTONO...

Suppose Database D1 contains 100 tables.. How 2 select the tables which are modified at 10am today? 1. SELECT table_name, table_owner FROM all_tab_modifications WHERE to_char(timestamp,'YYYYMMDDHH24MM') = to_char(sysdate,'YYYYMMDD')'1000' This will gives the table names which are modified (insert, update, delete, truncated) at 10 AM. 2. SELECT scn_to_timestamp(max(ora_rowscn)) FROM ;