Home » SQL & PL/SQL » SQL & PL/SQL » Need Explanation & Problem in procedure (merged)
Need Explanation & Problem in procedure (merged) [message #390314] Thu, 05 March 2009 22:27 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,

My PM told me to write down the procedure that retrives follwoing ,

Which is should pick-up the column names of PK and UK and constraints enabled, search for the same column names in the other table check whether the FK is done if not then print table name and column name for the same.

Can you tell me what exactly I have to display
Re: Need Explanation [message #390315 is a reply to message #390314] Thu, 05 March 2009 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
SELECT VIEW_NAME FROM ALL_VIEWS WHERE VIEW_NAME LIKE 'ALL%';

The results from above will give you a list that contains your answer(s).
Re: Need Explanation [message #390334 is a reply to message #390314] Fri, 06 March 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from dict where lower(comments) like '%constraint%';

Regards
Michel
Problem in procedure [message #390376 is a reply to message #390314] Fri, 06 March 2009 03:05 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi
This is my procedure it works fine if i passed the column name value.But it is not work fine for all column names.
Please provide me some guide line.

CREATE OR REPLACE PROCEDURE sp_find_fk_tbl_nm
(p_col_name Varchar2)
AS

pv_out_msg VARCHAR2(1000);

lv_table_name user_cons_columns.table_name%type;
lv_column_name user_cons_columns.column_name%type;

TYPE tab_column_name IS TABLE OF user_cons_columns.column_name%TYPE;
tv_col_name tab_column_name;
tv_cg_col_name tab_column_name;

TYPE tab_table_name IS TABLE OF user_cons_columns.table_name%TYPE;
tv_table_name tab_table_name ;
tv_cg_table_name tab_table_name ;

tv_col_name1 tab_column_name;

BEGIN
/* For Retriving PK AND UK */
dbms_output.put_line('Start Time :'||to_char(sysdate,'HH:MI:SS'));

SELECT DISTINCT ucc.column_name BULK COLLECT
INTO tv_col_name
FROM user_cons_columns ucc ,user_constraints uc
WHERE ucc.constraint_name=uc.constraint_name
AND uc.constraint_type IN ('P' ,'U')
AND uc.table_name NOT LIKE 'TMP%'
AND ucc.column_name=p_col_name
ORDER BY ucc.column_name;

--For Checking FK with others Tables/
IF tv_col_name.count> 0 THEN

FOR i IN tv_col_name.first..tv_col_name.last LOOP
SELECT ucc.table_name, ucc.column_name BULK COLLECT
INTO tv_table_name,tv_col_name1
FROM user_cons_columns ucc ,user_constraints uc
WHERE ucc.constraint_name=uc.constraint_name
AND uc.constraint_type='R'
AND uc.status ='ENABLED'
AND uc.table_name NOT LIKE 'TMP%'
AND ucc.column_name =tv_col_name(i);
dbms_output.put_line('FK Table/Col Name : '||tv_table_name(i)||'/'||tv_col_name1(i) );
END LOOP;
END IF;


/* For Columns names starting with CG_ whether those columns are
having FK with company_generic Table.
*/
SELECT ucc.table_name,ucc.column_name BULK COLLECT
INTO tv_cg_table_name ,tv_cg_col_name
FROM user_cons_columns ucc ,user_constraints uc
WHERE ucc.constraint_name=uc.constraint_name
AND ucc.column_name like 'CG_%'
AND UCC. table_name NOT LIKE 'TMP%'
AND uc.status ='ENABLED'
AND uc.constraint_type='R';

FOR j in tv_cg_table_name.first..tv_cg_table_name.last LOOP
dbms_output.put_line('FK CG_Column Name : '||tv_cg_table_name(j)||'/'||tv_cg_col_name(j) );
END LOOP;




dbms_output.put_line('End Time :'||to_char(sysdate,'HH:MI:SS'));


EXCEPTION
WHEN OTHERS THEN
-- pv_out_msg := fn_errconcat('-20823', fn_errconcat(SQLCODE), NULL);
dbms_output.put_line(SQLCODE);
END;
Re: Problem in procedure [message #390378 is a reply to message #390376] Fri, 06 March 2009 03:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
'Not work fine' isn't a phrase that crops up in the Oracle Error messages list.

Would you please supply a little more detail about what the code is meant to do, what columns/type of columns it doesn't work with, and what the symptoms of it not working are.
Re: Problem in procedure [message #390384 is a reply to message #390378] Fri, 06 March 2009 03:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This query:
SELECT DISTINCT ucc.column_name BULK COLLECT 
 INTO tv_col_name 
 FROM user_cons_columns ucc ,user_constraints uc
 WHERE ucc.constraint_name=uc.constraint_name
 AND uc.constraint_type IN ('P' ,'U')
 AND uc.table_name NOT LIKE 'TMP%'
 AND ucc.column_name=p_col_name
 ORDER BY ucc.column_name;
just populates the table tv_col_name with one or more rows, all containing the value p_col_name

The next loop and query repeatedly populates a table with then names of all the tables that have a constraint that uses that column, and you output a single row from the table each time through the loop - this row being determined by how far you are through the first table.

What, exactly and in detail, were you trying to achieve?
Re: Problem in procedure [message #390400 is a reply to message #390376] Fri, 06 March 2009 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

WHEN OTHERS is just stupid.

Regards
Michel
Re: Problem in procedure [message #390407 is a reply to message #390376] Fri, 06 March 2009 04:15 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi ,
Thanks to all for giving me the reply .My main problem is
I have write a program that contains following all the information.
a) Which is should pick-up the column names of PK and UK and constraints enabled, search for the same column names in the other table check whether the FK is done if not then print table name and column name for the same.
Re: Problem in procedure [message #390416 is a reply to message #390407] Fri, 06 March 2009 04:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you only ever have single column primary and unique keys, or do some of your primary and unique keys have multiple columns in them?
Previous Topic: Insufficient Privilege
Next Topic: Merge with Both Update and Insert at the Same Time
Goto Forum:
  


Current Time: Mon Dec 05 09:18:26 CST 2016

Total time taken to generate the page: 0.06225 seconds