Category: Oracle-Tips and Tricks

T-SQL vs. PL-SQL

Oracle, Oracle-Tips and Tricks, SQL, SQL-Tips and Tricks, Varinder Sandhu

T-SQL TSQL is a proprietary procedural language used by Microsoft in SQL Server. PL-SQL PL/SQL is a proprietary procedural language used by Oracle Microsoft’s T-SQL is similar to Oracle’s PL-SQL to some extent, sharing with some basic difference as below Difference in data type, There are many different data type. Sharing few as below … T-SQL PL-SQL Integer, SmallInt etc Number Varchar Varchar2 DATETIME, SMALL-DATETIME Date   The default date format of PL-SQL does not include time part. In T-SQL,

» Read more
,

Book – Oracle 2 Day DBA

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

About this Book Oracle 2 Day DBA is a database administration quick start guide that teaches you how to perform common day-to-day database administrative tasks. The goal of this book is to give you adequate information to help you understand the concepts behind the Oracle Database. It teaches you how to perform all common administration tasks needed to keep the database operational, including how to perform basic troubleshooting and performance monitoring activities. The primary administrative interface used in this book

» Read more
, , ,

Oracle – Schema vs User

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

For all intents and purposes you can consider a user to be a schema and a schema to be a user. A schema is the set of objects (tables, views, indexes, etc) that belongs to an user account. It is often used as another way to refer to an Oracle account. CREATE USER command creates a user. It also automatically creates a schema for that user. We can call as empty schema. The CREATE SCHEMA command allows creating multiple tables

» Read more
, ,

Oracle – DBA Basics

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

First I would like to share some background of this post. As I have SQL Server background (you can see in my profile here) and when I started learning Oracle. I have so many basic questions regarding oracle environment. Even I was little lit bit confused about few things. But when I got the answer of my question then I thought I should write here on my blog. Oracle and SQL server are very different products.  There is no comparison

» Read more
, , , , ,

Oracle – PL-SQL – Foreign key on delete cascade

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

ON DELETE CASCADE: When a referenced parent table row is removed the entire child are removed automatically. Example: — Create a Table as mytable1 having the PRIMARY KEY CREATE TABLE MYTABLE1 ( ID INTEGER PRIMARY KEY, EMP_NAME VARCHAR2(10) ); — Create a Table as mytable2 having the FOREIGN KEY CREATE TABLE MYTABLE2 ( ID INTEGER, ADDRESS VARCHAR2(50), FOREIGN KEY (ID) REFERENCES MYTABLE1 (ID) ON DELETE CASCADE ); — Insert one row in mytable1 INSERT INTO MYTABLE1 (ID, EMP_NAME) VALUES (1,'VARINDER');

» Read more
, ,

Oracle – Drop User Cascade

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

If you have user that owns objects and you want to drop the user then you have to use the Cascade keyword with your drop statement/command. The Oracle DROP USER CASCADE command drops a user and all owned objects. Without using DROP USER CASCADE you cannot delete the user that owns objects. Example: First you try to drop the user without CASCADE then below error occurred because my_user user own the my_table object (as shown above) But now use the

» Read more
,

Oracle – PL- SQL – Get the Second highest/ largest Value from column

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

How to get the Second highest/ largest Value from the column ? Example: See the table values in the snapshot. As per requirement we need to get second highest/ largest salary form the column sal which is 3000 as per column values. Solution: Script: select ename,sal from ( select ename, sal, row_number()over(order by sal desc) rn from emp ) x where rn = 2;

» Read more
, ,

Oracle – PL-SQL – Trigger – After Insert OR Update OR Delete

Oracle, Oracle-Tips and Tricks, Oracle-Triggers, Varinder Sandhu

We can create a trigger with multiple actions (i.e.  Insert or Update or Delete) here sharing an example of trigger in which we can handle three actions as insert, update and delete. Example: CREATE OR REPLACE TRIGGER EMPLOYEE_AI_AU_AD AFTER INSERT OR UPDATE OR DELETE ON EMPLOYEE FOR EACH ROW DECLARE V_Msg VARCHAR2(30) := 'Row Level Trigger Fired'; BEGIN IF INSERTING THEN dbms_output.put_line(V_Msg || ' After Inserting'); ELSIF UPDATING THEN dbms_output.put_line(V_Msg || ' After Updating'); ELSIF DELETING THEN dbms_output.put_line(V_Msg || '

» Read more
, , , ,

Oracle – Introduction to Databases, tablespaces, and datafiles

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

Databases, tablespaces, and datafiles are closely related, but they have important differences. It is important to know the differences for Newbie that‘s why sharing here. Databases:  An Oracle database consists of one or more logical storage units called tablespaces that collectively store all of the database data. Tablespace: Each tablespace in an Oracle database consists of one or more files called datafiles. Datafiles:  A database’s data is collectively stored in the datafiles that constitute each tablespace of the database. For

» Read more
, , ,

Oracle – How to copy a table

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

— CREATE TABLE CREATE TABLE EMPLOYEE ( ID NUMBER NOT NULL, FIRST_NAME VARCHAR2(10 BYTE), LAST_NAME VARCHAR2(10 BYTE) ); — INSERT SOME VALUES INTO TABLES INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (1,'VARINDER','SANDHU'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (2,'DINESH','SHARMA'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (3,'RANJOYT','SINGH'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (4,'VIKRAM','SINGH'); — CREATE A COPY OF EMPLOYEE TABLE WITH DATA CREATE TABLE EMPLOYEE_COPY AS SELECT * FROM EMPLOYEE; — CHECK THE TABLE SELECT * FROM EMPLOYEE_COPY; — CREATE A COPY OF EMPLOYEE TABLE

» Read more
,
1 2 3