Few queries of Oracle SQL(WIP)

1. Get count of weekdays or weekends in a month. I have used sysdate here to get the count for current month, you can replace sysdate with any other date as per your requirement.


-- For weekends in current month
SELECT COUNT(1)
FROM
  (SELECT TO_CHAR(to_date(level
    ||TO_CHAR(sysdate,'Mon-yyyy'),'dd-Mon-yyyy'),'DAY') AS m_date
  FROM dual
    CONNECT BY level <= TO_CHAR(last_day(sysdate),'dd')
  )
WHERE trim(m_date) IN ('SATURDAY','SUNDAY');


-- For weekdays in current month
SELECT COUNT(1)
FROM
  (SELECT TO_CHAR(to_date(level
    ||TO_CHAR(sysdate,'Mon-yyyy'),'dd-Mon-yyyy'),'DAY') AS m_date
  FROM dual
    CONNECT BY level <= TO_CHAR(last_day(sysdate),'dd')
  )
WHERE trim(m_date) NOT IN ('SATURDAY','SUNDAY');

2. Get 1 to 100 number using a SQL query

SELECT level  FROM dual CONNECT BY level <= 100

Create Dynamic Oracle Reports (Change Tables at Runtime)

Dear Friends,
                      If you want to create dynamic oracle reports then its very easy using record group and assigning them to reports from oracle forms. The steps are as follows:-

We will take an example here, consider a reports containing three fields as shown in query:-
Step 1:-
select emp_name, desig,dob from company.emp_mst;

Now you can see you have selected three columns from company.emp_mst in Q_EMP (Queryname in report).
Consider you want the same reports from a different table named contractor.user_mst.
Then what all you have to do is leave the report as it is and come to forms (from which you are calling this report.)

Step 2:-
In forms take two radio buttons as:-

Now while calling the report :-

---------------------------------------------------------------------------------------------------------

declare

rg recordgroup;

gc1 groupcolumn;

gc2 groupcolumn;

gc3 groupcolumn;

q1 varchar2(500);

j number;

begin

---------- Change queries based on user input ----------------------

if :radio_btn='1' then

q1:='select emp_name,desig,dob from company.emp_mst';

else

q1:='select emp_name,desig,dob from contractor.user_mst';

end if;

rg := Find_Group( 'rng' ); 

  IF NOT Id_Null(rg) THEN 

    Delete_Group( rg ); 

  END IF; 

-------------- create columns ----------------------------------

rg := create_group('rng');

gc1 := add_group_column(rg,'EMP_NAME',char_column,50);

gc2 := add_group_column(rg,'DESIG',char_column,30);

gc3 := add_group_column(rg,'DOB',date_column);

--------------- Populate record group-----------------------

 j := Populate_Group_With_Query( rg,q1);

/* And now after creating paramlist and adding parameters we add another line 
for sending record group as data parameter */

Add_Parameter(pl_id, 'Q_EMP', DATA_PARAMETER, 'rng');

-----------------------------------------------------------------------------------------------------------------------

Q_EMP is name of the query you have created in report and rng is the name of record group created here in forms.

Now compile and run the report.

Note:- The datatype and number of columns should be same as in report and record groups.

PL/SQL Interview Questions

Dear Friends,
                   I am going to share some important questions which are frequently asked in PL/SQL interview:-

1. Can we use rownum with between command ?
Ans:- Yes  e.g.  select * from tab_name where rownum between 1 and 5.

2. Can we use commit in Trigger ?
Ans:- Yes for that we have to declare a pragma.

3.What is difference between primary key and unique key not null ?

Ans:- A unique key by default will be created as a non-clustered index and a primary key will be created as a clustered index by default.

4. Which one is faster between Truncate and Delete and why ?

Ans:- Truncate is faster because it drops the table and recreates it. Truncate is a DDL command.

5. Do we need to write commit after using DDL commands ?

Ans:- No. DDL itself performs commit before and after execution, if any there is any pending DML then that will be automatically commited.

6. What will be the impact of following statement:-

 create table t2 as select * from t1 where 1=2;  ?

Ans:- It will create an empty table t2 identical to t1.

7. Why we use Foreign key ?

Ans:- for referential integrity of table.

8. Why indexes are used?

Ans:-For faster access to data.

9.What is NVL function?

Ans:- NVL- Null Value Function provides a default value to the column when it returns null.

