Disable Password expiry for Oracle Accounts

To disable password expiry for Oracle accounts find whihc profile is in use and then change the default timeout on the profile. If the users are already expired and locked the password must be reset and the account unlocked.


select * from dba_users; -- find the users and which profile they use

-- change the DEFAULT profile to not expire
ALTER PROFILE DEFAULT LIMIT
  PASSWORD_LIFE_TIME UNLIMITED;

-- could also add FAILED_LOGIN_ATTEMPTS UNLIMITED

Oracle PL/SQL Associative Arrays using %ROWTYPE

Creating an Associative Array in Oracle PL/SQL from a table using %ROWTYPE
This example creates an in memory table using the row type from another table then populates the tbale in batches of 1000. This allows operations to be done on the data before inserting it into a third table.

  procedure my_proc
  is
    TYPE tt_table IS TABLE OF mytable%ROWTYPE;
    l_id      NUMBER(38,0)  := -1;
    l_tab tt_table;
        cursor c_data is
      select * from mytable;
  begin
    open c_data;
    loop
      fetch c_data
      bulk collect into l_tab limit 1000;
      for i in 1 .. l_tab.count
      loop
        insert into other_tab (COL1)
        VALUES (l_tab(i).MYCOL1);
   -- Do other conditional processing here (otherwise we could just insert directly from a query)
      end loop;
      exit when l_tab.count = 0;
    end loop;
    close c_data;
  end;

Also see the example of using Associative Arrays at  - http://notastrophe.blogspot.com/2013/03/oracle-plsql-associative-arrays.html

Oracle PL/SQL Convert BOOLEAN to STRING

In Oracle pl/sql there is no direct method to convert a boolean type to a character type. It is necessary to test the boolean with a case statement as below:

l_varchar := case l_bool when TRUE then 'TRUE' else 'FALSE' end;

ORACLE PL/SQL Associative Arrays

An example of an Associative Array in Oracle 11g.
Associative arrays give you the ability to create in memory tables of a given datatype and iterate over them. This example shows the declaration of a table of character data which is populated from a select statement on an Oracle table.



-- Assign values to an empty associative array
procedure myproc
is
  TYPE tt_vals IS TABLE OF VARCHAR2(80);

    l_vals  tt_vals;
   l_count  NUMBER := 1;
begin
  l_vals := tt_vals(10);  -- l_vals.COUNT is 1 here and l_vals.EXISTS(1) is TRUE
  for mychar in (
    select CHAR_COL from mytab;
 )
LOOP
  l_vals.extend;
  l_vals(l_count) := mychar;
  l_count := l_count+1;
END LOOP;


----

l_vals(1) gives the value of the first element in the array, l_vals(2) the second....
l_vals.exists(N) evaluates to TRUE if the Nth value exists, FALSE otherwise
l_vals.count gives count of elements in the array

Other Collection Methods for the Associative Array are -
LIMIT,
FIRST and LAST,
PRIOR and NEXT for looping,
TRIM,
DELETE.

See Oracle PL/SQL Language Reference (pdf)

Also - http://notastrophe.blogspot.com/2013/03/oracle-plsql-associative-arrays-using.html

OBIEE Inconsistent Datatype Error

Using Bins in Oracle OBIEE I was getting ORA-00932: inconsistent datatypes: expected CHAR got NUMBER error.

The error occurred when the datatype of the column was Number, but I had a mix of String and Number in the Bin Name.

Eg.

CASE WHEN <TABLE.COLUMN> IS BETWEEN 30 and 39 THEN '30-39'
WHEN <TABLE.COLUMN> IS BETWEEN 40 and 49 THEN '40-49'
.....
ELSE
<TABLE.COLUMN>
END.

Becomes

CASE WHEN <TABLE.COLUMN> IS BETWEEN 30 and 39 THEN '30-39'
WHEN <TABLE.COLUMN> IS BETWEEN 40 and 49 THEN '40-49'
.....
ELSE
'Other'
END