Oracle APEX CRM Project Development | Part 2 | Database Tables Design

   

CREATE TABLE "CRM_AGENT" 

   ( "AGENT_NO" NUMBER, 

"AGENT_NAME" VARCHAR2(100), 

"AGENT_ADDRESS" VARCHAR2(100), 

"CREATE_DT" DATE, 

"UPDATE_DT" DATE, 

"UPDATE_BY" VARCHAR2(100), 

"CREATE_BY" VARCHAR2(100), 

CONSTRAINT "AGENT_NO_PK" PRIMARY KEY ("AGENT_NO")

  USING INDEX  ENABLE

   )   NO INMEMORY ;

--------------------------------------------------------------------------------

  CREATE TABLE "CRM_BASE" 

   ( "CRM_BASE_ID" NUMBER, 

"BASE_TYPE" VARCHAR2(100), 

"BASE_NAME" VARCHAR2(100)

   )   NO INMEMORY ;

-------------------------------------------------

  CREATE TABLE "CRM_CONTACT" 

   ( "CONTACT_NO" NUMBER, 

"CONTACT_ID" VARCHAR2(30), 

"CONTACTS" VARCHAR2(300), 

"CONTACTS_PERSON" VARCHAR2(300), 

"BIRTH_DATE" DATE, 

"CONTACT_WORK" VARCHAR2(16), 

"CONTACT_HOME" VARCHAR2(16), 

"CONTACT_MOBILE" VARCHAR2(16), 

"CONTACT_OTHERS" VARCHAR2(16), 

"CONTACT_ASSISTANT" VARCHAR2(16), 

"EMAIL_OFFICE" VARCHAR2(80), 

"EMAIL_SELF" VARCHAR2(80), 

"SKYPE_NAME" VARCHAR2(80), 

"IS_ASSIGNED_TO" VARCHAR2(80), 

"CONTACT_CAT" VARCHAR2(80), 

"LEAD_SOURCE" VARCHAR2(80), 

"DEPT_NAME" VARCHAR2(80), 

"PRIAMRY_ADDRESS" VARCHAR2(1000), 

"OTHER_ADDRESS" VARCHAR2(1000), 

"REAMRKS" VARCHAR2(3999), 

"STATUS" NUMBER, 

"APP_NO" VARCHAR2(100), 

"PAGE_NO" VARCHAR2(100), 

"COMPANY_NO" NUMBER, 

"CREATED_BY" VARCHAR2(100), 

"CREATED_DT" DATE, 

"UPDATED_BY" VARCHAR2(100), 

"UPDATED_DT" DATE, 

"GENDER_NAME" VARCHAR2(100), 

"LEAD_FLAG" NUMBER, 

"OPTY_FLAG" NUMBER, 

"CUST_FLAG" NUMBER, 

"SOURCE_TYPE" NUMBER, 

"USER_NO" VARCHAR2(100), 

"LEAD_SOURCE_1" NUMBER

   )   NO INMEMORY ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "CRM_CONTACT_ID_GEN" 

   BEFORE INSERT

   ON crm_contact

   REFERENCING OLD AS old NEW AS new

   FOR EACH ROW

     WHEN (new.CONTACT_NO IS NULL and new.CONTACT_ID is null) BEGIN

   SELECT NVL (MAX (CONTACT_NO), 0) + 1,NVL (MAX (to_number(CONTACT_ID)), 0) + 1 INTO :new.CONTACT_NO,:new.CONTACT_ID  FROM CRM_CONTACT;

END;

/

ALTER TRIGGER "CRM_CONTACT_ID_GEN" ENABLE;

---------------------------------------------------------------------------------

  CREATE TABLE "CRM_CUSTOMERS" 

   ( "CUST_NO" NUMBER, 

"CONTACT_NO" NUMBER, 

"CUSTOMER_NAME" VARCHAR2(100), 

PRIMARY KEY ("CUST_NO")

  USING INDEX  ENABLE

   )   NO INMEMORY ;

