Drop table in PL SQL

To move a table to the recycle bin or remove it entirely from the database, you use the DROP TABLE statement:

   DROP TABLE schema_name.table_name
   [CASCADE CONSTRAINTS | PURGE];
  • First, indicate the table and its schema that you want to drop after the DROP TABLE clause. If you don’t specify the schema name explicitly, the statement assumes that you are removing the table from your own schema.

  • Second, specify CASCADE CONSTRAINTS clause to remove all referential integrity constraints which refer to primary and unique keys in the table. In case such referential integrity constraints exist and you don’t use this clause, Oracle returns an error and stops removing the table.

  • Third, specify PURGE clause if you want to drop the table and release the space associated with it at once. By using the PURGE clause, Oracle will not place the table and its dependent objects into the recycle bin.

Refernces

oracle tutorial


Column-Level Collation and Case-Insensitive Database in Oracle

Collation determines how strings are compared, which has a direct impact on ordering (sorting) and equality tests between strings.

There are two basic types of collation.

  • Binary : Ordering and comparisons of string data are based on the numeric value of the characters in the strings.
  • Linguistic : Ordering and comparisons of string data are based on the alphabetic sequence of the characters, regardless of their numeric values. The list of linguistic collations is available here.

When using collations there are three suffixes that alter the behaviour of sorts and comparisons.

  • “_CI” : Case insensitive, but accent sensitive.
  • “_AI” : Both case and accent insensitive.
  • “_CS” : Both case and accent sensitive. This is default if no extension is used.

If no collation is specified, directly or via a default setting, the default USING_NLS_COMP pseudo-collation is used, which means the NLS_SORT and NLS_COMP parameters are used to determine the actual collation used.

// Syntax
COLLATE BINARY_CS / BINARY_CI / BINARY_AI

column_name  VARCHAR2(15 CHAR) COLLATE BINARY_CI
create table (...) DEFAULT COLLATION BINARY_CI;
ALTER TABLE t1 DEFAULT COLLATION BINARY_AI;
References

Oracle Base


Oracle NLS / National Language Suppport

The nls_database_parameters shows the values of the NLS parameters for the database. Oracle notes these differences between the parameters.

  • NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the session that is querying the view. It does not show information about the character set.
  • NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.
  • NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the database. The values are stored in the database.
SELECT * FROM nls_session_parameters ORDER BY 1;   
SELECT * FROM nls_instance_parameters ORDER BY 1;  
SELECT * FROM nls_database_parameters ORDER BY 1; 

The NLS_LANGUAGE and NLS_TERRITORY values in nls_database_parameters cannot be changed once the database has been created

References

dba-oracle
Oracle Blog


List of jobs and jobs history - PL/SQL

PL SQL Query to list the currently running jobs

SELECT job_name, session_id, running_instance, elapsed_time, cpu_used 
FROM dba_scheduler_running_jobs;

To view the jobs history

SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used 
FROM dba_scheduler_job_run_details;   

List the months between two dates - PL/SQL

PL SQL Query to list the months between two dates

SELECT TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE('01-JAN-22','DD-MON-YY'), 'MM'), LEVEL -1),'MON-YY')
MONTH_YEAR 
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE('01-DEC-22','DD-MON-YY'), 
    TO_DATE('01-JAN -22','DD-MON-YY')) + 1
ORDER BY LEVEL;

Result set

MONTH_YEAR
JAN-22
FEB-22
MAR-22
APR-22
MAY-22
JUN-22
JUL-22
AUG-22
SEP-22
OCT-22
NOV-22
DEC-22

Middleware in the Express.js

Middleware functions are functions that have access to the request object (req), the response object (res), and the next function in the application’s request-response cycle. The next function is a function in the Express router which, when invoked, executes the middleware succeeding the current middleware.

Middleware functions can perform the following tasks:

  • Execute any code.
  • Make changes to the request and the response objects.
  • End the request-response cycle.
  • Call the next middleware in the stack.

If the current middleware function does not end the request-response cycle, it must call next() to pass control to the next middleware function. Otherwise, the request will be left hanging.

To load the middleware function, call app.use(), specifying the middleware function.

For example, the following code loads the myLogger middleware function before the route to the root path (/).

  const express = require('express')
  const app = express()

  const myLogger = function (req, res, next) {
    console.log('LOGGED')
    next()
  }

  app.use(myLogger)

  app.get('/', (req, res) => {
  res.send('Hello World!')
  })

  app.listen(3000)

Snippet for creating a progress bar

Snippet for creating a progress bar in the header section.

  <div class="header mt-100">
    <div class="row">
      <div class="progress">
        <div class="progress-bar" id="myBar"></div>
      </div>
    </div>
  </div>

  <script>
    // When the user scrolls the page, execute scrollFn 
    window.onscroll = function () { scrollFn() };

    function scrollFn() {
      var winScroll = document.body.scrollTop || document.documentElement.scrollTop;
      var height = document.documentElement.scrollHeight - document.documentElement.clientHeight;
      var scrolled = (winScroll / height) * 100;
      document.getElementById("myBar").style.width = scrolled + "%";
    }
  </script>

What is a computer port?

What is a port?

A port is a virtual point where network connections starts and end. Ports are software based and managed by a computer’s operating system. Each port is associated with a specific process or service. Ports allow computers to easily differentiate between different kinds of traffic: emails go to a different port than webpages, for instance, even though both reach a computer over the same Internet connection.

What is a port number?

Ports are standardized across all network-connected devices, with each port assigned a number. Most ports are reserved for certain protocols — for example, all Hypertext Transfer Protocol (HTTP) messages go to port 80. While IP addresses enable messages to go to and from specific devices, port numbers allow targeting of specific services or applications within those devices.

References

Cloudflare


Publish your work increases your luck.

How can we increase the odds of finding luck? By being a person who works in public.

“The amount of serendipity that will occur in your life, your Luck Surface Area, is directly proportional to the degree to which you do something you’re passionate about combined with the total number of people to whom this is effectively communicated.”

Going further, he codifies it into a formula where:

Luck = [Doing Things] * [Telling People]

Do the work. Don’t be afraid to dive deep into your curiosity and your expertise. We need more people that are intensely curious. We need more people with deep expertise.

Tell people. Press publish, bring us along, share the journey. Tell us what you’ve learned, what you’ve built, or what you’re excited about.

The formula may be simple, but I’ll admit it’s not always easy. It’s scary to put yourself out there. It’s hard to open yourself up to criticism. People online can be mean. But for every snarky comment, there are ten times as many people quietly following along and admiring not only your work, but your bravery to put it out publicly. And at some point, one of those people quietly following along will reach out with a life-changing opportunity and you’ll think, “Wow, that was lucky.”

References

ReadME Project


Relational Model

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