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

What is difference between sql and excel?

1169

---

What is a primary key? Explain

1020

---

Explain correlated query work?

1098

---

How do you sort in sql?

1062

---

Is the primary key an index?

1073

---

Is ms sql is free?

1077

---

What is the maximum size of sqlite database?

1147

---

What is scope and visibility in PL/SQL?

1281

---

Can instead of triggers be used to fire once for each statement on a view?

1004

---

what is the stuff function and how does it differ from the replace function? : Sql dba

1102

---

How can we implement rollback or commit statement in a trigger?

1078

---

explain the advantages and disadvantages of stored procedure? : Sql dba

1059

---

what is a trigger? : Sql dba

1084

---

what is the use of friend function? : Sql dba

1055

---

Define sql delete statement.

1061

---