------------------------------------------------------------------------------------------

  CREATE TABLE "CRM_TICKETS" 

   ( "TKT_NO" NUMBER, 

"TKT_NAME" VARCHAR2(100), 

"TKT_PIPELINE" VARCHAR2(100), 

"TICKET_STATUS" VARCHAR2(100), 

"TKT_DESCRIPTIONS" VARCHAR2(200), 

"TICKET_OWNER" VARCHAR2(100), 

"TICKET_OWNER_ID" NUMBER, 

"PRIORITY" VARCHAR2(100), 

"ASSOCIATE_WITH" VARCHAR2(100), 

"CREATED_BY" VARCHAR2(100), 

"CREATED_DATE" DATE, 

"UPDATE_BY" VARCHAR2(100), 

"UPDATE_DATE" DATE, 

"ASSIGN_TO" NUMBER, 

"USER_NO" NUMBER, 

"COMPLETE_STATUS" CHAR(3), 

"COMPLETE_NOTE" VARCHAR2(200), 

"COMPETE_DATE" DATE, 

PRIMARY KEY ("TKT_NO")

  USING INDEX  ENABLE

   )   NO INMEMORY ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "TKT_NO_GEN" 

before insert on CRM_TICKETS

referencing old as old new as new

for each row

begin

select nvl(max(TKT_NO),0)+1 into :new.TKT_NO from CRM_TICKETS;

end;

/

ALTER TRIGGER "TKT_NO_GEN" ENABLE;

-------------------------------------------------------------

  CREATE TABLE "CRM_TRNS_LOG" 

   ( "CRM_TRNS_LG_NO" NUMBER, 

"CONTACT_NO" NUMBER, 

"CONTACT_ID" NUMBER, 

"CALL_DATE" DATE, 

"DESCRIPTIONS" VARCHAR2(100), 

"LOG_TYPE" VARCHAR2(20), 

"LOG_NO" NUMBER, 

"APPOINT_WITH" VARCHAR2(100), 

"APPOINT_SUBJECT" VARCHAR2(100), 

"CALL_START_TIME" DATE, 

"CALL_END_TIME" DATE, 

"CALL_DUARATIONS" VARCHAR2(100), 

"CALL_TYPE" VARCHAR2(50), 

"CALL_AGENDA" VARCHAR2(50), 

PRIMARY KEY ("CRM_TRNS_LG_NO")

  USING INDEX  ENABLE

   )   NO INMEMORY ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "CRM_TRNS_LG_NO_GEN" 

   BEFORE INSERT

   ON crm_trns_log

   REFERENCING OLD AS old NEW AS new

   FOR EACH ROW

     WHEN (new.crm_trns_lg_no IS NULL) BEGIN

   SELECT NVL (MAX (crm_trns_lg_no), 0) + 1 INTO :new.crm_trns_lg_no  FROM crm_trns_log;

END;

/

ALTER TRIGGER "CRM_TRNS_LG_NO_GEN" ENABLE;

1.The question is when the database open which parameter file search the database?

 

SPFILE (Binary File) P File (Text File)

1.The question is when the database open which parameter file search the database?

Ans: By default, Oracle Search the SPFILE to open the database because it is the binary file.

SQl>
Show parameter SPFILE (if you get output from this command then you could understand that

database is open by SPFILE otherwise it is open by PFILE.)

Then you will get the path of the location.

From PFile you can create SPFILE .

Command : Create SPFILE FROM PFILE;

Oracle Tablespace & User Creation | Step-by-Step Tutorial

ALTER SESSION SET CONTAINER = ORCLPDB;

sqlplus / as sysdba

SHOW CON_NAME;

ALTER SESSION SET CONTAINER = ORCLPDB;

-- Create a new tablespace named TBS_TEST

CREATE TABLESPACE tbs_test    

-- Specify the physical datafile location and name                      

DATAFILE 'D:\database19c\app\oradata\ORCL\TEST.DBF' 

-- Initial size of the datafile is 256 MB

SIZE 256M                                                                             

-- Allow the datafile to grow automatically when space is needed

AUTOEXTEND ON                                       

-- Maximum size the datafile can grow to is 2048 MB (2 GB)

MAXSIZE 2048M                                       

-- Use locally managed extents (recommended for performance)

