14 Aug 2022

A database management system (DBMS) is software that controls the storage, organization, and retrieval of data.

Typically, a DBMS has the following elements:

  • Kernel code This code manages memory and storage for the DBMS.

  • Repository of metadata This repository is usually called a data dictionary.

  • Query language This language enables applications to access the data.

A database application is a software program that interacts with a database to access and manipulate data.

The first generation of database management systems included the following types:

  • Hierarchical A hierarchical database organizes data in a tree structure. Each parent record has one or more child records, similar to the structure of a file system.

  • Network

A network database is similar to a hierarchical database, except records have a many-to-many rather than a one-to-many relationship.

References

Oracle

12 Aug 2022

In his seminal 1970 paper “A Relational Model of Data for Large Shared Data Banks,” E. F. Codd defined a relational model based on mathematical set theory. Today, the most widely accepted database model is the relational model.

A relational database is a database that conforms to the relational model. The relational model has the following major aspects:

  • Structures
    Well-defined objects store or access the data of a database.

  • Operations
    Clearly defined actions enable applications to manipulate the data and structures of a database.

  • Integrity rules
    Integrity rules govern operations on the data and structures of a database.

A relational database stores data in a set of simple relations. A relation is a set of tuples (rows). A tuple is an unordered set of attribute (columns) values.

A table is a two-dimensional representation of a relation in the form of rows (tuples) and columns (attributes). Each row in a table has the same set of columns. A relational database is a database that stores data in relations (tables). For example, a relational database could store information about company employees in an employee table, a department table, and a salary table.

References

Oracle

9 Aug 2022

Example of Cursor:

declare    
    cursor c1 is select first_name, salary from hr.employees;
begin    
    for c in c1 
    loop
        dbms_output.put_line('Ename: ' || c.first_name || ', Salary: ' || c.salary);
    end loop;
end;
/

Example of Ref Cursor

declare    
    c1 SYS_REFCURSOR;
    ename varchar2(10);
    sal number;
begin
    open c1 for select first_name, salary from hr.employees;
    LOOP 
        FETCH c1 into ename, sal;
            EXIT WHEN c1%NOTFOUND;
        dbms_output.put_line('Ename: ' || first_name || ', Salary: ' || salary);
    END LOOP;
    close c1;    
end;
/

They are both cursors and can be processed in the same fashion and at the most basic level, they both are same. There are some important differences between regular cursors and ref cursors which are following:

  1. A ref cursor can not be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.

  2. A ref cursor is defined at runtime and can be opened dynamically but a regular cursor is static and defined at compile time.

  3. A ref cursor can be passed to another PL/SQL routine (function or procedure) or returned to a client. A regular cursor cannot be returned to a client application and must be consumed within same routine.

  4. A ref cursor incurs a parsing penalty because it cannot cached but regular cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.

  5. A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.

  6. A regular cursor can more efficiently retrieve data than ref cursor. A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP. A ref cursor must use explicit array fetching.

Use of ref cursors should be limited to only when you have a requirement of returning result sets to clients and when there is NO other efficient/effective means of achieving the goal.

References

Ask Tom
greenstechnologys

8 Aug 2022

A cursor variable is a cursor that contains a pointer to a query result set. The result set is determined by execution of the OPEN FOR statement using the cursor variable.

A cursor variable, unlike a static cursor, is not associated with a particular query. The same cursor variable can be opened a number of times with separate OPEN FOR statements containing different queries. A new result set is created each time and made available through the cursor variable.

Strong typed REF CURSOR

DECLARE 
    TYPE customer_t IS REF CURSOR RETURN customers%ROWTYPE;
    c_customer customer_t;

This form of cursor variable called strong typed REF CURSOR because the cursor variable is always associated with a specific record structure, or type.

And here is an example of a weak typed REF CURSOR declaration that is not associated with any specific structure:

Weak typed REF CURSOR

DECLARE 
    TYPE customer_t IS REF CURSOR;
    c_customer customer_t;

Starting from Oracle 9i, you can use SYS_REFCURSOR, which is a predefined weak typed REF CURSOR, to declare a weak REF CURSOR as follows:

DECLARE c_customer SYS_REFCURSOR;

The SYS_REFCURSOR data type is known as a weakly-typed REF CURSOR type. Strongly-typed cursor variables of the REF CURSOR type require a result set specification.

References

OracleTututorial
IBM

2 Aug 2022

We can say two objects are referentially equal when the pointers of the two objects are the same or when the operators are the same object instance. For example {} === {} is false because it is checking referential equality.

Example:

