Home » SQL & PL/SQL » SQL & PL/SQL » Foreign Key and Primary Key Details...
Foreign Key and Primary Key Details... [message #390366] Fri, 06 March 2009 02:31 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have a query below which extract Foreign Key and Primary Key Details when i execute this i got error mentioned below.i cant fix it

Can anyone fix the Below.

Error: ORA-00933: SQL command not properly ended


SELECT DISTINCT
b.table_name Table_Name,
b.column_name Column_Name,
DECODE (a.constraint_type,'R', 'Referencial Integity(Foreign Key)','P','Primary
Key',' ') Constraint_Type,
a.constraint_name Constraint_Name,
a.r_constraint_name Reference_Constaint_Name,
b.POSITION pos,
c.table_name Reference_Table_Name,
c.column_name Reference_Column_Name,
b.POSITION pos1,
c.POSITION pos2
FROM all_constraints a, all_cons_columns b, all_cons_columns c
WHERE
a.constraint_name = b.constraint_name
AND a.constraint_type = 'R'
AND a.table_name = 'SE_T_PARENT_SUPPORT_MAP'
AND b.table_name = a.table_name
AND a.r_constraint_name = c.constraint_name(+)
AND b.POSITION(+) = c.POSITION
ORDER BY b.POSITION, c.POSITION
union 
select distinct
a.table_name Table_Name,
b.column_name Column_Name,
decode(constraint_type,'C','Check Constraint','P','Primary Key','U','Unique key'
,'V','With Check Option',' ') Constraint_Type,
a.constraint_name Constraint_Name,
' ' Reference_Constaint_Name,
' ' Pos,
' ' Reference_Table_Name,
' ' Reference_Column_Name,
' ' Pos1,
' ' Pos2
 from all_constraints a,all_cons_columns b
where
a.CONSTRAINT_NAME  = b.CONSTRAINT_NAME              AND
a.TABLE_NAME       = b.TABLE_NAME                               AND
a.table_name       = 'SE_T_PARENT_SUPPORT_MAP'  AND
a.constraint_type !='R'
order by a.constraint_name;




Re: Foreign Key and Primary Key Details... [message #390369 is a reply to message #390366] Fri, 06 March 2009 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL formater and copy and paste your sqlplus session.

And why is this in "Server Utilities"? Is this a "Datapump, Import, Export, SQL*Loader, TKPROF, asmcmd, nid, orapwd, etc." question? Does "SQL command not properly ended" mean this?

Regards
Michel

[Updated on: Fri, 06 March 2009 02:40]

Report message to a moderator

Re: Foreign Key and Primary Key Details... [message #390370 is a reply to message #390366] Fri, 06 March 2009 02:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In a union, you order the final resultset, not the individual queries.

[Updated on: Fri, 06 March 2009 02:46]

Report message to a moderator

Re: Foreign Key and Primary Key Details... [message #390392 is a reply to message #390366] Fri, 06 March 2009 03:48 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Thanks Frank i have removed the two order by clause from both the query but i want result set should be in ordered format.
Re: Foreign Key and Primary Key Details... [message #390395 is a reply to message #390392] Fri, 06 March 2009 03:52 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So, what search terms did you use in Google and while browsing the Oracle documentation?
Maybe we can help you improve your searching capabilities. It's an important skill for developers.
Previous Topic: Aggregate Functions Within Inline Views
Next Topic: PARTITIONS
Goto Forum:
  


Current Time: Thu Dec 08 04:05:51 CST 2016

Total time taken to generate the page: 0.09048 seconds