What does Entity-Relationship Diagram (ERD) mean?

Entity-Relationship Diagram (ERD):

An entity-relationship diagram (ERD) is a data modeling technique that graphically illustrates an information system’s entities and the relationships between those entities. An ERD is a conceptual and representational model of data used to represent the entity framework infrastructure.

The elements of an ERD are:

• Entities
• Relationships
• Attributes

Steps involved in creating an ERD include:

1. Identifying and defining the entities
2. Determining all interactions between the entities
3. Analyzing the nature of interactions/determining the cardinality of the relationships
4. Creating the ERD

Techopedia explains Entity-Relationship Diagram (ERD)

An entity-relationship diagram (ERD) is crucial to creating a good database design. It is used as a high-level logical data model, which is useful in developing a conceptual design for databases.

An entity is a real-world item or concept that exists on its own. Entities are equivalent to database tables in a relational database, with each row of the table representing an instance of that entity.

An attribute of an entity is a particular property that describes the entity. A relationship is the association that describes the interaction between entities. Cardinality, in the context of ERD, is the number of instances of one entity that can, or must, be associated with each instance of another entity. In general, there may be one-to-one, one-to-many, or many-to-many relationships.

For example, let us consider two real-world entities, an employee and his department. An employee has attributes such as an employee number, name, department number, etc. Similarly, department number and name can be defined as attributes of a department. A department can interact with many employees, but an employee can belong to only one department, hence there can be a one-to-many relationship, defined between department and employee.

In the actual database, the employee table will have department number as a foreign key, referencing from department table, to enforce the relationship.

Steps to change IP address of Oracle Application Server (J2EE and WebCache)

Below are the steps for changing IP Address And/or Hostname by using chgiphost.sh command.


Located at $ORACLE_HOME/chgip/scripts.


STEP 1] Stop all J2EE instance services (Mid Tier)


A) Go To $MID_HOME/opmn/bin
Give following command to stop opmn processes.
. /opmnctl stopall


B) Go to $MID_HOME /bin
Give following command to stop em console.
. /emctl stop iasconsole


STEP 2] Take Entire application server backup.


STEP 3] Note down the old IP- address and old hostname (if hostname is going to change) of the server.


STEP 4] change the IP/Hostname of the server, restart the server if required.


STEP 5] Check that the IP-Address and hostname in /etc/hosts is correct


STEP 6] Check that you are able to ping from other machine to that server.


STEP 7] Go to $MID_HOME /chgip/scripts


STEP 8] Run sh chgiphost.sh -mid


STEP 9] After excuting above command we have to specify NEW and OLD IP Or/And Hostname


When prompted for destination IP/Hostname enter new IP/Hostname.
When Prompted for source IP/Hostname enter enter Old IP/Hostname
When prompted for OIDAdmin password enter orcladmin password.


STEP 10] Check for any errors in $MID_HOME /chgip/log/


STEP 11] Start j2ee services using following method.


A) Go to $MID_HOME /opmn/bin
. /opmnctl startall


B) Go to $MID_HOME /bin
. /emctl start iasconsole


STEP 12] Check all services are started properly.


I hope it will help you in changing Oracle Application Server IP/ Hostname.

Steps to change IP address of Oracle Application Server (Infrastructure Tier and Business Inteligence)

We have to Change the IP address of server on which Infrastructure and forms are installed.

Note: Changing Hostname where infrastructure is installed is not supported method. It is not recommended to change hostname. Only Changing IP-Address is supported method.

Following are the steps which i have followed while doing the activity :

In following document the IP Address will be changed using chgiphost.sh command.

Located at $ORACLE_HOME/chgip/scripts.

STEP 1] Stop all services mid-tier plus infra

Stopping Mid –Tier

A) Go To $MID_HOME/opmn/bin
. /opmnctl stopall
B) Go to $MID_HOME /bin

Give following command to stop em console.
. /emctl stop iasconsole

Stopping INFRA-Tier

A) Go To $INFRA_HOME/opmn/bin
. /opmnctl stopall

B) Go to $INFRA_HOME/bin

Give following command to stop em console.
. /emctl stop iasconsole

C) Go to $INFRA_HOME/opmn/bin
Stop lsnrctl
./lsnrctl stop

D) Go to $INFRA_HOME/opmn/bin
Connect to Infra database
./sqlplus “/as sysdba”
./shut immediate

STEP 2] Take complete backup of application server.

STEP 3] Change the IP-address of the server, Reboot If required.

STEP 4] Check that the IP-Address and hostname in /etc/hosts is correct.

STEP 5] Check that you are able to ping from other machine.

