Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

URGENT !!!! Dynamic SQL in Functions and Triggers

drovnikdrovnik Member Posts: 31
/*
The function below is supposed to return
a set of columns that identify the primary
key of a table.
*/

CREATE OR REPLACE FUNCTION fn_Audit_Record ( p_Table_Name VARCHAR2 )
RETURN VARCHAR2
IS
--
v_Audit_Record User_Cons_Columns.Column_Name%TYPE;
v_cc NUMBER;
--
CURSOR c_Primary_Key IS
SELECT COUNT(Ucco.Column_Name) INTO v_cc
FROM User_Constraints Usco
,User_Cons_Columns Ucco
,User_Tab_Columns Utco
WHERE Usco.Table_Name = p_Table_Name
AND Usco.CONSTRAINT_TYPE = 'P'
AND Ucco.Constraint_Name = Usco.Constraint_Name
AND Usco.Table_Name = Ucco.Table_Name
ORDER BY Ucco.Position ;
--
BEGIN
--
OPEN c_Primary_Key;
LOOP
FETCH c_Primary_Key INTO v_Audit_Record;
EXIT WHEN c_Primary_Key%NOTFOUND ;
END LOOP;
CLOSE c_Primary_Key;
--
RETURN v_Audit_Record;
--
END fn_Audit_Record;

/*
How do i use the values retuned
to select the key in the table
by calling the function inside a trigger
/*

Comments

  • infidelinfidel Member Posts: 2,900
    : /*
    : The function below is supposed to return
    : a set of columns that identify the primary
    : key of a table.
    : */
    :
    : CREATE OR REPLACE FUNCTION fn_Audit_Record ( p_Table_Name VARCHAR2 )
    : RETURN VARCHAR2
    : IS
    : --
    : v_Audit_Record User_Cons_Columns.Column_Name%TYPE;
    : v_cc NUMBER;
    : --
    : CURSOR c_Primary_Key IS
    : SELECT COUNT(Ucco.Column_Name) INTO v_cc
    : FROM User_Constraints Usco
    : ,User_Cons_Columns Ucco
    : ,User_Tab_Columns Utco
    : WHERE Usco.Table_Name = p_Table_Name
    : AND Usco.CONSTRAINT_TYPE = 'P'
    : AND Ucco.Constraint_Name = Usco.Constraint_Name
    : AND Usco.Table_Name = Ucco.Table_Name
    : ORDER BY Ucco.Position ;
    : --
    : BEGIN
    : --
    : OPEN c_Primary_Key;
    : LOOP
    : FETCH c_Primary_Key INTO v_Audit_Record;
    : EXIT WHEN c_Primary_Key%NOTFOUND ;
    : END LOOP;
    : CLOSE c_Primary_Key;
    : --
    : RETURN v_Audit_Record;
    : --
    : END fn_Audit_Record;
    :
    : /*
    : How do i use the values retuned
    : to select the key in the table
    : by calling the function inside a trigger
    : /*

    I'm not sure what you're trying to do. You can't return a record type from a function declared as returning a varchar2.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.