Skip to main content

Posts

Showing posts from August, 2011

References to DBMS Concepts

Many of you may have some concern on Basics of DBMS and about the definitions of R-DBMS, H-DBMS and N-DBMS. Some of you might have studied the concepts little different than the one I have defined on my site, but nevertheless definitions and concepts(DBMS) on my site are simple and easy to understand which are explained at a very basic level. Am collating some links which will provide similar and more detailed definitions and insight on DBMS concepts.

DBMS and its history -- http://en.wikipedia.org/wiki/Database_management_system
Types of DBMS -- http://en.wikipedia.org/wiki/Types_of_DBMS
http://sqlserver-guide.blogspot.com/2009/05/data-base-system-models-or-types-of.html
Notes from Penn State University, USA -- http://www.personal.psu.edu/glh10/ist110/topic/topic07/topic07_06.html
Flat File Database -- http://en.wikipedia.org/wiki/Flat_file_database
Hierarchical DBMS -- http://en.wikipedia.org/wiki/Hierarchical_database_model
Network DBMS -- http://en.wikipedia.org/wiki/Network_mode…

Abnormal Program Termination. An internal error has occurred. Please provide the following files to Oracle Support./ Installing Oracle 10g on Windows 7/Vista

This video will take you through the steps to install Oracle 10g Release 2 on Windows 7 (Same steps work for Windows Vista). Usually if you install Oracle database on window 7/Vista you would get an error,

Abnormal Program Termination. An internal error has occurred. Please provide the following files to Oracle Support.
"Unknown"
"Unknown"
"Unknown"

Follow the steps shown in this video to successfully install Oracle Database on Windows 7/Vista.




Scripts under COMMON_TOP/admin/scripts on 11i

Following are the different scripts under COMMON_TOP/admin/scripts/$CONTEXT_NAME and their usages,

1. adapcctl.sh --> Apache Startup/Shutdown script
2. adfrmctl.sh --> Forms server Startup/Shutdown script
3. adrmsctl.sh --> Forms metric server Startup/Shutdown script
4. adfmcctl.sh --> Forms metric client Startup/Shutdown script
5. adcmctl.sh --> Concurrent manager Startup/Shutdown script
6. adautocfg.sh --> Autoconfig Startup/Shutdown script
7. adtcfctl.sh --> TCF Socket Startup/Shutdown script
8. adaprstctl.sh -->  Start / Stop Apache Web Server Listener in restricted mode
9. addisctl.sh --> Discoverer Startup/Shutdown script
10. adalnctl.sh --> Applications RPC Listener process Startup/Shutdown script
11. jtffmctl.sh --> JTF Fulfillment server Startup/Shutdown script

12. adstrtal.sh --> Start all of the scripts above using this script
13. adstpall.sh --> Stop all the services from the above script using this script


Oracle Forms Web CGI: Error detected -- Error getting least loaded host

Oracle Forms Web CGI: Error detected

Your request cannot be serviced at this time due to the following error:

ERROR: Error getting least loaded host from Load Balancer Server.



This error occurs if Oracle forms metric server does not get updated information on existing clients.
In Forms metric server load balancing, always clients ping back the information about themselves and server just listens to it. In case for some reason none of the clients are alive or have not passed the information or if forms metric server is down then this error is observed.

Sometimes if forms metric server is restarted without restarting client metric server even then this error is observed.

You need to have the following information handy along with the sequence of services to be restarted. Always start the script in the order in which it is shown below.

1. adfrmctl.sh --> Forms server startup script
2. adfmsctl.sh --> Forms metric server startup script
3. adfmcctl.sh --> Forms metric client start…

What is Oracle RECYCLEBIN ?

RECYCLEBIN is one of the new and core feature of version 10G.

This is similar to recyclebin on Mircosoft Windows. In MS Windows temporarily deleted files are kept in recyclebin and you can selectively restore the files you want. Similar to this option Oracle came up with RECYCLEBIN where in dropped tables are kept in RECYCLEBIN and you can selectively restore the tables.


SQL> desc recyclebin
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 ORIGINAL_NAME                                      VARCHAR2(32)
 OPERATION                                          VARCHAR2(9)
 TYPE                                               VARCHAR2(25)
 TS_NAME                                            VARCHAR2(30)
 CREATETIME                                         VARCHAR2(19)
 DROPTIME                                           VARCHAR2(19)
 DROPSCN …

How to display single quotes in SQL or Handling single quotes in Oracle SQL

Most of them have been asking me how do I display single quote in a select statement, here we go.

Single quotes are used to represent literal strings so one should always know how to handle them, Oracle provides different ways of handling single quotes.

1. If you want to begin and end with single quote,

SELECT '''hello how are you''' FROM DUAL;
'hello how are you'
Notice that you have to use 3 single quotes in the beginning  and at the end.

2. If you want to display single quote in a using concatenation operator,

