Phil's Notes

Tagged “pl/sql”

  1. Identity Column Gotcha in Oracle 19c

    2. oracle pl/sql

    I recently created a new table in a database that I maintain and decided to use the identity column feature to have unique IDs in my table. In the past when I was using an older version of Oracle, this feature was not available, so I had to create a sequence and a trigger each time I wanted to have a table with an auto-generated ID. So I created that table with the identity column to always generate an ID. Then to fill the table with data, I used an insert all statement. To my surprise, all the IDs for all the rows were the same. Turns out you need to insert one row at a time for the Identity column to work correctly.

    An example to replicate:

    -- Create the table
    create table userlist 
    (id int generated always as identity,
    username varchar2(10) not null);
     
    -- Insert the values
    insert all
    into userlist (username) values ('Phil')
    into userlist (username) values ('Bob')
    into userlist (username) values ('Charlie')
    select * from dual;
    commit;
     
    -- Query the table
    -- You will see here that all the users have the same ID.
    select * from userlist;

    The resulting table will look something like this:

    ID USERNAME
    1 Phil
    1 Bob
    1 Charlie

    To prevent this from happening, you just have to add PRIMARY KEY to the definition of the ID column. That way when you come across this issue you will get an error that won’t let you add those rows instead. In older versions, primary keys were defined by adding a constraint, but this way is easier.

    create table userlist 
    (id int generated always as identity primary key,
    username varchar2(10) not null);

  2. Get Bad Login Attempts in Oracle

    3. oracle pl/sql

    If you want to audit your database to see when and where bad login attempts are happening, you can run the query below:

    select *
    from dba_audit_trail
    where returncode = 1017;

    Here are a few other return codes you can check for:

    Return Codes

    Return Code Description
    0 Success
    1017 Bad login attempt
    28000 Account is locked

  3. Get the Session and Database Timezone Offset in Oracle

    4. oracle pl/sql

    If you are scheduling a procedure to query a TIMESTAMP WITH LOCAL TIME ZONE column, you may have noticed that when the database runs the query it returns a different time than when you run that same query yourself. That’s because the database is set to a different timezone than your session.

    Returning the Timezone

    To see the database’s timezone:

    select dbtimezone from dual;

    To see your session’s timezone:

    select sessiontimezone from dual;

    Altering the Timezone

    You can alter your session’s timezone offset by the name of the timezone or by adding or subtracting the hours yourself.

    alter session set time_zone = '-5:0';

    alter session set time_zone = 'America/New_York';

    If you want to temporarily set the timezone within a procedure, you will need to wrap the alter session statement in an execute immediate statement:

    execute immediate 'alter session set time_zone = ''America/New_York''';

    View All Available Timezones Names

    Oracle has a list of all the timezone names here:

    https://docs.oracle.com/cd/B19306_01/server.102/b14225/applocaledata.htm#i637736

    You can also view the list of available timezones by querying your database:

    select *
    from v$timezone_names;

  4. Finding the Unique Column in an Oracle Table

    5. sql pl/sql oracle

    If you ever find yourself looking for the unique column of a table in an Oracle database, but don’t see a primary key assigned and you don’t want to run a query against all the records in the table because it is too large, you can try querying against DBA_TABLES or ALL_TABLES to get the number of rows in that table.

    SELECT NUM_ROWS
    FROM DBA_TABLES --you can also use ALL_TABLES
    WHERE TABLE_NAME = 'your_table_name';

    Once you have the number of rows, query DBA_TAB_COLUMNS or ALL_TAB_COLUMNS to see which column has the same distinct number of rows (NUM_DISTINCT) as the NUM_ROWS from the previous query.

    SELECT COLUMN_NAME, NUM_DISTINCT
    FROM DBA_TAB_COLUMNS --you can also use ALL_TAB_COLUMNS
    WHERE TABLE_NAME = 'your_table_name'
    ORDER BY NUM_DISTINCT DESC;

    Source: https://stackoverflow.com/a/4010826/991383

See all tags.