All
|
Essays
|
Notes

A collection of tech notes, personal reflections, and evolving thoughts about whatever’s caught my curiosity.

notes

PL/SQL ROWNUM

25 July 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

pl-sql database
notes

Validate Conversion

24 July 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

pl-sql database
notes
20 July 2022

Its not an illusion, you are more than enough.

musings
notes
20 July 2022

I don’t know what’s make the evening more beautiful,
you or the sunset.

poetry
notes
20 July 2022

After long, tiring hours,
I want to drink you,
and the tea, slowly

poetry
notes
20 July 2022

Poets are wizards
who can make you dream.

poetry
notes
20 July 2022

Whenever I am looking into my brown eyes,
I can see the magic.
Its says believe in yourself.

musings
notes

PL/SQL SELECT INTO Clause

20 July 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

pl-sql database
notes

Count(*) is unsafe

19 July 2022
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

pl-sql database