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 difference between procedure and function

10 Answers   MCN Solutions, Wipro,

---

Explain constraints in sql?

0 Answers  

---

i have a word ***********hello********world******. I require a o/p **********hello world**********, Need to delete the middle stars.

3 Answers  

---

Explain about various levels of constraint.

0 Answers  

---

What are tuples in sql?

0 Answers  

---

Why is pl sql used?

0 Answers  

---

What pl/sql package consists of?

0 Answers  

---

wirte a query to remove null? following table are col1 col2 col3 dinesh null null null suresh null null null prakesh i want the output like col1 col2 col3 dinesh suresh prkaesh

6 Answers   TCS,

---

how to use regular expression in pattern match conditions? : Sql dba

0 Answers  

---

Table Student has 3 columns,Student_id,Student_Name & Course_Id. Table Course has 2 columns, Course_Id & Course_Name.Write a query to listdown all the Courses and number of student in each course.

10 Answers   TCS,

---

How can we store rows in PL/SQL using array?

0 Answers   MCN Solutions,

---

What is normalization? How many normalization forms are there?

0 Answers  

---