STEP 6] Set ORACLE_SID, ORACLE_HOME to infrastructure database.


STEP 7] Start Infrastructure database.
Go to $INFRA_HOME/bin
./sqlplus “/as sysdba”
Startup

STEP 8] Start database listener
Go to $INFRA_HOME/bin
./lsnrctl start

STEP 9] Start opmn service
Go To $INFRA_HOME/opmn/bin
./opmnctl start

STEP 10] Start OID
Go To $INFRA_HOME/opmn/bin
./opmnctl startproc ias-component=OID process-type=OID

STEP 11] Run Chgiphost script.
Go to $INFRA_HOME/chgip/scripts
Sh chgiphost.sh -infra

STEP 12] After excuting above command we have to specify NEW and OLD IP with orcladmin password :

When prompted for destination IP enter new IP.
When Prompted for source IP enter Old IP.
When prompted for OIDAdmin password enter orcladmin password

STEP 13] Check for any errors in $INFRA_HOME/chgip/log

STEP 14] Startall opmn service
Go To $INFRA_HOME/opmn/bin
./opmnctl startall

Check the status of opmn services.

STEP 15] Go to $MID_HOME

STEP 16] Set $ORACLE_HOME path to $MID_HOME

STEP 17] Go to $MID_HOME/chgip/scripts
chgiphost.sh –mid

STEP 18] After excuting above command we have to specify NEW and OLD IP with orcladmin password :

When prompted for destination IP enter new IP.
When Prompted for source IP enter Old IP.
When prompted for OIDAdmin password enter orcladmin password.

STEP 19] Check for any error in $MID_HOME/chgip/log

STEP 20] Start all services as follows:

A) Go To $INFRA_HOME/opmn/bin
./opmnctl startall

B) Go To $INFRA_HOME/bin
. /emctl start iasconsole

C) Go To $Mid_ORACLE_HOME/opmn/bin
. /opmnctl startall

D) Go to $MID_HOME/bin
. /emctl start iasconsole


STEP 20] Check status from front-end.

How To Write To A Path, Make A FTP Operation, Send E-Mail A SQL Query Result In Excel File Via PL/SQL

After I read some blog entries about exporting Excel files via PL/SQL recently, I also decided to share my Excel API code in PL/SQL. I wrote a similar article before .  This API has following features;

Most of the interface procedures accepts SQL query as parameter:

• Creates an excel file content as CLOB variable (PRC_CREATE_EXCEL_BY_QUERY)
• Writes any CLOB data to a specific folder that defined before with a filename. (PRC_WRITE_EXCEL_BY_MANUAL)
• Writes any SQL query to a specific folder that defined before (PRC_WRITE_EXCEL_BY_QUERY)
• Sends e-mail any CLOB data as attachment with TO, CC, BCC features and with also some specific words with spaces in sender name like “John Smith Company”. (PRC_EMAIL_EXCEL_BY_MANUAL)
• Sends e-mail with SQL query result in Excel file as attachment (PRC_EMAIL_EXCEL_BY_QUERY)
• Writes any CLOB data to a FTP path as file. (PRC_FTP_EXCEL_BY_MANUAL)
• Writes SQL query result in Excel file to a FTP path. (PRC_FTP_EXCEL_BY_QUERY)
• Downloads SQL query result in Excel file from APEX application using WPG_DOCLOAD package. (PRC_DOWNL_EXCEL_FILE)
• You can also create dynamic Excel file content in CLOB variable. For example;


DECLARE
MYEXCELCONTENT CLOB;
BEGIN
-- Test statements here
PK_EXCEL_API.G_SHOW_BORDER := 'Y';
PK_EXCEL_API.EX_EXCEL_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_WORKSHEET_OPEN(MYEXCELCONTENT, 'test');

PK_EXCEL_API.EX_ROW_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'asdasdasd');
PK_EXCEL_API.EX_ROW_CLOSE(MYEXCELCONTENT);

PK_EXCEL_API.EX_ROW_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'INVOICE NUMBER');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'SEQ NO');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'VERSION..');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'BLA BLA BLA');
PK_EXCEL_API.EX_ROW_CLOSE(MYEXCELCONTENT);

PK_EXCEL_API.EX_WORKSHEET_CLOSE(MYEXCELCONTENT);
PK_EXCEL_API.EX_EXCEL_CLOSE(MYEXCELCONTENT);
PK_EXCEL_API.PRC_WRITE_EXCEL_FILE(P_CLOB => MYEXCELCONTENT,
P_FILENAME => 'test.xls',
P_DIR => 'UTL_FILE_DIR');
END;

