Package creation in Oracle is a TWO step Process.
1.Declaration of Package
2.Definition of Package

Declaration of Package

  CREATE OR REPLACE PACKAGE TEST IS
    PROCEDURE JUNITPROC(C OUT SYS_REFCURSOR);
  END TEST;

Definition of Package

  CREATE OR REPLACE PACKAGE BODY TEST IS
  PROCEDURE JUNITPROC(C OUT SYS_REFCURSOR) IS
  BEGIN  
    OPEN C FOR
      SELECT 78985450.1245487986418648 DECIMAL_VAL FROM DUAL;
  END JUNITPROC;
END TEST;

While storing the HTML code in oracle db the column type should be CLOB rather than BLOB. Incase if you have used BLOB for storing the HTML code while carrying out update you should first convert the content in to hexadecimal values as below.

  SELECT (RAWTOHEX(UTL_RAW.CAST_TO_RAW('Test'))) 
    FROM DUAL;

The above code returns Hexadecimal value for test as below
54657374

Now if you want to update the Hexadecimal you need to do the same thing.

UPDATE TemplateTbl
   SET TemplateConetent = (RAWTOHEX (UTL_RAW.cast_to_raw ('Mugil   Nikkhil')))
   WHERE TemplateId = TL2600

The above code seems to be fine but it does not work as the   would be interrupted as if a variable prompting you to enter the variable value.

To overcome this issue   should be replace with &  as below

UPDATE TemplateTbl
   SET TemplateConetent = (RAWTOHEX (UTL_RAW.cast_to_raw ('Mugil &  Nikkhil')))
   WHERE TemplateId = TL2600

Cheers. We are Done. :-).

You can create autoincrement column in oracle in two ways.

Method 1 : Using sequence

Table Creation Query

 CREATE TABLE tblusers(UserId INT, UserName VARCHAR(50));

Sequence Creation Query

  CREATE SEQUENCE tblusers_userid_seq 
         MINVALUE 1 MAXVALUE 999999999999999999999999999 
         START WITH 1 INCREMENT BY 1 CACHE 20;

The above code creates a sequence for table tblusers with name tblusers_userid_seq with start value of 1 and limit 999999999999999999999999999.

Insertion Query

  INSERT INTO tblusers(UserId, UserName) VALUES (tblusers_userid_seq.nextVal, 'John')

Method 2 : Using Trigger
In Method 2 we can use triggers to carry out auto increment when rows are added to the table.

Before creating trigger you should make the auto increment column as primary key.For that the code is as below

  ALTER TABLE tblusers ADD (
  CONSTRAINT UserId_pk PRIMARY KEY (UserId));

The Creation of sequence and trigger is as follows

CREATE SEQUENCE tblusers_userid_seq;  
CREATE OR REPLACE TRIGGER tblusers_userid_trigg 
BEFORE INSERT ON tblusers 
FOR EACH ROW
BEGIN
  SELECT tblusers_userid_seq.NEXTVAL
  INTO   :new.UserId
  FROM   dual;
END;

Now during insertion there is no need to worry about the auto increment column and the insert query no need to contain the Id column as below

  INSERT INTO tblusers(UserName) VALUES ('John')

On Large tables dropping a column is time consuming as it needs to work on lot of records.To prevent this you can perform a logical delete of the column by Using the SET UNUSED.

ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);  

Once this is done the column wont be visible to users.

Later you can run a query to delete unused columns from tables

ALTER TABLE table_name DROP UNUSED COLUMNS;

In case you are working with table with less number of rows we can use drop command to drop the table column which performs Physical delete rather than using SET UNUSED which drops the column later

ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);

Alter table to change Column Type

ALTER TABLE emp MODIFY ename varchar2(50);

A NULL is a value for which comparison yields neither a true nor false result.NULL = NULL is not true. It’s not false either. The outcome of any comparison of one value to a NULL value is a NULL result.

If you want to check a Column for NULL value then use the following query

Query1

SELECT IFNULL(ColName, '') AS Names
  FROM TableName 

Query2

SELECT CASE WHEN ColName IS NULL
            THEN ''
            ELSE ColName
       END AS TableName 
