Home » SQL & PL/SQL » SQL & PL/SQL » To get the name of the object(table/view) throwimg exception
To get the name of the object(table/view) throwimg exception [message #436181] Tue, 22 December 2009 00:55 Go to next message
neha7.m
Messages: 6
Registered: December 2009
Junior Member
Hi,

I need to write a procedure which archives a table, deletes the old dat afrom it and inserts the new set of data. In this process, it needs be ensured that the table is archived before being dropped else the drop doesnt execute.

The code goes like this:

sqlplus  slsp/slsp@intel <<ENDOFSQL
spool batch_error_arch.lst
SET SERVEROUTPUT ON
DECLARE
        table_doesnot_exist EXCEPTION;
        object_not_found EXCEPTION;
        spcfd_index_doesnt_exist EXCEPTION;
        index_name_already_used EXCEPTION;
        primary_key_exception EXCEPTION;
        PRAGMA EXCEPTION_INIT(table_doesnot_exist,-942);
        PRAGMA EXCEPTION_INIT(object_not_found,-4043);
        PRAGMA EXCEPTION_INIT(spcfd_index_doesnt_exist,-1418);
        PRAGMA EXCEPTION_INIT(index_name_already_used,-955);
        PRAGMA EXCEPTION_INIT(primary_key_exception,-2260);
      
        
BEGIN
        execute immediate 'Drop table batch_error_log_archieve';
        execute immediate 'Rename batch_error_log to batch_error_log_archieve';
        execute immediate 'create index IDX_BATCH_ERROR_ARCHIEVE on batch_error_log_archieve(to_char(DM_LSTUPDDT,''YYYYMMDD''))';
        execute immediate 'Create table batch_error_log as select * from batch_error_log_archieve where to_char(DM_LSTUPDDT,''YYYYMMDD'') >(select to_char(add_months(sysdate, -6),''YYYYMMDD'') from dual)';
        execute immediate 'alter table batch_error_log ADD primary key (MSGSEQNUM)';
        execute immediate 'delete from batch_error_log_archieve where to_char(DM_LSTUPDDT,''YYYYMMDD'') > (select to_char(add_months(sysdate, -6),''YYYYMMDD'') from dual)';

EXCEPTION

        WHEN table_doesnot_exist THEN
        DBMS_OUTPUT.PUT_LINE('ORA - 00942 : TABLE DOESNOT EXIST');

        WHEN object_not_found THEN
        DBMS_OUTPUT.PUT_LINE('ORA-4043 : OBJECT TO BE RENAMED DOESNOT EXIST');

        WHEN spcfd_index_doesnt_exist THEN
        DBMS_OUTPUT.PUT_LINE('ORA-1418 : SPECIFIED INDEX DOESNOT EXIST');

        WHEN index_name_already_used THEN
        DBMS_OUTPUT.PUT_LINE('ORA-00955 : THE INDEX NAME HAS ALREADY BEEN USED');

        WHEN primary_key_exception THEN
        DBMS_OUTPUT.PUT_LINE('ORA-02260 : THERE CAN EXIST ONLY ONE PRIMARY KEY FOR A TABLE');

       
        
      
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLERRM: '||SQLERRM);
        DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);

END;
/


Here, if the table doesn't exist, table_doesnot_exist exception 'll be thrown. And the specified output 'll be displayed at the terminal.
Now, I need to know the table name as well which's causing the exception. Is it possible to print the table/view name as well?

