Oracle PL/SQL Merge with sequence

In Oracle 11g it is not possible to use a sequence in the USING clause of a MERGE statement. To get around this limitation enter a dummy integer in the USING clause and specify the sequence in the VALUES clause.

Putting Sequence in the subquery following USING in a MERGE statement gives an error
-- Eg>

MERGE INTO mytab tab
USING (SELECT 1 as col1,  myseq.nextval seq from dual) q1
ON (tab.col1 = q1.col1)  -- The ON clause will take ANDs eg. ON (t1.c1 = t2.c1 AND t1.c2=t2.c2)
WHEN NOT MATCHED THEN
INSERT (
  col1
)
VALUES (
  myseq.nextval
);

Gives

Error report:
SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.

Use a dummy value eg -999 instead

MERGE INTO mytab tab
USING (SELECT 1 as col1, -999 seq from dual) q1
ON (tab.col1 = q1.col1)
WHEN NOT MATCHED THEN
INSERT (
  col1
)
VALUES (
  myseq.nextval
);

Oracle 11G Shrinking Temp Tablespace

There are a few methods to shrink the temporary tablespace in Oracle 11G. In some cases you can directly resize or shrink the tablespace. If that is not possible then create a new temporary tablespace, reassign the default temp space and drop the old tablespace.


SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name like 'TEMP%';


alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M;
alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M

-- Can give -
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

-- If that fails, on 11G do -
alter tablespace TEMP shrink space keep 256M;

-- If that fails do

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/datapath/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE temp
2  TEMPFILE '/datapath/temp01.dbf' SIZE 256M REUSE
3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

From http://stackoverflow.com/questions/1824572/how-to-shrink-temp-tablespace-in-oracle

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