Here is the all package here:

PK_EXCEL_API_v1.0

PK_EXCEL_API_v1.0

Oracle: Recompile Invalid Objects

Question: 

I  have packages and package bodies going invalid when I make schema changes.  How do I recompile invalid objects?

Answer: 

The Oracle database will invalidate objects if a dependent object is changed. If I rebuild a table, the indexes on that table will become invalid because they use the table's rowids and rebuilding the table changes a row's rowid. It is the same with objects like packages, procedures and functions.

In a development environment with lots of users working on the same objects this can become aggravating. Just remember that someone caused the database to invalidate the object. You can control this by controlling who changes objects in the database, or splitting the development into multiple schemas so that one section does not cause another's objects to become invalid.

You can run this query to find invalid objects, which may cause the ORA-06508 error:
select 
   comp_id, 
   comp_name, 
   version, 
   status, 
   namespace, 
   schema 
from 
   dba_registry;

You can invoke the utl_recomp package to recompile invalid objects:

EXEC UTL_RECOMP.recomp_serial('schema name');

Here is a script to recompile invalid PL/SQL packages, stored procedures, functions and package bodies.  You may need to this script more than once for dependencies, if you get errors from the script.

invalid.sql

Set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool run_invalid.sql

select
   'ALTER ' || OBJECT_TYPE || ' ' ||
   OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
   dba_objects
where
   status = 'INVALID'
and
   object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;

spool off;

set heading on;
set feedback on;
set echo on;

@run_invalid.sql

How to drop USERS tables including its datafiles

DROP USER OE CASCADE;
SELECT * FROM Dba_Data_Files;
SELECT * FROM dba_tablespaces;
CREATE TABLESPACE def
   DATAFILE 'D:\APP\ORACLE\PRODUCT\10.2.0\ORADATA\DBWARE\def01.DBF'
   SIZE 100M AUTOEXTEND ON
ALTER DATABASE DEFAULT TABLESPACE def;
ALTER TABLESPACE USERS OFFLINE;
drop tablespace users including contents and datafiles;

SELECT  'DROP '||TABLE_TYPE||' ' ||TABLE_NAME||' ;'  FROM dba_tables
WHERE TABLESPACE_NAME='USERS';

select t.owner, t.table_name, count(*)
from dba_tables t
join (
  select s.owner, s.segment_name, s.tablespace_name from dba_tables t join dba_segments s on s.owner = t.owner and s.segment_name = t.TABLE_NAME and s.segment_type = 'LOBSEGMENT' group by s.owner, s.segment_name, s.tablespace_name
    ) l
on t.owner = l.owner
and t.table_name = l.segment_name
group by t.owner, t.table_name
having count(*) > 1 ;


select * from dba_segments
where tablespace_name ='USERS'
and segment_type like '%LOB%';



ALTER USER oe IDENTIFIED BY oe ACCOUNT UNLOCK;


SELECT 'DROP '||TABLE_TYPE||' ' ||TABLE_NAME||' ;' from cat ORDER BY TABLE_TYPE;




declare
v_str varchar2(4000);
new_ts varchar2(100):='def';
BEGIN
for c1 in(select table_name, column_name, segment_name from user_lobs where segment_name IN(select segment_name from user_segments where tablespace_name='USERS'))
LOOP
v_str := 'alter table '||c1.table_name||' move tablespace '||new_ts||' lob('||c1.column_name||') store as (tablespace '||new_ts||')';
dbms_output.put_line(v_str);
END LOOP;
END;
/

Oracle PL/SQL: Checking Alpha Numeric

  DECLARE
    CATLG VARCHAR2(50):='&VAL';
    ALPHA NUMBER:=0;
    NUMER NUMBER:=0;
    LEN NUMBER:= LENGTH(CATLG);
  BEGIN
    FOR I IN 1..LEN LOOP
      IF ASCII(SUBSTR(UPPER(CATLG),I,1)) BETWEEN 65 AND 90 THEN
        ALPHA:= ALPHA+1;
      END IF;
      IF SUBSTR(CATLG,I,1) IN ('0','1','2','3','4','5','6','7,','8','9') THEN
        NUMER:= NUMER+1;
      END IF;
    END LOOP;
    IF ALPHA = 0 THEN
      DBMS_OUTPUT.PUT_LINE 
('Alpha Numeric Mandatory Atleast single character required!');
      
    ELSIF NUMER = 0 THEN
      DBMS_OUTPUT.PUT_LINE 
('Alpha Numeric Mandatory Atleast single number required!');
      
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Alpha Numeric String');
    END IF;  
  END;