select * from all_source where lower(text) like '%proc_name%'
Category Archives: Oracle
Simple Oracle Function which Returns Boolean
Simple Oracle Function which Returns Yes or No
CREATE OR REPLACE FUNCTION isStudentInArmy(p_location IN VARCHAR(255), p_age IN LONG) RETURN CHAR IS isArmyMen CHAR(1); BEGIN SELECT CASE WHEN BodyType == 'Well Built and Strong' THEN 'Y' ELSE 'N' END INTO isArmyMen FROM tblMens WHERE Location = p_location AND Age = p_age; RETURN isArmyMen; END isStudentInArmy;
Package Creation in oracle
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;
How to escape ampersand while updating blob in oracle
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. :-).
Creating auto increment column in Oracle
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')
set unused oracle
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
I have a Table with rows like one below
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;