What is a RDBMS (Relational Database Management System)?

RDBMS is stands for Relational Database Management System. RDBMS is the foundation for SQL and all current database management systems, including MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) based on E. F. Codd's relational model.

Relational Database Management System

In the jargon of Database Management Systems, there is a rule that is unspoken. Since there aren't many databases that follow all of E.F. Codd's rules, the unspoken rule has been getting more attention.

• A management system or piece of software is considered to be a Database Management System if it complies with any one of the five or six rules set by E.F. Codd (DBMS).
• If a management system or programme adheres to any of E.F. Codd's seven to nine recommended guidelines, it qualifies as a semi-relational database management system (semi- RDBMS).
• In order to be considered a full Relational Database Management System, a management system or software must comply with all 9–12 of E.F. Codd's suggested rules (RDBMS).

Here is the list of Codd's Rules.

Rule 0 − Foundation rule

Any relational database management system (RDBMS) that is proposed or argued to be an RDBMS should be able to handle all of the stored data using its relational features.

Rule 1 − Rule of Information

Relational databases should store data as relationships. In Relational Database Management Systems, tables are what are called "relations." It is important to store the value as an entity in the table cells, whether it is user-defined data or meta-data.

Rule 2 − Rule of Guaranteed Access

It is against the rules to use pointers to get to data logically. Every atomic data entity should be accessed in a logical way by using the table name, the primary key represented by a specific row value, and the column name represented by an attribute value.

Rule 3 − Rule of Systematic Null Value Support

Relational databases do not have any problems with null values. They should always be thought of as "missing information." Null values don't depend on the type of data. You shouldn't mix them up with blanks, 0s, or empty strings. Null values can also be seen as "data that doesn't apply" or "information that isn't known."

Rule 4 − Rule of Active and online relational Catalog

Metadata is the data about the database or the data about the database in the language of Database Management Systems. "Data dictionary" is the name of the live online catalogue that stores the metadata. The so-called "data dictionary" can only be accessed by authored users with the right permissions, and the same query languages used to get to the database should be used to get to the data in the "data dictionary."

Rule 5 − Rule of Comprehensive Data Sub-language

Integrity constraints, views, data manipulations, transactions, and authorizations should all be able to be written in a single, strong language.

Rule 6 − Rule of Updating Views

Views should reflect the updates of their respective base tables and vice versa. A view is a logical table which shows restricted data. Views generally make the data readable but not modifiable. Views help in data abstraction.

Rule 7 − Rule of Set level insertion, update and deletion

The data should be able to be retrieved, added, changed, and deleted with just one operation.

Rule 8 − Rule of Physical Data Independence

Batch and end-user operations are separated in terms of how they are stored and how they are accessed.

Rule 9 − Rule of Logical Data Independence

Batch users and end users can change the database schema without having to recreate it or the applications that use it.

Rule 10 − Rule of Integrity Independence

Integrity constraints should be kept in the data dictionary as metadata, not in the application programmes.

Rule 11 − Rule of Distribution Independence

The Manipulation of Data The language of the relational system shouldn't care about where the physical data is stored, and it shouldn't matter if the physical data is stored in one place or in many places.

Rule 12 − Rule of Non Subversion

Any row should follow the rules for security and integrity. No special privileges are applicable.

Almost all full scale DBMSs are RDMSs. Oracle implements 11+ rules and so does Sybase. FoxPro only uses 7+ rules, while SQL Server uses 11+ rules.

index Table Type in PLSQL

DECLARE

  TYPE TABLENAMETYPE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

  L_TABLENAMETYPE TABLENAMETYPE;

BEGIN

  --YOU CAN USE THIS L_TABLENAMETYPE TYPE HERE

  L_TABLENAMETYPE(1) := 'AB';

  L_TABLENAMETYPE(2) := 'ABC';

  L_TABLENAMETYPE(3) := 'ABD';

  L_TABLENAMETYPE(4) := 'ABE';

  FOR LDX IN 1 .. L_TABLENAMETYPE.COUNT

 

   LOOP

 

    DBMS_OUTPUT.PUT_LINE(L_TABLENAMETYPE(LDX));

 

  END LOOP;

  ---DELETE ALL VALUES

  L_TABLENAMETYPE.DELETE;

END;

--RESULT

AB

ABC

ABD

ABE

---USE TABLE TYPE WITH BULK COLLECT INTO

DECLARE

CURSOR C IS

  SELECT * FROM EMP;

TYPE ANY_TABLE_TYPE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;

EMP_REC ANY_TABLE_TYPE;

BEGIN

  OPEN C;

  FETCH C BULK COLLECT

 

    INTO EMP_REC;

  CLOSE C;

  FORALL I IN EMP_REC.FIRST .. EMP_REC.LAST

 

    UPDATE EMP

   

       SET COMM = EMP_REC(I).SAL * 10 / 100

   

     WHERE EMPNO = EMP_REC(I).EMPNO;

  COMMIT;

END;

Perform Dynamic DLL in Oracle

EXECUTE IMMEDIATE statement can be used to execute dynamic SQL statements.

Syntax: EXECUTE IMMEDIATE 'User_Code';

--

-- Create/Drop Table using this PLSQL Script or Procedure

-- DLL Script

-- by mfaisal 02-Jan-2020

Declare

---Create

begin

  execute immediate 'CREATE TABLE X_DDL_ON_APPS (APPS_ID NUMBER)';

end;

Declare

--Drop with Oracle EBS Package

begin

  system.ad_apps_private.do_apps_ddl(schema_name => 'APPS',

                                     ddl_text => 'DROP TABLE X_DDL_ON_APPS ');

end;

Backup of all Oracle Database objects on Server with Procedure

----Backup With Procedure

---Create Directory

CREATE OR REPLACE DIRECTORY PS_DIR AS '/u01/oracle/PROD/fs2/EBSapps/appl/au/12.0.0/forms/BackUp';

---Create Procedure

  

   CREATE OR REPLACE PROCEDURE    EXPORT_DDL

AS

    V_DDL_CLOB  CLOB;

    VPATH VARCHAR2(255);

BEGIN

    FOR C IN (SELECT OBJECT_NAME, OBJECT_TYPE

             FROM USER_OBJECTS

             WHERE OBJECT_TYPE IN ('TABLE','VIEW','FUNCTION','PROCEDURE','PACKAGE','PACKAGE_BODY')

             and OBJECT_NAME like '%TGC%')

    LOOP

        V_DDL_CLOB := DBMS_METADATA.GET_DDL(C.OBJECT_TYPE, C.OBJECT_NAME);

        DBMS_XSLPROCESSOR.CLOB2FILE(V_DDL_CLOB, 'PS_DIR', C.OBJECT_TYPE || '_' || C.OBJECT_NAME||'.SQL');

    END LOOP;

END;

--- Call procedure

Exec EXPORT_DDL;

Compile Oracle form scripts

For single form

frmcmp_batch userid=apps/apps module=/opt/oracle/fs1/EBSapps/appl/au/12.0.0/forms/US/Form.fmb output_file=/opt/oracle/fs1/EBSapps/appl/fnd/12.0.0/forms/US/Form.fmx module_type=form batch=no compile_all=yes



For all forms/Lib

Copy following code and paste in notepad and save as Compile.cmd

@ECHO OFF 
cls 
Echo compiling libraries 10g .... 
for %%f IN (*.pll) do frmcmp userid=User_name/pass@orcl module=%%f batch=yes module_type=library compile_all=yes window_state=minimize 
ECHO libraries compilation complete
ECHO ----------------------------------
Echo compiling menus 10g .... 
for %%f IN (*.mmb) do frmcmp userid=User_name/pass@orcl module=%%f batch=yes module_type=menu compile_all=yes window_state=minimize 
ECHO menus compilation complete
ECHO ----------------------------------
Echo compiling forms 10g .... 
for %%f IN (*.fmb) do frmcmp userid=User_name/pass@orcl module=%%f batch=yes module_type=form compile_all=yes window_state=minimize 
ECHO form compilation complete
ECHO ----------------------------------
ECHO Form Compilation complete

Print Function for PLSQL Testing

For tracing PLSQL code , Print function  show output in DBMS Output as per Arguments.

1) For implementation of  this function simply add G_Debug_Enabled Variable and Function Specification in your package Specification. For enable tracing set G_Debug_Enabled as True before execute your code.

  G_Debug_Enabled Boolean default false;
    procedure print(pString in varchar2

                , pText_1 in varchar2 default null

                , pValue1 in varchar2 default null

                , pText_2 in varchar2 default null

                , pValue2 in varchar2 default null

                , pText_3 in varchar2 default null

                , pValue3 in varchar2 default null

                , pText_4 in varchar2 default null

                , pValue4 in varchar2 default null

                , pText_5 in varchar2 default null

                , pValue5 in varchar2 default null

                , pText_6 in varchar2 default null

                , pValue6 in varchar2 default null

                , pText_7 in varchar2 default null

                , pValue7 in varchar2 default null

                , pText_8 in varchar2 default null

                , pValue8 in varchar2 default null

                , pText_9 in varchar2 default null

                , pValue9 in varchar2 default null

                );

procedure test_Print_function;

2) Add Function Body in Package Specification Body

procedure print(pString in varchar2

                , pText_1 in varchar2 default null

                , pValue1 in varchar2 default null

                , pText_2 in varchar2 default null

                , pValue2 in varchar2 default null

                , pText_3 in varchar2 default null

                , pValue3 in varchar2 default null

                , pText_4 in varchar2 default null

                , pValue4 in varchar2 default null

                , pText_5 in varchar2 default null

                , pValue5 in varchar2 default null

                , pText_6 in varchar2 default null

                , pValue6 in varchar2 default null

                , pText_7 in varchar2 default null

                , pValue7 in varchar2 default null

                , pText_8 in varchar2 default null

                , pValue8 in varchar2 default null

                , pText_9 in varchar2 default null

                , pValue9 in varchar2 default null

                )

    is

    begin

            if G_Debug_Enabled then

               Dbms_output.put_line('$ '||pString||' : '||pText_1||'  => '||pValue1||' '||pText_2||' => '||pValue2||' '||pText_3||' => '||pValue3||' '||pText_4||' => '||pValue4||' '||pText_5||' => '||pValue5||' '||pText_6||' => '||pValue6||' '||pText_7||' => '||pValue7||' '||pText_8||' => '||pValue8||' '||pText_9||' => '||pValue9);

              end if;

      end;

procedure test_Print_function

  is

  begin

          print('Function Test','TransactionID',10,'EmployeeName','mFaisal');

    end;

3) Now test test_print_function

begin

  -- Call the procedure

  PKG_wf_api.G_Debug_Enabled:=true;

  PKG_wf_api.test_print_function;

end;

Output: 

$ Function Test : TransactionID  => 10 EmployeeName => mFaisal