Thanks in advance.
Re: To get the name of the object(table/view) throwimg exception [message #436185 is a reply to message #436181] Tue, 22 December 2009 01:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Start with a query on user_tables and proceed from there, depending on its results.
By the way, in the majority of cases, it is considered bad practice to drop and (re)create database-objects at runtime. It will invalidate objects that are dependent on them.
Although I can see the reasoning behind what you are doing here and why you might have chosen this approach, have you considered alternatives? (eg partitioned tables, moving partitions)
Re: To get the name of the object(table/view) throwimg exception [message #436197 is a reply to message #436181] Tue, 22 December 2009 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can for instance:
1/ Remove the exception clause as it says the same thing as the error message Oracle will raise and it will raise it with the line number
2/ Or enclose each statement in BEGIN/END/EXCEPTION block

Regards
Michel
Re: To get the name of the object(table/view) throwimg exception [message #436216 is a reply to message #436197] Tue, 22 December 2009 03:09 Go to previous messageGo to next message
neha7.m
Messages: 6
Registered: December 2009
Junior Member
Thanks Michel and Frank. But your suggestions ain't helping me much. As I mentioned before, I need the NAME of the table/view. I
Re: To get the name of the object(table/view) throwimg exception [message #436218 is a reply to message #436216] Tue, 22 December 2009 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you put each statement in a PL/SQL block you can raise the message you want INCLUDING the table/view name.

Regards
Michel
Re: To get the name of the object(table/view) throwimg exception [message #436219 is a reply to message #436216] Tue, 22 December 2009 03:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Surely, if you query user_tables you should be able to verify what it is you are missing?!
Furthermore, could you explain how an error-message displaying the line-number (following Michel's suggestion) would result in ambiguous errors?
Re: To get the name of the object(table/view) throwimg exception [message #436228 is a reply to message #436181] Tue, 22 December 2009 04:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Each of your exceptions can only be raised by one of your DDL statements, and each statement only refers to a single table - why don't you just include the table name in the error message - you aren't using any sort of input table name anywhere that I can see.

Also, these statements:
execute immediate 'Create table batch_error_log as select * from batch_error_log_archieve where to_char(DM_LSTUPDDT,''YYYYMMDD'') >(select to_char(add_months(sysdate, -6),''YYYYMMDD'') from dual)';

execute immediate 'delete from batch_error_log_archieve where to_char(DM_LSTUPDDT,''YYYYMMDD'') > (select to_char(add_months(sysdate, -6),''YYYYMMDD'') from dual)';


would be better rewritten as:
execute immediate 'Create table batch_error_log as select * from batch_error_log_archieve where DM_LSTUPDDT > add_months(sysdate, -6)';

execute immediate 'delete from batch_error_log_archieve where DM_LSTUPDDT > add_months(sysdate, -6)';
Re: To get the name of the object(table/view) throwimg exception [message #436370 is a reply to message #436228] Wed, 23 December 2009 02:44 Go to previous messageGo to next message
neha7.m
Messages: 6
Registered: December 2009
Junior Member
Thanks everybody. I am through it. I followed Michel and created a separate begin-exceptio-end blocks. Now it displays what causes the exception.
The code goes as:
sqlplus  slsp/slsp@intel <<ENDOFSQL
spool job_run_stat_arch_new.lst
SET SERVEROUTPUT ON
DECLARE
        -- DECLARING EXCEPTIONS
        table_doesnot_exist EXCEPTION;
        spcfd_indx_dsnt_exist EXCEPTION;
        objectnotfound EXCEPTION;
        invalid_identifier EXCEPTION;
        missing_left_paranthesis EXCEPTION;
        column_list_already_indexed EXCEPTION;
        index_name_already_used EXCEPTION;
        primary_key_exception EXCEPTION;
        unexpected_symbol EXCEPTION;
        -- DEFINING EXCEPTIONS
        PRAGMA EXCEPTION_INIT(table_doesnot_exist, -942);
        PRAGMA EXCEPTION_INIT(spcfd_indx_dsnt_exist,-1418);
        PRAGMA EXCEPTION_INIT(objectnotfound, -4043);
        PRAGMA EXCEPTION_INIT(invalid_identifier,-904);
        PRAGMA EXCEPTION_INIT(column_list_already_indexed, -1408);
        PRAGMA EXCEPTION_INIT(missing_left_paranthesis, -906);
        PRAGMA EXCEPTION_INIT(index_name_already_used,-955);
        PRAGMA EXCEPTION_INIT( primary_key_exception,-2260);
        PRAGMA EXCEPTION_INIT(unexpected_symbol,-6550);

BEGIN
        execute immediate 'Drop index IDX_J_R_S_1';
BEGIN
        execute immediate 'create index IDX_JOB_RUN_ARCHIEVE_1 on job_run_statistics_archieve(STATUS)';
        execute immediate 'create index IDX_JOB_RUN_ARCHIEVE_2 on job_run_statistics_archieve(to_char(DM_LSTUPDDT,''YYYYMMDD'
'))';
        execute immediate 'delete from job_run_statistics_archieve where STATUS = 1';
BEGIN
        execute immediate 'Insert into job_run_statistics select * from job_run_statistics_archieve where to_char(DM_
LSTUPDDT,''YYYYMMDD'') > (select to_char(add_months(sysdate, -6),''YYYYMMDD'') from dual)';

BEGIN
          execute immediate 'Alter table job_run_statistics ADD primary key (JOB_RUN_ID)';
          execute immediate 'Create index IDX_J_R_S_1 on job_run_statistics (STATUS)';
EXCEPTION
        WHEN primary_key_exception THEN
        DBMS_OUTPUT.put_line('Primary Key Exception on table job_run_statistics Column : JOB_RUN_ID');

        WHEN invalid_identifier THEN
        DBMS_OUTPUT.put_line('INVALID Identifier : STATUS: on table :job_run_statistics');
END;

EXCEPTION
        WHEN table_doesnot_exist THEN
        DBMS_OUTPUT.put_line('TAble : job_run_statistics : does not exist');

END;

EXCEPTION
        WHEN index_name_already_used THEN
        DBMS_OUTPUT.put_line('Index name : IDX_JOB_RUN_ARCHIEVE_1  OR IDX_JOB_RUN_ARCHIEVE_2 : is already used');

        WHEN table_doesnot_exist THEN
        DBMS_OUTPUT.put_line('Table job_run_statistics_archieve : does not exist');
END;

EXCEPTION
        WHEN spcfd_indx_dsnt_exist THEN
        DBMS_OUTPUT.put_line('Index :IDX_J_R_S_1 : doesnt exist');
END;
/

Re: To get the name of the object(table/view) throwimg exception [message #436372 is a reply to message #436370] Wed, 23 December 2009 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The plan is not
BEGIN
  BEGIN
    ...
    BEGIN
     ...
     BEGIN
      ...
     EXCEPTION
      ...
     END
   EXCEPTION
    ...
   END
  EXCEPTION
   ...
  END
EXCEPTION
  ...
END

but
DECLARE
  something EXCEPTION;
BEGIN
  BEGIN
    ...
  EXCEPTION
    .... 
    RAISE something;
  END  
  BEGIN
    ...
  EXCEPTION
    .... 
    RAISE something;
  END  
  BEGIN
    ...
  EXCEPTION
    .... 
    RAISE something;
  END
EXCEPTION
  WHEN something ....
END

Then it is clearer and limit the level of embbeded blocks.

Regards
Michel
Re: To get the name of the object(table/view) throwimg exception [message #436375 is a reply to message #436372] Wed, 23 December 2009 03:23 Go to previous messageGo to next message
neha7.m
Messages: 6
Registered: December 2009
Junior Member
No Michel. The plan I've used is more proper considering the requirement resting with me. The codeis supposed to stop executing further as soon as any error is encountered.
Re: To get the name of the object(table/view) throwimg exception [message #436377 is a reply to message #436375] Wed, 23 December 2009 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is also when does the code I posted, it is just another way to write it and I think it is clearer than nested blocks (above all if you indent the code as it most be done).

Regards
Michel

[Updated on: Wed, 23 December 2009 03:40]

Report message to a moderator

Re: To get the name of the object(table/view) throwimg exception [message #436378 is a reply to message #436375] Wed, 23 December 2009 03:45 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, and Michel's suggestion does that, while also being more readable, and easier to expand.

I still don't see how you can have this problem, as in your code the exception that you get uniquely defines the table that was involved - if you get an ORA-02260, then it has to have happened on table job_run_statistics, as that's the only primary key you create.

You've also overlooked the other easy method - store the table name in a local variable before each execution - that way you know what the table involved in the error was:
        
DECLARE
...
  v_table   varchar2(30);

BEGIN
  v_table := 'batch_error_log_archieve';
  execute immediate 'Drop table batch_error_log_archieve';

  v_table := 'batch_error_log';
  execute immediate 'Rename batch_error_log to batch_error_log_archieve';

  v_table := 'batch_error_log_archieve';
  execute immediate 'create index IDX_BATCH_ERROR_ARCHIEVE on batch_error_log_archieve(to_char(DM_LSTUPDDT,''YYYYMMDD''))';

  v_table := 'batch_error_log';
  execute immediate 'Create table batch_error_log as select * from batch_error_log_archieve where to_char(DM_LSTUPDDT,''YYYYMMDD'') >(select to_char(add_months(sysdate, -6),''YYYYMMDD'') from dual)';
....

EXCEPTION
..


I see you're also quite clue resistant on the point of not needing to SELECT from dual to get your dates.
Previous Topic: dateadd in plsql?
Next Topic: Export
Goto Forum:
  


Current Time: Thu Dec 08 22:10:02 CST 2016

Total time taken to generate the page: 0.10021 seconds