EXTENT MANAGEMENT LOCAL                             

ONLINE;    -- Make the tablespace immediately available for use

-- Create a new database user named TEST1

CREATE USER TEST1 

-- Set the password for the user

IDENTIFIED BY TEST 

-- Set TBS_TEST as the default tablespace for this user

-- All objects (tables, indexes) will be created here by default

DEFAULT TABLESPACE tbs_test

-- Set TEMP as the temporary tablespace for sorting operations

TEMPORARY TABLESPACE TEMP; 

-- Grant DBA role to user TEST1

-- This gives full administrative privileges

GRANT DBA TO TEST1;

 

call form from url oracle forms 12c

Go to the formsweb.cfg file then scroll down and write:

Here test_web will be your project name like sales then location of the forms file.

 [test_web]

form=D:\SIS_Forms\LOGIN_FORM.fmx

usesdi=yes

userid=sales/s@ora19

Some Oracle-Supplied Packages

 • DBMS_OUTPUT 

 • UTL_FILE 

 • UTL_MAIL

 • UTL MAIL 

 • DBMS_ALERT 

 • DBMS_LOCK

 • DBMS_SESSION  

 • DBMS APPLICATION INFO 

 • HTP 

 • DBMS_SCHEDULER

ORA-03113: end-of-file on communication channel

 SQL> startup

ORACLE instance started.

Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size            1577058304 bytes

Database Buffers         2701131776 bytes

Redo Buffers                7630848 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 46641

Session ID: 259 Serial number: 54397

SQL> STARTUP MOUNT;

SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0

Unsafe to proceed

ORA-03114: not connected to ORACLE

SQL> sqlplus

SP2-0042: unknown command "sqlplus" - rest of line ignored.

SQL>

SQL>

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@vmi2001271 ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 10 09:15:03 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> SHUTDOWN IMMEDIATE;

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Additional information: 4376

Additional information: -896098569

Process ID: 0

Session ID: 0 Serial number: 0

SQL> shutdown abort

ORACLE instance shut down.

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size            1577058304 bytes

Database Buffers         2701131776 bytes

Redo Buffers                7630848 bytes

Database mounted.

SQL> ^C

SQL> startup nomount

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL>  alter database clear unarchived logfile group 2;

Database altered.

SQL>  alter database clear unarchived logfile group 1;

Database altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size            1577058304 bytes

Database Buffers         2701131776 bytes

Redo Buffers                7630848 bytes

Database mounted.

Database opened.

CV Design Using PLSQL Dynamic Content

 declare

    cursor c_emp is

        SELECT j.Emp_reg_id,

               (p.First_name || ' ' || p.last_name) Name,

               p.father_name,

               p.mother_name,

               p.Address,

               p.Phone,

               p.email,

               p.date_of_birth,

               p.religion,

               p.blood_group,

               p.Nationality

          FROM job_seeker_registration j

               JOIN Personal_details p ON j.emp_reg_id = p.emp_reg_id

        WHERE j.emp_reg_id = :G_EMP_ID;

    cursor c_academic_details is

        SELECT 

               a.Name_of_degree,

               a.Subject,

               a.Name_of_institute,

               a.Duration,

               a.result

          FROM academic_details a

        WHERE a.emp_reg_id = :G_EMP_ID;

        --

        cursor c_experience is 

           SELECT 

              e.job_title,

               e.dept_name,

               to_number(ROUND((e.End_date - e.Start_date) / 365)) Year_Of_Experience

          FROM experience_details e

WHERE e.emp_reg_id= :G_EMP_ID;

       ---

       cursor  c_skills is  SELECT 

                s.Skill_name,

               s.skill_level

          FROM skills s

WHERE s.emp_reg_id= :G_EMP_ID;

        ---

        cursor   c_references is SELECT 

             r.Reference_name,

               r.Designation,

               r.contact_no

          FROM REFERENCES r

WHERE r.emp_reg_id= :G_EMP_ID;

