Category Archives: Queries

How to escape ampersand while updating blob in oracle

Posted on by | Leave a reply

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. :-).

set unused oracle

Posted on by | Leave a reply

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);

How to get Highest Paid Employee by Department

Posted on by | Leave a reply

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;