PL/SQL ROWNUM

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


Validate Conversion

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


The SELECT INTO Clause

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


Count(*) is unsafe

SELECT COUNT(*) INTO var WHERE condition;

IF var > 0 THEN
SELECT NEEDED_FIELD INTO otherVar WHERE condition;

In PLSQL method with count(*) is unsafe in above code. If another session deletes the row that met the condition after the line with the count(*), and before the line with the select ... into, the code will throw an exception that will not get handled.

Use the below insted,

SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND
References

Stack Overflow


Set Define OFF

The SET DEFINE command changes the prefix character used to mark substitution variables. You can use SET DEFINE to turn variable substitution off.

SET DEF[INE] {OFF | ON | prefix_char}

Define is a SQL*Plus client variable. It is NOT a database level setting.

When you start SQL*Plus, variable substitution will be on by default, and the default prefix character is an ampersand. If you are running a script that uses ampersands in text strings, you may want to change the prefix character to something else. If your script doesn’t use substitution variables, you may find it easiest to turn the feature off.

References

Oreilly


Brag document

Prepare a Brag document for the future self.

The brag document is like a reflection to the past. It will help to show what are your projects, how did you done it, what you learned, you goals and collabrations etc.

References

Julia
Github ReadME


Best practices for writing code comments

1: Comments should not duplicate the code.
2: Good comments do not excuse unclear code.
3: If you can’t write a clear comment, there may be a problem with the code.
4: Comments should dispel confusion, not cause it.
5: Explain unidiomatic code in comments.
6: Provide links to the original source of copied code.
7: Include links to external references where they will be most helpful.
8: Add comments when fixing bugs.
9: Use comments to mark incomplete implementations.

References

Stack Overflow Blog


Motivation for fragments

A common pattern is for a component to return a list of children. In the below example when Column component is rendering the HTML will be invalid as a div is coming in the middle of the table. Whenever we are using Fragments, we can avoid that.

Take this example React snippet:

class Table extends React.Component {
  render() {
    return (
      <table>
        <tr>
          <Columns />
        </tr>
      </table>
    );
  }
}

would need to return multiple elements in order for the rendered HTML to be valid. If a parent div was used inside the render() of , then the resulting HTML will be invalid.

class Columns extends React.Component {
  render() {
    return (
      <div>
        <td>Hello</td>
        <td>World</td>
      </div>
    );
  }
}

results in a output of:

<table>
  <tr>
    <div>
      <td>Hello</td>
      <td>World</td>
    </div>
  </tr>
</table>

useState

useState is a React Hook that lets you add a state variable to your component.

const [statex, setStatex] = useState(initialState)
Usage
  • Adding state to a component
  • Updating state based on the previous state
  • Updating objects and arrays in state
  • Avoiding recreating the initial state
  • Resetting state with a key
  • Storing information from previous renders

Keyed Fragments

Fragments declared with the explicit <React.Fragment> syntax may have keys. A use case for this is mapping a collection to an array of fragments — for example, to create a description list:

function Glossary(props) {
  return (
    <dl>
      {props.items.map(item => (
        // Without the `key`, React will fire a key warning
        <React.Fragment key={item.id}>
          <dt>{item.term}</dt>
          <dd>{item.description}</dd>
        </React.Fragment>
      ))}
    </dl>
  );
}