Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: invalid identifier (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
ORA-00904: invalid identifier [message #681200] Thu, 25 June 2020 05:10 Go to next message
s4.ora
Messages: 71
Registered: March 2010
Member
Hi All,

Can anyone tell me why is the below procedure is not getting compiled?

create or replace procedure TRAIL_ELEMENT_CREATE_BATCH_TAB(p_object_type varchar2, p_run_id number, p_no_of_batch number)
IS
	err_msg varchar2(4000);
	proc_name varchar2(30);
	table_does_not_exist EXCEPTION;
    PRAGMA exception_init ( table_does_not_exist,-942 );
	PRAGMA exception_init ( invalid_identifier_exception, -904 );
BEGIN

	proc_name := $$plsql_unit;
	execution_log_writer (p_object_type, proc_name, p_run_id, 'S', null);

BEGIN
	EXECUTE IMMEDIATE 'truncate table STE_GTT_BNO';
EXCEPTION
	WHEN table_does_not_exist THEN
		NULL;
END;

BEGIN
	EXECUTE IMMEDIATE 'drop table STE_GTT_BNO';
EXCEPTION
	WHEN table_does_not_exist THEN
		NULL;
END;

BEGIN
	EXECUTE IMMEDIATE 'alter table stg_trail_element drop column BATCH_NO';
EXCEPTION
	WHEN invalid_identifier_exception THEN
		NULL;
END;

execute immediate '
CREATE TABLE STE_GTT_BNO
as
select ste.*, NTILE('||p_no_of_batch||') OVER (ORDER BY TRAIL_GRANITE_ID) AS BATCH_NO 
from STG_TRAIL_ELEMENT ste
';

execute immediate 'drop table STG_TRAIL_ELEMENT';

execute immediate 'alter table STE_GTT_BNO rename to STG_TRAIL_ELEMENT';

	execution_log_writer (p_object_type, proc_name, p_run_id, 'C', null);

-- exception
	-- when others then
		-- err_msg := dbms_utility.format_error_backtrace();
		-- execution_log_writer (p_object_type, proc_name, p_run_id, 'E', err_msg);

END;
/
I am getting the below error

Procedure TRAIL_ELEMENT_CREATE_BATCH_TAB compiled

LINE/COL ERROR
--------- -------------------------------------------------------------
7/26 PLS-00109: unknown exception name 'INVALID_IDENTIFIER_EXCEPTION' in PRAGMA EXCEPTION_INIT
Errors: check compiler log
Re: ORA-00904: invalid identifier [message #681201 is a reply to message #681200] Thu, 25 June 2020 05:15 Go to previous message
s4.ora
Messages: 71
Registered: March 2010
Member
Hi All,

Kindly ignore the post.. this is my Bad.. I need to declare the Exception first, like the way I declared for "table_does_not_exist"....
Previous Topic: View and Table created from same query giving different result
Next Topic: Creating a record from existing random records (5 merged)
Goto Forum:
  


Current Time: Mon Sep 28 15:00:23 CDT 2020