SELECT 'hi, how' || '''' || 's mithun' FROM DUAL;
hi, how's mithun
Notice that you have to use 4 single quotes here.

3. If you want to display single quote in between,

SELECT 'hey how''z dhruv doing' FROM DUAL;
hey how'z dhruv doing
Notice that you have to use 2 single quotes here.




Virtual Host Map has a mixture of HTTP and HTTPS Protocol origin servers

Major goofup on one of my portal servers, suddenly started seeing the following while restarting webcache and webcacheadmin. Protocol of Origin Servers were changed.


13001 Virtual Host Map has a mixture of HTTP and HTTPS Protocol origin servers. All origin servers for a virtual host map must use the same protocol.


This is caused due to virtual host map consists of origin servers with HTTP and HTTPS protocols. A virtual host mapping must consist of origin servers that use either the HTTP protocol exclusively or the HTTPS protocol exclusively.

To resolve this go to Origin Servers definitions page of OracleAS Web Cache Manager, correct the mapping.

1. If you do not have access to Webcache Administrator or if its down, open webcache.xml under ORACLE_HOME/webcache/.
2. Search for Origin Servers definition by searching for the hostname of one of your origin servers. Make sure SSLENABLED="NONE" if your protocol is HTTP for all your Origin Servers.
3. Once this is done restart OracleA…

ESI include fragment protocol does not match origin server protocol

Errors in webcache event_log,

[10/Aug/2011:16:29:55 +0530] [warning 11250] [ecid: 378117729099,0] ESI include fragment protocol does not match origin server protocol: Origin Server Protocol=http URL=https://xxxxxxx.in/portal/pls/portal/!PORTAL.wwpob_smd.has_privilege/selfcare/viewlans
[10/Aug/2011:16:29:55 +0530] [warning 11250] [ecid: 1168391710083,0] ESI include fragment protocol does not match origin server protocol: Origin Server Protocol=http URL=https://xxxxxxxxx.in/portal/pls/portal/PORTAL.wwv_setting.render_css?p_lang_type=NOBIDI&p_subscriberid=1&p_styleid=1&p_siteid=0&p_rctx=P



These messages are just warning and not errors. These messages are expected if you are using an external SSL server, meaning if SSL is implemented on the load balancer and not on your portal then these messages are expected in webcache event_log.

Answers for SUB Queries

1. SQL> select empno, ename from emp where deptno=(select deptno from dept where dname='RESEARCH');


2. SQL> select empno, ename from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));

3. SQL> select dname from dept where deptno in ( select deptno from emp where job ='ANALYST');

4. SQL> select empno, ename, mgr from emp where mgr = (select empno from emp where ename='JONES');

5. SQL> select empno, ename, mgr from emp where mgr = (select mgr from emp where ename='JONES')

6. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','ACCOUNTING'))

7. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','RESEARCH')) and empno in (select mgr from emp)

8. SQL> select empno, ename from emp where empno not in ( select mgr from emp where mgr is not null)

9. select…

Answers for SQL Functions

1. SQL> SELECT empno, ename FROM emp WHERE Length(ename) = 4;

2. SQL> SELECT empno, ename, job FROM emp where Length(job)=7;

3. SQL> SELECT Length('qspiders') - Length(replace('qspiders','s','')) FROM dual;

4. SQL>  SELECT empno, ename, job FROM emp WHERE Instr(job,'MAN') >0;

5. SQL> SELECT empno, ename, job FROM emp WHERE Instr(job, 'MAN') =1;

6. SQL> SELECT empno, ename, job FROM emp WHERE (Length(ename) - Length(Replace(ename, 'L',''))) = 1;

7. SQL> SELECT * FROM dept WHERE Instr(dname,'O') > 0;

8. SQL> SELECT Concat(ename,' working as a ') || Concat(job, ' earns ') || Concat(sal, '  in ') || Conc
at('dept ',deptno) AS text from emp;

OR

SQL> SELECT Concat(Concat(Concat(Concat(Concat(Concat(Concat(ename,' working as a '), job),' earns '), sal),'  in '),'dept '), deptno) AS text FROM emp;

9. SQL> SELECT empno, ename…

Answers for Basic SELECT with Conditions

Answers on Basic SELECT statement with Conditions:

1. SQL> SELECT * FROM emp WHERE ename LIKE 'S%';
2. SQL> SELECT * FROM emp WHERE ename LIKE '_L%';

3. SQL> SELECT * FROM emp WHERE ename LIKE '%E_';

4. SQL> SELECT * FROM emp WHERE ename LIKE '____';

5. SQL> SELECT * FROM emp WHERE ename LIKE '%L%';

6. SQL> SELECT * FROM emp WHERE ename LIKE '_____%';

7. SQL> SELECT * FROM emp WHERE sal BETWEEN 2000 and 3000;

9. SQL> SELECT * FROM emp WHERE mgr IS NULL OR COMM IS NULL;

10. SQL> SELECT * FROM emp WHERE mgr IS NULL AND COMM IS NULL;

11. SQL> SELECT * from emp WHERE job = 'MANAGER';

12. SQL> SELECT * from emp WHERE job = 'MANAGER' and deptno in (10,20);

13. SQL> SELECT * FROM emp WHERE job IN ('CLERK','ANALYST') AND sal >= 1000 AND deptno IN (20,30);

14. SQL> SELECT * FROM emp where deptno in (20,30) and comm IS NULL;

15. SQL> SELECT * FROM emp WHERE ename LIKE (…

Questions on BASIC SELECT with Conditions

Questions on Basic SELECT statement:

Syntax:
SELECT*|{[DISTINCT] column|expression [alias],...} FROMtable [WHEREcondition(s)];

1. Display all the employees whose name starts with 'S' 2. List the employees name having letter 'L' as the second character
3. List the employees name having 'E' as last but one character
4. List the employees name having exactly 4 letters
5. List the employee whose name is having letter 'L'
6. List the employees name having atleast 5 characters
7. List employees earning between 2000 and 3000
9. List emp who do not have any reporting manager or commision is Null
10. List emp who do not have any reporting manager AND commision is Null
11. List only managers
12. List managers working in dept 10 and 20
13. List all the clerks and analysts with salary atleast 1000 in dept 20 and 30
14. List the employees in dept 20 and 30 who get no commision
15. List employees whose name starts with either 'A' or 'S' in dept 20
16. Li…

Questions from Qspiders

(1) How do I list all of my tables?
(2) Create one table from another table without copying the data from the first table.
(3) How do I select from different user’s tables?
(4) What is SQL?
(5) What is a function in oracle?
(6) What is the default format of date in Oracle? How can I change my default date format?
(7) Difference between a)ROLLBACK & SAVEPOINT b)ROLLBACK & COMMIT c)Truncate & Delete d)Truncate & Drop e)Delete & Drop f)PK and (UNIQUE + Not Null) g)ROUND & TRUNC h)ROWID & ROWNUM i)Alternate Key & Candidate Key j)Simple Key & Composite Key k)Stored Procedure and Function l)Stored Procedure and Trigger m)Equi-Join and Outer Join n)CHAR & VARCHAR2 o)CASE Statements & DECODE p)UNION and UNION ALL
8) What is a view? What are its advantages?
9) What is a synonym?
10) What is PL/SQL?
11) What is an Index? Why it is useful?
12) What is PL/SQL?
13) Mention some features of Oracle 9i database.
14) Mention some features of Oracle 10g database.
15) What is the meaning …

Questions on BASIC SELECT Statement

BASIC SELECT Statement:
 Syntax:
SELECT*|{[DISTINCT] column|expression [alias],...} FROMtable;

1. Display all rows and all columns of emp table
2. Display any 2 columns of emp table
3. Calculate annual salary with Quarterly commission of 500
4. Display distinct salaries of all the employees
5. Display output as following,
      "Hello SMITH your salary is 5000"
6. Is the following statement correct,
       SeleCT ENAME,deptno FROM emp;
       select *,ename from emp;
       select ename deptno from emp;


CLICK HERE FOR ANSWERS
1. CLICK HERE FOR QUESTIONS ON BASIC SELECT 2. CLICK HERE FOR QUESTIONS ON BASIC SELECT WITH CONDITION 3. CLICK HERE FOR QUESTIONS FROM QSPIDERS 4. CLICK HERE FOR QUESTIONS ON FUNCTIONS 5. CLICK HERE FOR QUESTIONS ON SUBQUERIES 6. CLICK HERE FOR MORE QUESTIONS ON SQL

SQL Interview Questions on Subqueries

SUB Queries:
1. List the employees working in research department 2. List employees who are located in New York and Chicago
3. Display the department name in which ANALYSTS are working
4. Display employees who are reporting to JONES
5. Display all the employees who are reporting to Jones Manager
6. Display all the managers in SALES and ACCOUNTING department
7. Display all the employee names in Research and Sales Department who are having at least 1 person reporting to them
8. Display all employees who do not have any reportees
9. List employees who are having at least 2 reporting
10. List the department names which are having more than 5 employees
11. List department name having at-least 3 salesman
12. List employees from research and accounting having at-least 2 reporting
13. Display second max salary
14. Display 4th max salary
15. Display 5th max salary  -- Answer for nth Max Salary
Co-Related Subqueries:
16. Write a query to get 4th max salary from EMP table
17. Write a query to get 2nd…

SQL Interview Questions on Functions

SQL Functions:
Use only functions for data manupulation.
1. List employees whose name having 4 characters 2. List employees whose job is having 7 characters
3. Find out howmany times letter 'S' occurs in 'qspiders'
4. List the employees whose job is having last 3 characters as 'man'
5. List employees whose job is having first 3 characters as 'man'
6. Display all the names whose name is having exactly 1 'L'
7. Display dept names which are having letter 'O'
8. Display the output as shown below,
   Scott working as a clerk earns 3000 in dept 20
9. Display employees who earn odd numbered salaries
10. Display number of employees getting NULL comission.
11. Display total sal and comm drawn by dept 30
12. Count number of clerks in dept 10 and 20
13. List Department wise total salary
14. List department wise total sal only if the total sal is > 3000
15. Display job wise total salary excluding dept 30 only if the total salary is > 5000
16. Dis…