Answer / swapna

CREATE OR REPLACE FUNCTION cons_dependants (tname IN
VARCHAR2, cvalue IN NUMBER)
RETURN NUMBER
IS

CURSOR depen_cur
IS
SELECT c1.table_name table_name, c2.column_name
column_name
FROM user_constraints c1, user_cons_columns c2,
user_constraints c
WHERE c1.constraint_name = c2.constraint_name
AND c1.r_constraint_name = c.constraint_name
AND c.table_name = UPPER (tname);

tbl_cnt NUMBER;
sql_cur NUMBER;
qry_stmt VARCHAR2 (1000);
v_tbl_cnt NUMBER;
BEGIN
FOR i IN depen_cur
LOOP
/*PREPARE THE GENERAL QUERY STATEMENT TO FIND OUT THE DATA
EXISTANCE IN DEPENDANT TABLES*/
dbms_output.put_line(i.table_name);
qry_stmt := 'select count(*) from '
|| i.table_name
|| ' where '
|| i.column_name
|| ' = '
|| ':c_bind_value'
|| ' and '
|| 'record_status ='
|| ' '
|| '''N''';
DBMS_OUTPUT.put_line (qry_stmt);
sql_cur := DBMS_SQL.open_cursor;
DBMS_SQL.parse (sql_cur, qry_stmt, DBMS_SQL.native);
DBMS_SQL.bind_variable (sql_cur, ':c_bind_value',
cvalue);
DBMS_SQL.define_column (sql_cur, 1, v_tbl_cnt);
tbl_cnt := DBMS_SQL.EXECUTE (sql_cur);

IF DBMS_SQL.fetch_rows (sql_cur) > 0
THEN
DBMS_SQL.column_value (sql_cur, 1, v_tbl_cnt);
DBMS_OUTPUT.put_line (v_tbl_cnt||' number of
rows');

IF v_tbl_cnt > 0
THEN
DBMS_SQL.close_cursor (sql_cur);
RETURN 1;
ELSIF v_tbl_cnt = 0
THEN
DBMS_SQL.close_cursor (sql_cur);
END IF;
END IF;
END LOOP;
RETURN -1; /*CAN DELETE THE RECORDS*/
END cons_dependants;
/

What is the difference between partition and index?

0 Answers  

---

What is sql and how does it work?

0 Answers  

---

How do you create a db file?

0 Answers  

---

Do we need commit after truncate?

0 Answers  

---

Can we create table inside stored procedure?

0 Answers  

---

Can you selectively load only those records that you need? : aql loader

0 Answers  

---

What is the full form of sql?

1 Answers  

---

function can return value ,procedure also return value through out parameter then what is the difference?

3 Answers   3i Infotech,

---

Types of joins ?

11 Answers   Polaris, TCS,

---

How many databases can sql express handle?

0 Answers  

---

Which command is used to delete a package?

0 Answers  

---

What is the difference between function and procedure in pl/sql?

0 Answers  

---