SQL/PLSQL

How to get IP Address , Computer Name,Database User, Oracle, Database user ID, Database Name n Oracle?

You can get IP Address , Computer Name,Database User, Oracle, Database user ID, Database Name n Oracle by using these commands; SELECT UPPER(GET_CONTEXT_VALUE(‘TERMINAL’)) COMPUTER_NAME FROM DUAL; SELECT UPPER(GET_CONTEXT_VALUE(‘SESSION_USER’)) ORACLE_USER_NAME FROM DUAL; SELECT UID ORACLE_USER_ID FROM DUAL; SELECT UPPER(GET_CONTEXT_VALUE(‘OS_USER’)) OS_USER_NAME FROM DUAL; SELECT SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’) IP_ADDRESS FROM DUAL; SELECT HOST_NAME DB_NAME FROM V$INSTANCE;  

How to Delete Data in Oracle with cursor?

In Oracle we can delete data with two ways one is simple delete statement and second is with use of cursor. Both codes are available below down. With Simple Statement: DELETE FROM EMP WHERE EMP_CODE= 101; With Cursor: DECLARE CURSOR C1 IS SELECT EMP_CODE,EMP_SALARY FROM EMP; BEGIN FOR I IN C1 LOOP DELETE FROM  EMP_SAL […]

How to update data with cursor in Oracle?

How to Update Data by using Cursor? With simple update statement we can update only simple data but with cursor we can update data with reference. Sample Code: ====================================== SQL> CREATE TABLE  EMP 2  ( 3  EMP_CODE        NUMBER, 4  EMP_NAME        VARCHAR2(100), 5  EMP_SALARY      NUMBER, 6  USER_NAME       VARCHAR2(100), 7  DATE_TIME       DATE 8  ); Table created. SQL> […]

How to make Procedure in Oracle?

Oracle Procedure: Procedures are a combination of SQL and PL/SQL. The difference between in procedure and function is that function should return the value but in procedure return is optional. The sample code is given below. Functions are normally used for computations where as procedures are normally used for executing business logic. Main difference between […]

How to make Function in Oracle?

Oracle Functions: Functions are a combination of SQL and PL/SQL. The difference between in procedure and function is that function should return the value but in procedure return is optional. The sample code is given below. ——————————————————————————– SQL> CREATE TABLE  EMP 2  ( 3  EMP_CODE        NUMBER, 4  EMP_NAME        VARCHAR2(100), 5  EMP_SALARY      NUMBER, 6  USER_NAME       VARCHAR2(100), […]

How to make Data Base Trigger in Oracle?

What is Database Trigger Database triggers are designed and applied on tables to execute an event in the database. When we perfume a transaction on table then trigger execute and perfume the specific task. Database trigger are same like stored procedures but stored procedures are call explicitly on the other hand database triggers are call […]

How to make View in Oracle?

Table No.1 CREATE TABLE GRN_MASTER ( GRN_NUM       NUMBER, GRN_DATE      DATE, SUPPLIER_ID   NUMBER, GATE_PASSS_ID NUMBER, STORE_ID      NUMBER, APPROVED_ID   NUMBER, CONSTRAINTS PK_GRN_NUM PRIMARY KEY (GRN_NUM) ); Table No.2 CREATE TABLE GRN_DETAIL ( GRN_NUM       NUMBER, SR_NUM        NUMBER, ITEM_CODE     NUMBER, ITEM_UOM      NUMBER, ITEM_RATE     NUMBER, ITEM_VALUE    NUMBER, CONSTRAINT FK_GRN_NUM_DTL FOREIGN KEY (GRN_NUM) REFERENCES GRN_MASTER(GRN_NUM) ); View: -CREATE OR REPLACE VIEW […]

How to Rename Column Name in Oracle?

Code: ALTER TABLE GRN_MASTER RENAME COLUMN CHECKED_ID TO CHECKED_NAM;

How to Drop Column in Oracle?

Code: ALTER TABLE GRN_MASTER DROP COLUMN APPROVED_DATE;

How to add Column in Oracle?

What is table Column? A table column is a field of an entity that are using to store data, one table have multiple columns. Code: ALTER TABLE GRN_MASTER ADD   APPROVED_DATE DATE; ALTER TABLE GRN_MASTER ADD   (CHECKED_ID NUMBER, CHECKED_DATE DATE);  

How to add Foreign key in Oracle?

What is Foreign Key? Foreign key means value copy from one table to another table. If one table have primary key then we can use that table column in another table as a foreign key. The table that have primary key is called master table and the table that is using this column is called […]

How to add Primary Key in oracle?

What is Primary key? Primary key is on single field or on multiple fields that is using for uniqueness of the record. One table always has one primary key. Primary key at the stage of table creation: CREATE TABLE GRN_MASTER ( GRN_NUM       NUMBER, GRN_DATE      DATE, SUPPLIER_ID   NUMBER, GATE_PASSS_ID NUMBER, STORE_ID      NUMBER, APPROVED_ID   NUMBER, CONSTRAINTS PK_GRN_NUM […]

How to create Table in Oracle?

What is table? Table is a logical structure in database that is using for store the data in databases. We can create table in oracle by using this syntax. Code: CREATE TABLE GRN_MASTER ( GRN_NUM       NUMBER, GRN_DATE      DATE, SUPPLIER_ID   NUMBER, GATE_PASSS_ID NUMBER, STORE_ID      NUMBER, APPROVED_ID   NUMBER, CONSTRAINTS PK_GRN_NUM PRIMARY KEY (GRN_NUM) );  

SQL/PLSQL (Main Categories)

How to create Table in Oracle? How to add Primary key in oracle? How to add Foreign key in Oracle? How to add Column in Oracle? How to Drop column in Oracle? How to Rename Column Name in Oracle? How to make view in Oracle? How to make Data Base trigger in Oracle? How to […]