Category Archives: SQL

Create PL/SQL script handling errors

Problem

The aim of this article is to create a shell script calling a pl/sql script. These two scripts should handle errors an generate logs.

Solution

This article will present the shell script and then the pl sql script.

1. The shell script

This shell script will show how to manage inputs, display information and handle errors from pl sql script.

dir=`pwd`
cd "$dir"

# Prints the usage
usage()
{
    printf "$0 -a input1 -b input2 \n"
    printf "Script called.\n"
    printf "\t-a input1\t: my first input\n";
    printf "\t-b input2\t: my second input\n";
    printf "\t-h\t\t: shows usage\n";
    printf "\n"
    exit 1;
}

# Processes the options on the command line
processInputs()
{

# user of the database
USER=user

# pass of the database
PASS=pass
# service can be found in tnsnames.ora
SERVICE=service

INPUT1=
INPUT2=
while getopts "a:b" name; do
      case $name in
      a) INPUT1=$INPUT1 ;;
      b) INPUT2=$INPUT2 ;;   
      h) usage ;;
      \?)  usage ;;
      esac
done


if [ -z "$INPUT1" ]; then
    printf "ERROR: input 1 is not specified\n"
    usage;
fi

if [ -z "$INPUT2" ]; then
    printf "ERROR: input 2 is not specified\n"
    usage;
fi

echo "call pl sql script"
sqlplus -S $USER/$PASS@$SERVICE @mypl_sql_script.sql
sql_return_code=$?

if [ $sql_return_code != 0 ]
then
echo "error in the script"
echo "Error code $sql_return_code"
exit 0;
fi
echo "call pl sql script finished"

}

processInputs "$@"

2. The pl sql script

This is a simple pl sql where a rollback is done if any errors occur. Also a log script is generated. This script is called by the previous shell script.


spool log.txt
set serveroutput on
VARIABLE  v_return_code number;
declare
    vrequest varchar2(1000);
    vrequestInsert varchar2(1000);

begin
	vrequest := 'SELECT * FROM CUSTOMER_USER WHERE NAME= ''john''';
       vrequestInsert := 'Insert into GROUP_USER (ID,VERSION,TYPE,NAME) values (''11'',''0'',''blah'',''admin'')';

execute immediate vrequestInsert ;
        execute immediate vrequest;
execute immediate vrequestInsert ;


       :v_return_code:=0;

     exception
     when others then
        dbms_output.put_line('KO - ' || vrequest || ' - ' || sqlerrm);  
        :v_return_code:=sqlerrm;

        ROLLBACK;
      
end;
/   
spool off

EXIT :v_return_code;

3. Some DDL Statements have auto commit after exceution

For some DDL statements the rollback mechanism does not work.

DDL statements do commit (ALTER/CREATE etc) and this will happen even if something failed. If you’re running EXECUTE IMMEDIATE like dynamic statement that runs a DDL, this will also commit your transaction.

https://stackoverflow.com/questions/18042589/pl-sql-auto-commit-on-execution

I would add that DROP as well does auto commit after execute immediate.

Advertisements

Unusable INDEX on PARTITION table

When index is unusable on table, it prevents the updating/inserting of new data

Everyday a clean up task would messed up a partitioned table. Specifically the following command would make the indexes unusable :


ALTER TABLE table_name DROP PARTITION partition_name;

Thus the table would have unusable indexes. This problem prevents from importing new data.

 

Description of the partitionned table

The table with unusable indexes is partitioned and has two indexes. One index is on the primary key and the other index on a date column MY_TABLE_DATE.
The table is partitioned with the method “Range Partitioning”

Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.

https://docs.oracle.com/cd/B10500_01/server.920/a96524/c12parti.htm

The table is partitionned with the date and has a primary key :


create table MY_TABLE
(
ID NUMBER GENERATED BY DEFAULT AS IDENTITY ,
MY_TABLE_DATE DATE NOT NULL,
NAME VARCHAR2(3) NOT NULL,
SURNAME VARCHAR2(20) NOT NULL,
PRIMARY KEY(ID))
)
PCTFREE 10
TABLESPACE tab_p_specified_space
PARTITION BY RANGE (MY_TABLE_DATE )
(PARTITION P_20100101 VALUES LESS THAN (TO_TIMESTAMP('2010/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS') )
PCTFREE 10
TABLESPACE tab_p_specified_space
);

Actually this primary key is a problem according to Oracle documentation. Indeed the partitionned column should be part of the primary key. Read on from « Partitioned Index-Organized Tables » :

For partitioning an index-organized table
Partition columns must be a subset of primary key columns

https://docs.oracle.com/cd/B10500_01/server.920/a96524/c12parti.htm

To make the partitionned column part of the primary key , do something like this:


ALTER TABLE MY_TABLE DROP CONSTRAINT TABLE_PK;
ALTER TABLE MY_TABLE ADD CONSTRAINT TABLE_PK PRIMARY KEY (ID, MY_TABLE_DATE) USING INDEX LOCAL TABLESPACE tab_p_specified_space;

Unfortunately this modification of the partitioned table did not fix the problem either.

Solution

In order to fix unusable indexes I used the command “UPDATE INDEXES” suggested by this article https://docs.oracle.com/database/121/VLDBG/GUID-1D59BD49-CD86-4BFE-9099-D3B8D7FD932A.htm#VLDBG1122

“UPDATE INDEXES” will rebuild the indexes after the command DROP PARTITION is executed.


requestDeletePartition := 'ALTER TABLE BASE.MY_TABLE DROP PARTITION TO_CHAR(Date,'YYYYMMDD') || 'UPDATE INDEXES' ;

Unfortunately the result was the following error :

SQLCODE : -14048
SQLERRM : ORA-14048: a partition maintenance operation may not be combined with other operations

I spend sometimes trying to figure out the solution for this error . The solution is simple . I added a space between ‘ and UPDATE INDEXES like this :


requestDeletePartition := 'ALTER TABLE BASE.MY_TABLE DROP PARTITION TO_CHAR(Date,'YYYYMMDD') || ' UPDATE INDEXES' ;

The solution will rebuild indexes after dropping the partition. This fix is not sustainable for every scenario. Because it means to launch the command of rebuilding indexes everyday on a table with potentially millions of data. When the indexes are being rebuilt, the table cannot be used. Thus this command should be launched when nobody is using the application.

Annexes

Another tested solution

On google we can quickly find interesting articles how to fix the problem :


ALTER INDEX index_name REBUILD

https://docs.oracle.com/database/121/SPATL/alter-index-rebuild.htm#SPATL1017

This is a similar solution to the previous one. The table is not usable when the indexes are being rebuilt.

Other solution possible
I did not test this solution but it should fix the problem too.

https://www.toadworld.com/platforms/oracle/b/weblog/archive/2014/09/26/the-trick-of-drop-a-table-partition-without-impact-the-global-index