Difference between %TYPE and %ROWTYPE in Oracle

Difference between %TYPE and %ROWTYPE in Oracle

%TYPE: is used to defined the data type of variable as the column name datatype specified for a table. If table column has datatype varchar2(30) then %type variable has same datatype varchar2(30).
Define as: vEmployeeName Employee.Name%TYPE;

Example

SET SERVEROUTPUT ON
DECLARE
vEmployeeName Employee.Name%TYPE;
BEGIN
SELECT Name INTO vEmployeeName
FROM Employee
WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE(vEmployeeName);
END;
/

%ROWTYPE: Used to declare a record with the same types as found in the specified table, (it is like an array which defined all columns of a table with their datatype representation used with variable name with column_name of table).
Define as:rEmployee Employee%ROWTYPE;

Example

SET SERVEROUTPUT ON
DECLARE
rEmployee Employee%ROWTYPE;
BEGIN
rEmployee.Name := 'RAM';
rEmployee.sex := 'MALE';
DBMS_OUTPUT.PUT_LINE(rEmployee.Name);
DBMS_OUTPUT.PUT_LINE(rEmployee.Age);
END;
/

Advertisements

PLS-00103: Encountered the symbol “CREATE” when expecting

PLS-00103: Encountered the symbol “CREATE” when expecting

If you want to use DDL statement in PL/SQL Blocks then you can do not write directly DDL Statement.
You have to use the Dynamic SQL Syntax for executing the DDL Statement.

Error:

SQL> begin
2 create table test_bkp1 (id number);
3 end
4 /
create table test_bkp1 (id number);
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge

Solution:
Donot use DDL directly in PL/SQL blocks. Use the DDL Statements with Dynamic SQL as follows.

BEGIN
EXECUTE IMMEDIATE 'create table TEST_BKP1 (id number, column_value varchar2(100))';
END;
/

Dynamic SQL in Oracle

Dynamic SQL in Oracle

Dynamic SQL is used to execute the things at run time. It is used for executing DDL Statement in the PL/SQL Blocks becauze you can not execute DDL directly in PL/SQL Blocks.

Following are the examples for using the Dynamic SQL
Following is the table used in the examples
Name: TEST
DATA:
SQL> select * from TEST;
NAME ID
—— ——–
RAM 1
SHAM 2

SELECT in Dynamic SQL
Pass the ID column and table name TEST as parameter in which dynamic query return the NAME column value as return output..

set serveroutput on
Declare
p_id varchar2(100) := '1';
p_table_name varchar2(100) := 'TEST';
VALUE VARCHAR2(100);
BEGIN
EXECUTE IMMEDIATE 'select name from ' || p_table_name ||' where id = :P' INTO VALUE USING p_id;
DBMS_OUTPUT.put_line(value);
END;
/

OUTPUT:
------------
RAM

INSERT in Dynamic SQL

DECLARE
query_text VARCHAR2(1000) := 'insert into test(id, NAME) values (:P_ID, :P_VAL)';
id NUMBER := 3;
VALUE VARCHAR2(100) := 'RAMESH';
BEGIN
EXECUTE IMMEDIATE query_text USING id, VALUE;
END;
/

OUTPUT:
SQL> select * from TEST;
NAME ID
------- -------
RAMESH 3
RAM 1
SHAM 2

Update values in dynamic SQL

DECLARE
query_text VARCHAR2(1000) := 'update test set name = :P_VAL where id = :P_ID';
id NUMBER := 3;
VALUE VARCHAR2(100) := 'RAKESH';
BEGIN
EXECUTE IMMEDIATE query_text USING VALUE,ID;
END;
/

OUTPUT:
SQL> select * from test;
NAME ID
---------- ----------
RAKESH 3
RAM 1
SHAM 2

NOTE: It means you should correct the sequence of input as query written inside the dynamic query clauses. otherwise following error occurred:
Error:
EXECUTE IMMEDIATE query_text USING id, VALUE;
— Error occurred due to update query use varchar2 first in sequence then number
DECLARE
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 6
Solution:
Correct the sequence of parameter passing in dynamic SQL.

