SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is there a better way to re-write this SP code

curious_mindJul 31 2018 — edited Aug 28 2018

Hi All,

I have a SP which is running as part of nightly batches being run everyday, but when it is executing the table which is referenced through this SP is throwing one error called ORA-08103: object no longer exists.

we have services teams who are using some SP's which is fetching data from the tables which are getting updated above. please help me with some suggestions.

this is happening frequently in production and consumer is complaining about the behaviour of the service.

PROCEDURE TRUNCATE_TABLE_PART (p_schema_name IN VARCHAR2,

p_table_name IN VARCHAR2,

p_partition_name IN VARCHAR2,

p_message OUT VARCHAR2

)

v_partition_count NUMBER (5);

v_sql VARCHAR2 (2000);

v_message_out VARCHAR2 (200);

partition_does_not_exist EXCEPTION;

PRAGMA EXCEPTION_INIT (partition_does_not_exist, -2149);

BEGIN

v_sql :=

'alter table '

|| p_schema_name

'.'

|| p_table_name

' truncate partition '

|| p_partition_name

;

EXECUTE IMMEDIATE (v_sql);

--Rebuild the unused indexes

ODS_APPLICATION_UTILITY.REBUILD_INDEX (p_schema_name => p_schema_name,

p_table_name => p_table_name,

p_message => v_message_out

);

IF v_message_out = 'Successful' THEN

p_message := 'Successful';

END IF;

EXCEPTION

WHEN partition_does_not_exist THEN

DBMS_OUTPUT.ENABLE(buffer_size => NULL);

DBMS_OUTPUT.PUT_LINE('ORA-02149 Specified partition ' || P_PARTITION_NAME|| ' doesn''t exist');

RAISE;

WHEN OTHERS THEN

DBMS_OUTPUT.ENABLE(buffer_size => NULL);

DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);

RAISE;

END TRUNCATE_TABLE_PART;

here is the code for REBUILD_INDEX

PROCEDURE REBUILD_INDEX (p_schema_name	IN VARCHAR2,
	p_table_name	IN VARCHAR2,
	p_message	OUT VARCHAR2
	)
	AS

v_sql VARCHAR2 (2000);

BEGIN

	FOR index_list IN
	( SELECT index_name
	FROM all_indexes
	WHERE owner=p_schema_name
	AND table_name=p_table_name
	AND status = 'UNUSABLE'
	)

	LOOP
	v_sql := 'ALTER INDEX '\|\|index_list.index_name\|\|' REBUILD';
	EXECUTE IMMEDIATE (v_sql);
	END LOOP;

p_message := 'Successful';

EXCEPTION

	WHEN OTHERS THEN
	DBMS_OUTPUT.ENABLE(buffer_size => NULL);
	DBMS_OUTPUT.put_line(SQLCODE \|\| ':' \|\| SQLERRM);
	RAISE;

END REBUILD_INDEX;

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 25 2018

Added on Jul 31 2018

16 comments

607 views