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

How many aggregate functions are available there in sql?

927

---

How to sort the rows in sql.

1002

---

What is dialect in sql?

887

---

Mention what pl/sql package consists of?

935

---

Explain raise_application_error.

1039

---

What is difference between db2 and sql?

935

---

What are basic techniques of indexing?

1317

---

What is cold data?

895

---

What is nosql vs sql?

1119

---

What is a boolean in sql?

943

---

What is the clause we need to add in function body to return variable?

965

---

What is sql mysql pl sql oracle?

1025

---

If a cursor is open, how can we find in a pl/sql block?

1103

---

Explain what is a subquery ?

1118

---

What is record type in pl sql?

928

---