e.g. select nvl(name,'Ram') from emp_mst;

This query will return Ram in place of null values.

10. What are mutating tables?

Ans:- Tables which are being modified by any DML are Mutating table. 

PDE-PPU007 Cannot destroy compiled state

Hello Friends,
                        I got a typical error  PDE-PPU007 Cannot destroy compiled state while using Oracle reports 6i.

On this error every thing works properly except prompting this message. So don't get panic the solution of this problem is very simple.
1. Identify the line of code where this error occurs using debug messages.
 i.e. message('error message'); in forms and  srw.message(1,'error message'); in reports.
use these messages between every lines of suspicious code (where you can expect this error).

2. Comment the line of code when detected.
3. Compile it using CTRL+SHIFT+K and save it.
4. Now uncomment the line code and  again Compile it using CTRL+SHIFT+K and save it.
5. Run it. It is solved now.  

Interview Basics for Oracle developer

Introduction:-

SQL stand for structured query language i.e. a language that is used to query from database and is supported by every database. This tutorial will give complete reference about SQL and complex queries.

It is used to store, manipulate and retrieve the data in RDBMS. It is standard language for all Relational databases.

SQL Command:-

These are the codes which defines type of action on database like insert, update, delete etc.

SQL command can be classified into five groups:-

DDL (Data Definition language):-Creates or modifies any object like tables, views, synonyms etc. in database e.g. create, alter, truncate, drop.

DML (Data Manipulation Language):- Make changes in Data in RDBMS e.g. insert, update, delete.

DCL (Data Control Language):- It is used to give privileges or take it back from users e.g. grant, revoke.

DQL (Data Query language):- It is used to retrieve data from database e.g. select.

TCL (Transaction Control Language):- COMMIT and ROLLBACK commands comes in this section as former is used to make the changes permanent and later erases all unsaved data before commit.

Note: - Commit and Rollback can only affect the changes done by DMLs.

RDBMS:-

It is Relational Database Management System. It is Database Management System which follows relational model given by E.F. Codd.

For example:- Oracle, MySQL, MS SQL Server, IBM DB2 etc.

Some Important definition:-

Table:- It is collection of related data stored in rows and columns. It is the base of RDBMS.

Field:- It is the smallest unit of table. It contains particular information of the record  e.g. name varchar2 (50) in the table employee.

Column:-It is vertical part of the table which contains all the values of particular field.

Row:-It shows one record of the table and also the horizontal part.

Create table is a command that is used to create table, view, triggers, procedures, functions and other database objects in the database. We will discuss every this one by one.

Create Table:-

Code:-

Create table table_name (column_name  datatype(size));

Example:- create table students (name varchar2(50),code number(4), address varchar2(100), class varchar2(10));

The above code will create a table named students having four columns as name, code, address, class.

Insert Command:-

Insert command is used to insert a new record in the table.

Code:- insert into students(name, address, code, class) values (‘Sandeep’,1,’Sec 65 Noida’, ’Tenth’).

      Insert into students (name, address, code) values (‘Hari’,2,’NSP New Delhi’);

In the above code the previous line will insert a record in all the field and the later one will insert in the three field given before values.

The sequence of field name and values should follow the same order so that it can match the data.

#Note:- commit is necessary after all the DML commands otherwise the data will remain unchanged.

Select Command:-

Select command is used to retrieve the data from the table. The syntax of the select command is as follows:-

Select * from students;

It will display all the data of the table as for now it has only one row so it will display:-

Sandeep	1	Sec 65 Noida	Tenth
Hari	2	NSP New Delhi

To select particular column we can use it like:-

Select name from students; Result:-

Sandeep

Hari

Where Command:-

Where command is used to filter the result of select command based on condition provided, it makes our result precise and narrow. The syntax is as follows:-

Select name from students where code=1;

Result:-Sandeep.

Select name from students where code=2;

Result:-Hari.

Select address from students where code=5;

Result:- null. (Reason there is no record the table with the code=5.)

#Note:- null is unknown value to database.

AND Command:-

And command is used to with where command when two or more condition is used. The syntax is as follows:-

Select name from students where code=1 and class=’Tenth’;

Result:-Sandeep.

You can see above we have two conditions first code=1 and second class=’Tenth’. Both of the condition must be true to display the result. If we take an example as:-

Select name from student where code=2 and class=’Tenth’;

Result:-null; (Because both conditions are not satisfied.)