Hi,
I want to execute and store results of a dynamic sql query in a strored procedure.
I will get the the following variables from user at run time : v_column_names, v_table_name, v_col , v_value
The query will be like :
v_query := 'SELECT '||v_column_names||' FROM '||v_table_name||' WHERE '||v_col||' = '||v_value;
e.g. v_column_names := 'ACCOUNT_NUM, SYSTEM_ID, ENTITY_ID';
v_table_name := 'ACCOUNT';
v_col := 'COUNTRY_CODE';
v_value := 'USA';
Here is what i tried
DECLARE
v_column_names VARCHAR(200) := 'ENTITY_ID, SYSTEM_ID, ACCOUNT_NUM';
v_table_name VARCHAR(200) := 'ACCOUNT';
v_col VARCHAR(200) := 'COUNTRY_CODE';
v_value VARCHAR(200) := '''USA''';
TYPE column_record IS RECORD (
c1 VARCHAR2(200) := null,
c2 VARCHAR2(200) := null,
c3 VARCHAR2(200) := null
);
TYPE st_table IS TABLE OF column_record INDEX BY BINARY_INTEGER;
pk_table st_table;
v1 NUMBER := 1;
BEGIN
v_select := 'SELECT '||v_column_names||' FROM '||v_table_name||' WHERE '||v_col||' = '||v_value;
EXECUTE IMMEDIATE v_select BULK COLLECT INTO pk_table;
FOR i IN 1..pk_table.count LOOP
DBMS_OUTPUT.PUT_LINE(pk_table(v1).c1);
DBMS_OUTPUT.PUT_LINE(pk_table(v1).c2);
DBMS_OUTPUT.PUT_LINE(pk_table(v1).c3);
v1 := v1 +1;
END LOOP;
END;
The number of column names passed in v_column_names is dynamic, so i need a way to store the results, and then manipulate them.
I look forward to your response.
EDIT: The real question :
1. In parameters i pass it the table name and the value of the primary key of the row which i want to delete.
2. It then checks if that row is referenced anywhere, deletes the reference and then deletes the current row. This can go on, until all referenced rows are deleted.
The above code i have supplied it a small part of the larger stored procedure.