Delete value in Dynmaic SQL

DECLARE
query_text VARCHAR2(1000) := 'delete from test where id = :P_ID';
id NUMBER := 3;
BEGIN
EXECUTE IMMEDIATE query_text USING ID;
END;
/

OUTPUT:
SQL> select * from test;
NAME ID
---------- ----------
RAM 1
SHAM 2

DDL Statements execute with Dynamic SQL

BEGIN
EXECUTE IMMEDIATE 'create table TEST_BKP (id number, column_value varchar2(100))';
END;
/

OUTPUT:
SQL> desc test_bkp;
Name Null? Type
-------------- -------- ---------------
ID NUMBER
COLUMN_VALUE VARCHAR2(100)

ORA-01439: column to be modified must be empty to change datatype

ORA-01439: column to be modified must be empty to change datatype

I want to change the table NAME datatype from Number to varchar character type. When i fired it is giving the following error as:
ORA-01439: column to be modified must be empty to change datatype

SQL> desc test1;
Name Null? Type
------------ -------- ----------------
NAME NUBER(10)
ID NOT NULL NUMBER(11)

SQL> alter table test1 modify name varchar2(10);
alter table test1 modify name varchar2(10)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Solution:
1. Create a backup table for the table so that we will remove data from existing table.

SQL> create table test1_bkp as select * from test1;
Table created.

SQL> select * from test1_bkp;
NAME ID
---- -------
1 1
2 2

2. Empty the original table

SQL> delete from test1;
2 rows deleted.

SQL> commit;
Commit complete.

3. Modify the column data type

SQL> alter table test1 modify name varchar2(10);
Table altered.

4. Insert back the existing data into table.

SQL> insert into test1 select * from test1_bkp;
2 rows created.
SQL> commit;
Commit complete.

5. Test the table is working fine

SQL> select * from test1;
NAME ID
----- ------
1 1
2 2

LOG_ARCHIVE_DEST_n parameter setting in Oracle Dataguard

LOG_ARCHIVE_DEST_n parameter setting in Oracle Dataguard

LOG_ARCHIVE_DEST_n: this parameter is used to transfer the archived redo from the primary database to standby database. Standby to primary in case of switchover.

Note: LOG_ARCHIVE_DEST_n destination must have either a LOCATION or SERVICE attribute to specify a local disk directory or a remotely accessed database. All other parameter are optional.

Configured two parameter for primary and standby mode
Primary (LOG_ARCHIVE_DEST_1) define the physical location for the primary database archived redo logs.
Secondary (LOG_ARCHIVE_DEST_2) handle the transmission of the standby site’s archived redo logs back to the original primary database
when these two databases exchange roles in the future.

Example
Set the parameter LOG_ARCHVICE_DEST_n for defining the PRIM and STANBY database server.
The DB_UNIQUE_NAME parameter specifies PRIM (DB_UNIQUE_NAME=PRIM), which is also specified with the DB_UNIQUE_NAME attribute on the LOG_ARCHIVE_DEST_1 parameter.
The DB_UNIQUE_NAME attribute on the LOG_ARCHIVE_DEST_2 parameter specifies the STANBY destination.
Both PRIM and STANBY are listed in the LOG_ARCHIVE_CONFIG=DG_CONFIG parameter.


Primary parameter example
DB_UNIQUE_NAME=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STANBY)'
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2='SERVICE=STANBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANBY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE

Parameter used in Log_archive_dest with Example


AFFIRM and NOAFFIRM
Redo transport service use sync or async I/O to write redo data to disk. Default is NOAFFIRM.
AFFIRM: all disk I/O to archived redo log files and standby redo log file is performed synchronously and complete before continue.
NOAFFIRM: it do asynchronous, log writer doesn’t wait on primary database of disk I/O complete, it continues.
Note: AFFIRM and NOAFFIRM applied on remote standby destinations and have no effect on disk I/O for the primary database’s online redo log files.

