1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Create SQL Statement for Foreign Key for an owner schema

CREATE OR REPLACE PROCEDURE foreign_key_text (
cons_name IN VARCHAR2,
cons_owner IN VARCHAR2
)
AS
r_columns VARCHAR2 (2000);
p_columns VARCHAR2 (2000);
p_constraint VARCHAR2 (2000);
p_table VARCHAR2 (50);
r_table VARCHAR2 (50);
r_owner VARCHAR2 (50);
p_owner VARCHAR2 (50);
r_dummy NUMBER;

CURSOR r_cur
IS
SELECT column_name
FROM dba_cons_columns
WHERE constraint_name = UPPER (cons_name)
AND owner = UPPER (cons_owner);

tr_cur VARCHAR2 (50);

CURSOR p_cur
IS
SELECT column_name
FROM dba_cons_columns
WHERE constraint_name =
(SELECT DISTINCT r_constraint_name
FROM dba_cons_columns a, dba_constraints b
WHERE a.constraint_name = b.constraint_name
AND a.constraint_name = UPPER (cons_name)
AND a.owner = UPPER (cons_owner));

pr_cur VARCHAR2 (50);
BEGIN
r_columns := '';
p_columns := '';

SELECT COUNT (1)
INTO r_dummy
FROM dba_cons_columns
WHERE constraint_name = UPPER (cons_name) AND owner = UPPER (cons_owner);

OPEN r_cur;

LOOP
FETCH r_cur
INTO tr_cur;

IF r_cur%NOTFOUND
THEN
EXIT;
END IF;

r_columns := r_columns || tr_cur || ',';
END LOOP;

r_columns := SUBSTR (r_columns, 1, LENGTH (r_columns) - 1);
DBMS_OUTPUT.put_line ('Foreign key columns:' || r_columns);

OPEN p_cur;

LOOP
FETCH p_cur
INTO pr_cur;

IF p_cur%NOTFOUND
THEN
EXIT;
END IF;

p_columns := p_columns || pr_cur || ',';
END LOOP;

p_columns := SUBSTR (p_columns, 1, LENGTH (p_columns) - 1);
DBMS_OUTPUT.put_line ('Primary key columns:' || p_columns);

SELECT table_name, owner
INTO r_table, r_owner
FROM dba_constraints
WHERE constraint_name = UPPER (cons_name) AND owner = UPPER (cons_owner);

SELECT table_name, owner
INTO p_table, p_owner
FROM dba_constraints
WHERE constraint_name =
(SELECT DISTINCT a.r_constraint_name
FROM dba_constraints a
WHERE a.constraint_name = UPPER (cons_name)
AND a.owner = UPPER (cons_owner));

p_constraint :=
'alter table '
|| r_owner
|| '.'
|| r_table
|| ' add constraint '
|| cons_name
|| ' foreign key ('
|| r_columns
|| ') references '
|| p_owner
|| '.'
|| p_table
|| '('
|| p_columns
|| ')'
|| ';';
DBMS_OUTPUT.put_line
('-----------------------------------------------------------------------------'
);
DBMS_OUTPUT.put_line (p_constraint);
DBMS_OUTPUT.put_line
('-----------------------------------------------------------------------------'
);

CLOSE r_cur;

CLOSE p_cur;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line
('-----------------------------------------------------------------------------'
);
DBMS_OUTPUT.put_line ( 'No referential constraint by the name '
|| cons_name
|| ' for owner '
|| cons_owner
);
WHEN OTHERS
THEN
CLOSE r_cur;

CLOSE p_cur;
END;
/
SQL > exec foreign_key_text('fk_table2','oracle9i')
Foreign key columns:X,Y
Primary key columns:A,B
-----------------------------------------------------------------------------
alter table ORACLE9I.TABLE2 add constraint fk_table2 foreign key (X,Y) references ORACLE9I.TABLE1(A,B);
-----------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL > exec foreign_key_text('NOEXISTENT_CONSTRAINT','ORACLE9I')
Foreign key columns:
Primary key columns:
-----------------------------------------------------------------------------
No referential constraint by the name NOEXISTENT_CONSTRAINT for owner ORACLE9I

PL/SQL procedure successfully completed.

SQL >

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy