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.

Import DMP file from one schema to another

imp FILE=D:\dmpFile.dmp FULL=N fromuser=fromUser touser=ToUser LOG=D:\LOG.TXT



imp FILE=D:\25mar2020.dmp FULL=N fromuser=apps touser=pos LOG=D:\LOG.TXT

change tablespace of table Oracle

Runtime this Query and Move one table space to other.

SELECT 'ALTER TABLE <SCHEMA>.' || TABLE_NAME ||' MOVE TABLESPACE '||' <TABLESPACE_NAME> ' FROM dba_tables WHERE OWNER = '<SCHEMA>' AND TABLESPACE_NAME <> '<TABLESPACE_NAME>'


SELECT 'ALTER TABLE POS.' || TABLE_NAME ||' MOVE TABLESPACE '||' POS ' FROM dba_tables WHERE OWNER = 'POS'

Command :

ALTER TABLE POS.APPS_CATALOG_HEADER MOVE TABLESPACE  POS 

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;

Get the Password of a User In EBS

CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/

--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';

Example of Oracle Pipelined Function

Create Type Loop_Type is table of Varchar2(400)


Create or Replace Function  Find_Loop_F (P_End_loop in NUmber Default 10) Return Loop_Type PIPELINED
Is
    begin
        For  op in  1..P_End_loop loop
            Pipe row  (op);
            Pipe row  ('Value' );
        end loop;
        --Return;
end;


select * from (Table (Find_Loop_F(200)))

Send Email from PLSQL

DECLARE
    v_from_name         VARCHAR2(100) := 'admin@falickfaisal.com';
    v_to_name           VARCHAR2(100) := 'falickfaisal@gmail.com';

    v_subject           VARCHAR2(100) := 'This is an awesome mail';
    v_message_body      VARCHAR2(100) := 'Hey you!! Bla Bla Bluu';
    v_message_type      VARCHAR2(100) := 'text/plain';

    v_smtp_server       VARCHAR2(200)  := 'smtpout.asia.secureserver.net';
    n_smtp_server_port  NUMBER        := 25;
    conn                utl_smtp.connection;

    TYPE attach_info IS RECORD (
        attach_name     VARCHAR2(40),
        data_type       VARCHAR2(40) DEFAULT 'text/plain',
        attach_content  CLOB DEFAULT ''
    );
    TYPE array_attachments IS TABLE OF attach_info;
    attachments array_attachments := array_attachments();

    n_offset            NUMBER;
    n_amount            NUMBER        := 1900;
    v_crlf              VARCHAR2(5)   := CHR(13) || CHR(10);
    base64username VARCHAR2(40) := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('admin@falickfaisal.com'))); 
base64password  VARCHAR2(40):= UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('123456789')));



BEGIN

  -- Fill data for example
    attachments.extend(5);
    FOR i IN 1..5
    LOOP
        SELECT 'test' || to_char(i) || '.txt','text/plain','test' || to_char(i)
        INTO attachments(i)
        FROM dual;
    END LOOP;



  -- Open the SMTP connection ...
    conn := utl_smtp.open_connection(v_smtp_server,n_smtp_server_port);
    utl_smtp.helo(conn, v_smtp_server);
    UTL_SMTP.command(conn, 'AUTH', 'LOGIN');
    UTL_SMTP.command(conn, base64username );
    UTL_SMTP.command(conn, base64password );

    utl_smtp.mail(conn, v_from_name);
    utl_smtp.rcpt(conn, v_to_name);

  -- Open data
    utl_smtp.open_data(conn);

  -- Message info
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('To: ' || v_to_name || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('From: ' || v_from_name || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Subject: ' || v_subject || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('MIME-Version: 1.0' || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: multipart/mixed; boundary="SECBOUND"' || v_crlf || v_crlf));

  -- Message body
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || v_message_type || v_crlf || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(v_message_body || v_crlf));

  -- Attachment Part
    FOR i IN attachments.FIRST .. attachments.LAST
    LOOP
    -- Attach info
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).data_type
                            || ' name="'|| attachments(i).attach_name || '"' || v_crlf));
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'
                            || attachments(i).attach_name || '"' || v_crlf || v_crlf));

    -- Attach body
        n_offset := 1;
        WHILE n_offset < dbms_lob.getlength(attachments(i).attach_content)
        LOOP
            utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(dbms_lob.substr(attachments(i).attach_content, n_amount, n_offset)));
            n_offset := n_offset + n_amount;
        END LOOP;
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('' || v_crlf));
    END LOOP;

  -- Last boundry
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND--' || v_crlf));

  -- Close data
    utl_smtp.close_data(conn);
    utl_smtp.quit(conn);

END;

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