Category: Oracle

Oracle – Get IP Address and Host Name

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

With UTL_INADDR The UTL_INADDR package provides a PL/SQL procedure to support internet addressing. It provides an API to retrieve host names and IP addresses of local and remote hosts. Example select UTL_INADDR.GET_HOST_NAME Host_Name, UTL_INADDR.GET_HOST_ADDRESS IP_Address from dual ; With SYS_CONTEXT SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements Example select SYS_CONTEXT('USERENV', 'IP_ADDRESS') IPAddress from dual; select SYS_CONTEXT('USERENV', 'HOST') HostName from dual; For more detail one can

» Read more
, ,

Oracle – ORA-01007: variable not in select list

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

Error: ORA-01007: variable not in select list Cause: A reference was made to a variable not listed in the SELECT clause. In OCI, this can occur if the number passed for the position parameter is less than one or greater than the number of variables in the SELECT clause in any of the following calls: DESCRIBE, NAME, or DEFINE. In SQL*Forms or SQL*Report, specifying more variables in an INTO clause than in the SELECT clause also causes this error. Action:

» Read more
,

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
,
1 2 3 4