Home » SQL & PL/SQL » SQL & PL/SQL » Regarding Invalid object (oracle 10.2.0.2.0,solaris10)
Regarding Invalid object [message #412638] Fri, 10 July 2009 04:50 Go to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member


Hi all,


SQL> create table tab_invalid (abc number);

Table created.

SQL> desc tab
Name Null? Type
------------------------ -------- ----------

TNAME NOT NULL VARCHAR2(3
TABTYPE VARCHAR2(7
CLUSTERID NUMBER

SQL> select * from tab where tname='TAB_INVALID';

TNAME TABTYPE CLUSTERID
-------------------------- ------- ----------
TAB_INVALID TABLE

SQL>
SQL> insert into tab_invalid values (03);

1 row created.

SQL> insert into tab_invalid values (10);

1 row created.

SQL> insert into tab_invalid values (20);

1 row created.

SQL> insert into tab_invalid values (12);

1 row created.

SQL> insert into tab_invalid values (09);

1 row created.

SQL> select * from tab_invalid;

ABC
----------
3
10
20
12
9

SQL> create synonym syn_invalid for tab_invalid;

Synonym created.

SQL>
SQL>
SQL> drop table tab_invalid;

Table dropped.

SQL>
SQL> select * from syn_invalid;
select * from syn_invalid
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid


SQL>
SQL> select * from dba_objects where status = 'invalid';

no rows selected

SQL> select * from dba_objects where status = 'INVALID';

no rows selected




In this case, the table tab_invalid is dropped and when
the synonym (dependent object) is queried it throws error

ERROR at line 1:
ORA-00980: synonym translation is no longer valid

Here when the table is dropped it will become invalid and
if we query the synonym (dependent object)
it will throw error because the dependent object is altered
and has become invalid .

Then i checked the status of the object it showing me valid.

how come ?



Thanks in advance
Re: Regarding Invalid object [message #412646 is a reply to message #412638] Fri, 10 July 2009 05:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's not what I see:
SQL> create table test_002 (col_2  number);

Table created.

SQL> 
SQL> create synonym test_002_syn for test_002;

Synonym created.

SQL> 
SQL> create or replace procedure test_002_prog as
  2    v_cnt   pls_integer;
  3  begin
  4    select count(*)
  5    into   v_cnt
  6    from   test_002_syn;
  7    
  8    dbms_output.put_line(v_cnt);
  9  end;
 10  /

Procedure created.

SQL> 
SQL> select object_name,status from user_objects where object_name like 'TEST_002%';

OBJECT_NAME                                                                                                                      STATUS
------------------------- -------
TEST_002                  VALID
TEST_002_PROG             VALID
TEST_002_SYN              VALID

SQL> 
SQL> drop table test_002;

Table dropped.

SQL> 
SQL> select object_name,status from user_objects where object_name like 'TEST_002%';

OBJECT_NAME                                                                                                                      STATUS
-------------------------- -------
TEST_002_PROG              INVALID
TEST_002_SYN               INVALID
Re: Regarding Invalid object [message #412650 is a reply to message #412638] Fri, 10 July 2009 05:47 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
for me its working fine
SQL>  create table tab_invalid (abc number);

Table created.

SQL>   insert into tab_invalid values (03);

1 row created.

SQL> select * from tab_invalid;

       ABC
----------
         3

SQL>  create synonym syn_invalid for tab_invalid;

Synonym created.

SQL>  drop table tab_invalid;

Table dropped.

SQL> select status from all_objects where lower(OBJECT_NAME) like '%syn_invalid%'

STATUS
-------
INVALID
Re: Regarding Invalid object [message #412651 is a reply to message #412638] Fri, 10 July 2009 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your error comes that your code is not formatted.

Regards
Michel
Re: Regarding Invalid object [message #412654 is a reply to message #412650] Fri, 10 July 2009 06:20 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member



Thank you all for your response.


I tried the same steps given by anand and then compiled ,
it says object does not exist.

For your reference


SQL> SELECT 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||
'COMPILE BODY;' FROM DBA
_OBJECTS
2 WHERE OWNER IN ('SYS')
3 AND STATUS = 'INVALID'
4 AND OBJECT_TYPE ='SYNONYM';

'ALTERPACKAGE'||OWNER||'.'||OBJECT_NAME||'COMPILEBODY;'
--------------------------------------------------------------------------------

ALTER PACKAGE SYS.SYN_INVALID COMPILE BODY;

SQL>
SQL>
SQL> ALTER PACKAGE SYS.SYN_INVALID COMPILE BODY;
ALTER PACKAGE SYS.SYN_INVALID COMPILE BODY
*
ERROR at line 1:
ORA-04043: object SYN_INVALID does not exist


In this case how to compile and make the object valid.


Thanks and Regards,
Re: Regarding Invalid object [message #412656 is a reply to message #412654] Fri, 10 July 2009 06:35 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The SYS schema is special.

A lot of things in the SYS schema work differently than in other schemas. Don't create any objects in the SYS schema yourself.

And FORMAT YOUR CODE.
Previous Topic: How to identify Unicode Characters
Next Topic: Query for group by period
Goto Forum:
  


Current Time: Wed Dec 07 04:40:09 CST 2016

Total time taken to generate the page: 0.17416 seconds