Query to find Primary and Foreign keys

[pradeep kaltari]

Hi,
I have another doubt.
How to create user for oracle and assign only some previlliges. For example only to insert recort into the table.

thanks,
Srinivasan r

Hi,
Follow the link: http://www.techonthenet.com/oracle/grant_revoke.php

Cheers,
Pradeep

[rsrinivasan]

Hi,
I create a function in oracle

create or replace function 
divide(first integer, second integer) return integer is 
begin 
  if first>second 
      return first 
  else 
      return second 
end;

But it tells warning as below..

Warning: Function created with compilation errors.

What is the error? Give correct syntex...

Thanks,
Srinivasan r

[debasisdas]

check the code

create or replace function divide(first integer, second integer) return integer is
begin
if first > second then return first;
else return second;
end if;
end;

[cpiyush]

Hi,
I create a function in oracle

create or replace function 
divide(first integer, second integer) return integer is 
begin 
  if first>second 
      return first 
  else 
      return second 
end;

But it tells warning as below..

Warning: Function created with compilation errors.

What is the error? Give correct syntex...

Thanks,
Srinivasan r

Execute this query if you are getting compilation errors:-
show errors;
This will list the line number/col number combination & the description of error which you have made in the function definition.

Check your mistakes using this, as I can see one mistake just by reviewing ur code that you have not specifieds the type(IN/OUT) of the arguments passed in the prototype...

Cheer!!!
cPiyush.

[debasisdas]

You had made some basic mistakes

1. Forgotten to add semicolons.
2. Forgotten to add End if.

[debasisdas]

It is not compulsary to specify type.

If nothing is specified default type Is IN .

Not specifying the parameter MODE is not an error.

[cpiyush]

It is not compulsary to specify type.

If nothing is specified default type Is IN .

Not specifying the parameter MODE is not an error.

Oh...Thanks Das...:-)

[Pumuky]

Hi,
The following query will give all the constraints specified on a table belonging to <owner_name> schema.

SELECT * 
FROM SYS.ALL_CONSTRAINTS A , SYS.ALL_CONS_COLUMNS B
WHERE S.OWNER=<owner_name> AND S.TABLE_NAME=<table_name> AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME

You can further filter this based upon the constraint type you are interested in. For primary key constraint, include CONSTRAINT_TYPE ='P' in the WHERE clause. For foreign key constraint, include CONSTRAINT_TYPE ='R' in the WHERE clause.

Hi all,
the disscusion is very interesting... I would need to know the way to get the FK between two tables, could you help me?
Thank you in advance,
Pumuky

[thelastfrontier]

find foreign key

Here a query which delivers me the TABLE_NAME,COLU MN_NAME to which a foreign key <table>.<column > is pointing (ORACLE 10).
I use the USER system VIEW to get it:

select cc.TABLE_NAME, cc.COLUMN_NAME from USER_CONS_COLUM NS cc WHERE cc.CONSTRAINT_N AME = ( select c.R_CONSTRAINT_ NAME from USER_CONS_COLUM NS cc join USER_CONSTRAINT S c on cc.CONSTRAINT_N AME= c.CONSTRAINT_NA ME where c.TABLE_NAME = '<table>' and cc.COLUMN_NAME = '<column>' and C.CONSTRAINT_TY PE='R')

Maybe someone knows a shorter query but it works fine for me.

.

[thelastfrontier]

all foreign keys from a table

again a USER query for ORACLE 10: all columns (query result 'ID') in a <table> that represent foreign keys and point to 'TABLE'.'COLUMN ':

select
ccc.COLUMN_NAME as "ID", ucc.TABLE_NAME as "TABLE", ucc.COLUMN_NAME as "COLUMN"
from
USER_CONS_COLUM NS ucc,
(
select
cc.COLUMN_NAME, c.R_CONSTRAINT_ NAME
from
USER_CONS_COLUM NS cc
inner join
USER_CONSTRAINT S c
on
cc.CONSTRAINT_N AME = c.CONSTRAINT_NA ME
where
cc.TABLE_NAME=' <table>'
and
c.CONSTRAINT_TY PE='R'
) ccc
where
ucc.CONSTRAINT_ NAME = ccc.R_CONSTRAIN T_NAME

[Madhusmita Biswal]

in order to know the primary key column name and forign key column in a table just execute the query :=

SELECT * FROM ALL_CONS_COLUMN S A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NA ME = C.CONSTRAINT_NA ME
WHERE C.TABLE_NAME = <table_name>
AND C.CONSTRAINT_TY PE in ('P','R')
and a.owner=<owner_ name>
and c.owner=<owner_ name>

[karanshah89]

Thanks, that was really useful