Answer Posted / 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;
/

Is sqlite thread safe?

993

---

What is information schema in sql?

1017

---

Is delete faster than truncate?

960

---

Do we need commit after truncate?

1034

---

explain mysql aggregate functions. : Sql dba

942

---

Does sql require a server?

945

---

Can we join tables without foreign key?

909

---

What are secondary keys?

923

---

What is pl sql quora?

1073

---

what does it mean to have quoted_identifier on? What are the implications of having it off? : Sql dba

947

---

What are the popular database management systems in the it industry?

930

---

What are the benefits of triggers?

1122

---

When can we use the where clause and the having clause?

969

---

Which command is used to delete a package?

1093

---

How is indexing done in search engines?

930

---