begin

    for rec in c_emp loop

        htp.p('<div style="border: 1px solid #000; padding: 20px; margin-bottom: 20px; font-family: Arial, sans-serif;">');

        htp.p('<h1 style="text-align: center;padding:0%;">Curiculam Vitae Of</h1>');

        htp.p('<h2 style="text-align: center;padding:0%;">' || rec.Name || '</h2>');

        htp.p('<h4 style="text-align: center;padding:0%;"><strong>Address:</strong> ' || rec.Address || '</h4>');

        htp.p('<h4 style="text-align: center;padding:0%;"><strong>Phone:</strong> ' || rec.Phone || '</h4>');

        htp.p('<h4 style="text-align: center;padding:0%;"><strong>Email:</strong> ' || rec.email || '</h4>');

        htp.p('<h2>Personal Information</h2>');

        htp.p('<p><strong>Father Name:</strong> ' || rec.father_name || '</p>');

        htp.p('<p><strong>Mother Name:</strong> ' || rec.mother_name || '</p>');

        htp.p('<p><strong>Address:</strong> ' || rec.Address || '</p>');

        htp.p('<p><strong>Phone:</strong> ' || rec.Phone || '</p>');

        htp.p('<p><strong>Email:</strong> ' || rec.email || '</p>');

        htp.p('<p><strong>Date of Birth:</strong> ' || rec.date_of_birth || '</p>');

        htp.p('<p><strong>Religion:</strong> ' || rec.religion || '</p>');

        htp.p('<p><strong>Blood Group:</strong> ' || rec.blood_group || '</p>');

        htp.p('<p><strong>Nationality:</strong> ' || rec.Nationality || '</p>');

    end loop;

begin

  

  --academic

    htp.p('<h2>Academic Details</h2>');

    htp.p('<table border="1" style="width: 100%; border-collapse: collapse;">');

    htp.p('<tr><th>Degree</th><th>Subject</th><th>Institute</th><th>Duration</th><th>Result</th></tr>');

    for rec_academi in c_academic_details loop

        htp.p('<tr>');

        htp.p('<td>' || rec_academi.Name_of_degree || '</td>');

        htp.p('<td>' || rec_academi.Subject || '</td>');

        htp.p('<td>' || rec_academi.Name_of_institute || '</td>');

        htp.p('<td>' || rec_academi.Duration || '</td>');

        htp.p('<td>' || rec_academi.result || '</td>');

        htp.p('</tr>');

    end loop;

       htp.p('</table>');

    --Experiences

     htp.p('<h2>Experience Details</h2>');

        htp.p('<table border="1" style="width: 100%; border-collapse: collapse;">');

        htp.p('<tr><th>Job Title</th><th>Department</th><th>Years of Experience</th></tr>');

    for rec_experience in c_experience loop

     htp.p('<tr>');

        htp.p('<td>' || rec_experience.job_title || '</td>');

        htp.p('<td>' || rec_experience.dept_name || '</td>');

        htp.p('<td>' || rec_experience.Year_Of_Experience || '</td>');

        htp.p('</tr>');

        htp.p('</table>');

    end loop;

   

    

--SKILLS

    htp.p('<h2>Skills</h2>');

        htp.p('<table border="1" style="width: 100%; border-collapse: collapse;">');

        htp.p('<tr><th>Skill Name</th><th>Skill Level</th></tr>');

for rec_skills in c_skills

        Loop

        htp.p('<tr>');

        htp.p('<td>' || rec_skills.skill_name || '</td>');

        htp.p('<td>' || rec_skills.skill_level || '</td>');

        htp.p('</tr>');

        htp.p('</table>');

        end loop;

---REFERENCES

          htp.p('<h2>References</h2>');

        htp.p('<table border="1" style="width: 100%; border-collapse: collapse;">');

        htp.p('<tr><th>Name</th><th>Designation</th><th>Contact No</th></tr>');

        htp.p('<tr>');

for rec_references in c_references

        loop

  htp.p('<tr>');

        htp.p('<td>' || rec_references.Reference_name || '</td>');

        htp.p('<td>' || rec_references.Designation || '</td>');

        htp.p('<td>' || rec_references.contact_no || '</td>');

        htp.p('</tr>');

        htp.p('</table>');

        end loop;

    htp.p('</table>');

    htp.p('</div>');

    end;

end;