Use following combination:
LGWR and AFFIRM attributes the log writer process sync writes the redo data to disk, control is not returned to the user until the disk I/O completes,
and online redo log files on the primary database might not be reusable until archiving is complete.
ARCH and AFFIRM attributes ARCn processes synchronously write the redo data to disk, the archival operation might take longer, and online redo log files on the primary database might not be reusable until archiving is complete.
ASYNC and AFFIRM attributes No performance effect

Example:
LOG_ARCHIVE_DEST_3='SERVICE=stby1 LGWR SYNC AFFIRM'
LOG_ARCHIVE_DEST_STATE_3=ENABLE


ARCH and LGWR
Redo transport services use to choose archiver processes ARCH or the log writer process (LGWR) to collect transaction redo data and move to standby destinations. Default is ARCH process.

Example:
LOG_ARCHIVE_DEST_3='SERVICE=standby LGWR'
LOG_ARCHIVE_DEST_STATE_3=ENABLE


DELAY
It is time lag when redo data is archived on a standby and when the archived redo log file is applied to the standby database.

Example
LOG_ARCHIVE_DEST_3='SERVICE=standby DELAY=120'
LOG_ARCHIVE_DEST_STATE_3=ENABLE


VALID FOR
used for redo transport services transmit redo data to a destination. Default value is ALL_LOGFILES & ALL_ROLES.
Different parameter available:
ONLINE_LOGFILE This destination is valid only when archiving online redo log files
STANDBY_LOGFILE This destination is valid only when archiving standby redo log files.
ALL_LOGFILES This destination is valid when archiving any one of above ONLINE_LOGFILE or STANDBY_LOGFILE.
PRIMARY_ROLE This destination is valid only when the database is running in the primary role.
STANDBY_ROLE This destination is valid only when the database is running in the standby role.
ALL_ROLES This destination is valid when the database is running in either the primary or the standby role.

Example:
LOG_ARCHIVE_DEST_1='LOCATION=/arch VALID_FOR=(ALL LOGFILES, ALL_ROLES)';
LOG_ARCHIVE_DEST_STATE_1=ENABLE


SYNC and ASYNC
network I/O is to be done synchronously (SYNC) or asynchronously (ASYNC) when archival is performed using the log writer process (LGWR).
Note:
1. With LGWR, default is sync used.
2. With ARCH , only SYNC is valid.
3. SYNC attribute is used for no data loss. It ensure that redo is transmitted successfully at destination before continue.

Example:
LOG_ARCHIVE_DEST_3='SERVICE=stby1 LGWR SYNC'
LOG_ARCHIVE_DEST_STATE_3=ENABLE

Reference link:
https://docs.oracle.com/cd/B19306_01/server.102/b14239/log_arch_dest_param.htm#i78506

DB_UNIQUE_NAME and DB_NAME parameter in Oracle

DB_UNIQUE_NAME and DB_NAME parameter in Oracle

DB_UNIQUE_NAME: specifies a name that uniquely identifies the databases in the Data Guard configuration. It is used in while creating Data Guard which specified remote location.Databases with the same DB_NAME within the same DB_DOMAIN must have a unique DB_UNIQUE_NAME. It is used in LOG_ARCHIVE_CONFIG parameter values for remote location.Log transport services validates that the db_unique_name of the database that specified destination.

DB_NAME: It is the identifier of exact database. It should be the same in both the standby and production initialization parameter files

Note: DB_NAME for all standby databases must match the DB_NAME of the primary database.

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

Error occurred due to Switchover having active session

I am trying to switchover the primary database to standby database then i am getting the following error:

SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

Solution:
For disconnect the active user you can use the clause WITH SESSION SHUTDOWN.

-- From primary if getting error:
PRIM> alter database commit to switchover to physical standby with session shutdown;

-- From Standby if getting error:
Standby> alter database commit to switchover to primary with session shutdown;

Note:
You can verify before switch over command with switchover_status column in v$database.
It tell you that any session is active at time of switchover or not.

Following value means session is active. use this command at both primary or Standby before switch over.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

Value of Switchover at primary and Standby as follow then it good to go for switchover the Dataguard environment

STAMDBY> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

PRIM> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY