1 Q. SELECT SAL,DEPTNO
FROM EMP
GROUP BY DEPTNO;
Error. The columns which are in SELECT Statement those all should be in GROUP BY Clause
except the Group functions like sum(column_name) or max/min (column_name)
other wise it will raise an Error as " not a GROUP BY expression ".
A. The Query should be as
SELECT SAL,DEPTNO
FROM EMP
GROUP BY SAL,DEPTNO;
2 Q. CREATE TABLE X_EMP (EMPNO);
Error. While creating a new Table,if we do not mention data type for a particular column then
it will raise an Error as " need to specify the datatype for this column ".
A. The Creating Table should be as
CREATE TABLE X_EMP (EMPNO NUMBER / (WIDTH));
3 Q. SELECT * FROM ALL_TABLES
--, USER_TABLES
WHERE TABLE_NAME LIKE 'emp%'
Error. If we want to see the particual tabel data in TOAD OR SQL * PLUS
Then we should type Table / View name always in Upper / Capital Letters as
A. The Query should be as
SELECT * FROM ALL_TABLES
--, USER_TABLES
WHERE TABLE_NAME LIKE 'EMP%'
4 Q. Can we use sub-query in creating a Table ?.
A. YES, CREATE TABLE TABLE_NAME AS (SELECT * FROM EMP)
OR, CREATE TABLE TABLE_NAME AS (SELECT EMPNO,ENAME FROM EMP)
5 Q. Can we use Inline-View in inserting a Table ?.
A. YES, INSERT INTO TABLE_NAME (SELECT * FROM EMP WHERE DEPTNO=30)
or, INSERT INTO TABLE_NAME (SELECT empno,ename FROM EMP DEPTNO=30).
...Package Error
6 Q. CREATE OR REPLACE PACKAGE PJANA -- PKG SPECIFICATION
AS
PROCEDURE P1(P_OPERATING_NAME IN VARCHAR2);
PROCEDURE P2(P_PAY_LOOKUP_CODE IN VARCHAR2);
END;
CREATE OR REPLACE PACKAGE BODY PJANA -- PKG BODY
AS
PROCEDURE P1(P_OPERATING_NAME IN VARCHAR2)
IS
BEGIN
NULL;
END;
END;
Error. While creating PACKAGE if we take 2 Procedures or Functions in PACKAGE SPECIFICATION,
Then if we do not declare one of the procedures or functions in PACKAGE BODY
Then it will raise an Error as
" subprogram or cursor 'P2' is declared in a package specification and must be defined in the package body ".
A. The PACKAGE BODY Should be as
CREATE OR REPLACE PACKAGE BODY PJANA -- PKG BODY
AS
PROCEDURE P1(P_OPERATING_NAME IN VARCHAR2)
IS
BEGIN
NULL;
END;
PROCEDURE P2(P_PAY_LOOKUP_CODE IN VARCHAR2)
IS
BEGIN
NULL;
END;
END;
7 Q. How to see the Particual Operating Unit data in TOAD By using View table not Base table ?.
By enabling as
A. BEGIN
MO_GLOBAL.SET_ORG_CONTEXT(101,null,'ONT'); -- 101 is ORG_ID, ONT IS Application Short Name.
END;
8 Q. DECLARE
V NUMBER(8);
BEGIN
SELECT SAL INTO V FROM EMP WHERE EMPNO=7782;
DBMS_OUTPUT.PUT_LINE(V);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('there is no record found');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no data found in table');
END;
Error. if we use OTHERS Exception first at EXCEPTION Block when there are more than 2 Exceptions
then it will raise an Error as
" OTHERS handler must be last among the exception handlers of a block ".
A. DECLARE
V NUMBER(8);
BEGIN
SELECT SAL INTO V FROM EMP WHERE EMPNO=7782;
DBMS_OUTPUT.PUT_LINE(V);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no data found in table');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('there is no record found');
END;
9 Q. DECLARE
V NUMBER(8);
BEGIN
SELECT SAL INTO V FROM EMP WHERE EMPNO=7782;
DBMS_OUTPUT.PUT_LINE(V);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no data found in table');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no data found in table');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('there is no record found');
END;
Error. If we use one Exception twice at EXCEPTION Block
then it will raise an Error as
" exception 'NO_DATA_FOUND' may appear in at most one exception handler in this block ".
A. So, we need not mention one Exception name twice at Exception block.
10. What will be the output by using as
DECLARE
BEGIN
...........
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
A. we can simply know or find out what type of Exception will give us particularly.
11 Q. DECLARE
CURSOR C IS SELECT EMPNO FROM EMP;
CURSOR C IS SELECT DNAME FROM DEPT WHERE DEPTNO=10;
BEGIN
FOR I IN C LOOP
DBMS_OUTPUT.PUT_LINE(I.EMPNO);
END LOOP;
FOR J IN C LOOP
DBMS_UTPUT.PUT_LINE(J.DNAME);
END LOOP;
END;
Error. If we declare same Cursor name twice at DECLARE Section for different purpose
Then it will raise an Error as
" too many declarations of 'C' match this call ". -- 'C' Means Cursor Name.
A. DECLARE
CURSOR C IS SELECT EMPNO FROM EMP;
CURSOR C1 IS SELECT DNAME FROM DEPT WHERE DEPTNO=10;
BEGIN
FOR I IN C LOOP
DBMS_OUTPUT.PUT_LINE(I.EMPNO);
END LOOP;
FOR J IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(J.DNAME);
END LOOP;
END;
12 Q. BEGIN
DELETE EMP WHERE DEPTNO=10;
-- UPDATE JANA SET COMM=9 WHERE DEPTNO=10;
-- INSERT INTO JANA(EMPNO,SAL) VALUES(3,3);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' '||'rows are deleted');
DBMS_OUTPUT.PUT_LINE(SQL%NOTFOUND);
DBMS_OUTPUT.PUT_LINE(SQL%FOUND);
END;
Error. While using any DML Operations on a Base Table ,If we use Implicit Cursor attributes as SQL%NOTFOUND (OR) SQL%FOUND to display
Then it will raise an error as " wrong number or types of arguments in call to 'PUT_LINE' ". so we can not use these two Implict Cursors to display
by using as DBMS_OUTPUT.PUT_LINE(SQL%NOTFOUND); (OR) DBMS_OUTPUT.PUT_LINE(SQL%FOUND);
A. BEGIN
DELETE EMP WHERE DEPTNO=10;
-- UPDATE JANA SET COMM=9 WHERE DEPTNO=10;
-- INSERT INTO JANA(EMPNO,SAL) VALUES(3,3);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' '||'rows are deleted');
END;
13 Q. DECLARE
v_sal number(3);
BEGIN
SELECT SAL INTO v_Sal FROM JANA WHERE EMPNO=7788;
DBMS_OUTPUT.PUT_LINE('Employee salary:'||' '||v_sal);
END;
Error. While declaring Size or Width of a particular Data Type at declaration section if we give less size for a data type when actual size is more
Then it will raise an Error as " numeric or value error: number precision too large ".
A. DECLARE
v_sal number(10); (or) v_sal number;
BEGIN
SELECT SAL INTO v_Sal FROM JANA WHERE EMPNO=7788;
DBMS_OUTPUT.PUT_LINE('Employee salary:'||' '||v_sal);
END;
14 Q. SELECT * FROM EMP WHERE ROWNUM=6
Error. While using ROWNUM Pseudo column,If we want to display 6th row of a table it does not work with as ROWNUM=6
Then it will give Empty row. but how to display 6th row, by using MINUS SET Operators
Note>. ROWNUM works only with < ,<=, = 1 (first row only) but does not work with >,>=,
A. SELECT * FROM jana WHERE ROWNUM<6 --first 5 rows will be deleted but 6 is remaing to display.
MINUS
SELECT * FROM jana WHERE ROWNUM<5 -- first 5 rows will be deleted.
15 Q. Can we pass DEFAULT values to a Parameterised Cursor or Cursor Parameter ?.
Note. While using Cursor Parameter we can not use OUT , INOUT Parameters.
A. Yes, we can pass DEFAULT values to a Cursor Parameter as following
DECLARE
CURSOR C (P_DEPTNO IN NUMBER DEFAULT 10)
IS
SELECT ENAME
FROM JANA
WHERE DEPTNO=P_DEPTNO;
BEGIN
FOR I IN C LOOP
DBMS_OUTPUT.PUT_LINE(I.ENAME);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;
16 Q. What type of Data type can we use for Outbound Interface ?.
A. We can use this UTL_FILE.FILE_TYPE is a built-in data type for Outbound Interface.
17 Q. Can we use Group By clause while creating Value Set ?.
A. No, In a Value Set Table Type at Where class section, if we use Group by class then it will raise an error
So we can not use Group by class but we can use WHERE (Or) Order By clauses only.
. At Where / Order by Section as " where deptno <> 20 order by deptno desc "
18 Q. What is the Link between Inventory (INV) AND Order Management ( ONT) Applications ?.
A. SELECT
msib.segment1
,oola.ordered_quantity
FROM
OE_ORDER_LINES_ALL oola
, MTL_SYSTEM_ITEMS_B msib
WHERE
oola.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
AND oola.SHIP_FROM_ORG_ID = msib.ORGANIZATION_ID
19 Q. What is the Link between Inventory (INV) AND Purchase Order ( PO ) Applications ?.
A. SELECT
msib.segment1, pla.quantity
FROM
MTL_SYSTEM_ITEMS_B msib
, PO_LINES_ALL pla
, PO_LINE_LOCATIONS_ALL plla
WHERE
msib.INVENTORY_ITEM_ID = pla.ITEM_ID
AND msib.ORGANIZATION_ID = plla.SHIP_TO_ORGANIZATION_ID;
No comments :
Post a Comment