Ques 141. For this code to be successful, what must be true?
1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package. 2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package. 3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package. 4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
Is it helpful?
Add CommentView Comments
Ques 142. A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
1. DBMS_DDL 2. DBMS_DML 3. DBMS_SYN 4. DBMS_SQL
Is it helpful?
Add CommentView Comments
Ques 143. How to implement ISNUMERIC function in SQL *Plus ?
Method 1:
Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;
Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)
Method 2:
select instr(translate('wwww', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X') FROM dual;
It returns 0 if it is a number, 1 if it is not.
Is it helpful?
Add CommentView Comments
Ques 144. How to Select last N records from a Table?
select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm) where a > ( select (max(rownum)-10) from clm)
Here N = 10
The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.
Cursor rem_master(brepno VARCHAR2) IS select a.* from ter.ter_master a where NOT a.repno in (select repno from ermast) and (brepno = 'ALL' or a.repno > brepno) Order by a.repno
Is it helpful?
Add CommentView Comments
Ques 145. What are steps required tuning this query to improve its performance?
Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO
-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:
SELECT a.* FROM ter.ter_master a WHERE NOT EXISTS (SELECT b.repno FROM ermast b WHERE a.repno=b.repno) AND (a.brepno = 'ALL' or a.repno > a.brepno) ORDER BY a.repno;