<script>
    var a = 1;
    var b = 1;
    console.log(a == b); // true
    console.log(a === b); // true

    var c = 10;
    var d = "10";
    console.log(c == d); // true
    console.log(c === d); // false

    const name1 = {
        first_name: "sarah",
    };

    const name2 = {
        first_name: "sarah",
    };

    console.log(name1 == name2); // false
    console.log(name1 === name2); // false

    console.log(Object.is(name1, name2)); // false
    console.log(Object.is(name1, name1)); // true
</script>
References

webdevsimplified
geeksforgeeks
Object is

30 Jul 2022

When a component’s props or state change, React decides whether an actual DOM update is necessary by comparing the same with Virtual DOM. If it is not equal React will update the DOM.

React maintains two Virtual DOM at each time, one contains the updated Virtual DOM and one which is just the pre-update version of this updated Virtual DOM. Now it compares the pre-update version with the updated Virtual DOM and figures out what exactly has changed in the DOM like which components have been changed. This process of comparing the current Virtual DOM tree with the previous one is known as ‘diffing’.

The algorithm used for diffing is known as Diffing Algorithm.

Once React finds out what exactly has changed then it updated those objects only, on real DOM. React uses something called batch updates to update the real DOM. It just means that the changes to the real DOM are sent in batches instead of sending any update for a single change in the state of a component. This entire process of transforming changes to the real DOM is called Reconciliation

References

geeksforgeeks

27 Jul 2022

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. A pseudocolumn is also similar to a function without arguments. However, functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

Rownum is an example for pseudocolumns.

References

Oracle Docs

25 Jul 2022

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, … N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row. A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.

ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

select * from t 
where ROWNUM > 1;

Below query will give you the wrong results,

select *   from emp 
where ROWNUM <= 5 
order by sal desc;

To understand the above, check the below query with this structure:

select ..., ROWNUM
from t
where <where clause>
group by <columns>
having <having clause>
order by <columns>;

Think of it as being processed in this order:

  • The FROM/WHERE clause goes first.
  • ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
  • SELECT is applied.
  • GROUP BY is applied.
  • HAVING is applied.
  • ORDER BY is applied.

Below is the way ti use the rownum

select *   from  
( select * from emp 
order by sal desc ) 
where ROWNUM <= 5;
References

Oracle Blog

24 Jul 2022

VALIDATE_CONVERSION determines whether expr can be converted to the specified data type. If expr can be successfully converted, then this function returns 1; otherwise, this function returns 0. If expr evaluates to null, then this function returns 1. If an error occurs while evaluating expr, then this function returns the error.

SELECT VALIDATE_CONVERSION(1000 AS BINARY_DOUBLE)
FROM DUAL;

SELECT VALIDATE_CONVERSION('1234.56' AS BINARY_FLOAT)
FROM DUAL;

SELECT VALIDATE_CONVERSION('July 20, 1969, 20:18' AS DATE,
    'Month dd, YYYY, HH24:MI', 'NLS_DATE_LANGUAGE = American')
FROM DUAL;

SELECT VALIDATE_CONVERSION('200 00:00:00' AS INTERVAL DAY TO SECOND)
FROM DUAL;

SELECT VALIDATE_CONVERSION('P1Y2M' AS INTERVAL YEAR TO MONTH)
FROM DUAL;

SELECT VALIDATE_CONVERSION('$100,00' AS NUMBER,
    '$999D99', 'NLS_NUMERIC_CHARACTERS = '',.''')
FROM DUAL;

SELECT VALIDATE_CONVERSION('29-Jan-02 17:24:00' AS TIMESTAMP,
    'DD-MON-YY HH24:MI:SS')
FROM DUAL;

SELECT VALIDATE_CONVERSION('1999-12-01 11:00:00 -8:00'
    AS TIMESTAMP WITH TIME ZONE, 'YYYY-MM-DD HH:MI:SS TZH:TZM')
FROM DUAL;

SELECT VALIDATE_CONVERSION('11-May-16 17:30:00'
    AS TIMESTAMP WITH LOCAL TIME ZONE, 'DD-MON-YY HH24:MI:SS')
FROM DUAL;
References

Oracle Docs

20 Jul 2022

The SELECT INTO is actually a standard SQL query where the SELECT INTO clause is used to place the returned data into predefined variables.

create or replace function auth_Name
( v_auth_state IN author.author_state%type)
return varchar2

as
    v_authName author.author_last_name%type;
    
    begin
    select author_last_name into v_authName
    from author
    where author_state = v_auth_state;

    return v_authName;

    exception

    when TOO_MANY_ROWS
    then return 'Too Many Authors in that State';

    when NO_DATA_FOUND
    then return 'No Authors in that State';

    when others
    then raise_application_error(-20011,'Unknown Exception in authName Function');

end;
References

dba-Oracle