FROM Test

Query3

 SELECT COALESCE(ColName, 0) as 'ColName' FROM TableName;

Query4

 SELECT IF(ColName IS NULL, '', ColName)
   FROM TableName 

If you already created a Table but you havent added foreign key constraint then you can do that by
running the following statement in MySQL

Run this before you run the FK Query Constraint

SET FOREIGN_KEY_CHECKS = 0; 

Run the FK Query

ALTER TABLE tbl2Name 
  ADD CONSTRAINT fkConstraint FOREIGN KEY(fkcolumnName) 
      REFERENCES tbl1Name(pkcolumnName);

Run the below code again

 SET FOREIGN_KEY_CHECKS = 1; 

I have a Table with rows like one below

Image

I want to have Highest paid employee in Each Department

EmpName      Designation         Salary
Phillipe           Analyst                    3150
Scott              Clerk                        2500
Mac                Manager                  5000

Query 1(Oracle Specific)

SELECT EmpName, Designation, Salary
  FROM (SELECT EmpName, Designation, Salary,
               RANK() OVER (PARTITION BY Designation ORDER BY Salary DESC) AS rn
          FROM Employees)
 WHERE rn = 1
 ORDER BY Designation;

Query 2

 SELECT EmpName, Designation, Salary
   FROM Employees
  WHERE (Designation, Salary) IN (SELECT Designation, MAX(Salary)
                                   FROM Employees
                                  GROUP BY Designation)
 ORDER BY Designation;

Query 3(Oracle Specific)

SELECT max(EmpName) keep (dense_rank last order by salary) AS EmpName,
       Designation, max(Salary) keep (dense_rank last order by salary)  AS Salary
  FROM Employees
 GROUP BY Designation;

Query 4(Oracle Specific)

SELECT max(EmpName) keep (dense_rank last order by salary) AS EmpName,
       Designation, max(Salary) keep (dense_rank last order by salary)  AS Salary
  FROM Employees
 GROUP BY Designation;
CREATE TABLE ipaddress(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                       date VARCHAR(255),
                       ip VARCHAR(255));

INSERT INTO ipaddress(date, ip)
               VALUES('1-1-2012', '195.165.1.2'),
                     ('1-1-2012', '195.165.1.3'),
                     ('12-2-2012', '195.165.1.8');

Total number of Visits should be grouped based on IP Address

SELECT COUNT(ip), MONTHNAME(STR_TO_DATE(date, '%c-%e-%Y')) as period
  FROM ipaddress
 GROUP BY period;

The first Location from second table should be added to the first table Location.

CREATE TABLE projectlist(ProjectId   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                         ProjectName VARCHAR(50),
                         Location    VARCHAR(50));
                             
CREATE TABLE LocationList(LocaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                          ProjectId INT,
                          Location    VARCHAR(50));

INSERT INTO projectlist(ProjectName)
                 VALUES('Project A'),
                       ('Project B'),
                       ('Project C'),
                       ('Project D'),
                       ('Project E'),
                       ('Project F'),
                       ('Project G'),
                       ('Project H');
                           
INSERT INTO LocationList(ProjectId, Location)
                  VALUES(1, 'Location A'),
                        (1, 'Location C'),                      
                        (3, 'Location B'),
                        (4, 'Location C'),
                        (5, 'Location D'),
                        (2, 'Location C'),
                        (2, 'Location B'),
                        (2, 'Location A'),
                        (6, 'Location A'),
                        (6, 'Location B'),
                        (7, 'Location B'),
                        (8, 'Location D'),
                        (8, 'Location A');

Query 2

UPDATE projectlist JOIN (SELECT LocationList.ProjectID,
                                LocationList.Location
                           FROM LocationList
                           JOIN (SELECT LocationList.ProjectID,
                                       min(LocaId) minLocaId
                                  FROM LocationList
                                 GROUP BY LocationList.ProjectID) l1
                             ON LocationList.LocaId = l1.minLocaID) l2 ON projectList.ProjectID = l2.ProjectID
   SET ProjectList.Location = l2.Location;
Posted in SQL.