E ovako, uspio sam napraviti da mi pravi matricu za moju funkciju, posto je funkcija veoma jednostavna. I to mi sad šljaka kako treba, međutim hoću da napravim da mi bude univerzalna, tj da mi prima funkciju kao parametar i da ju obradjuje. Kod ovoga što sam uradio ide ispod:
Code:
create or replace procedure test_
IS
v_string_fnc varchar2(10000) := UPPER('function test_funkcija_nebosja
Return varchar2
IS
kpp_value varchar2(20);
begin
select KPP into kpp_value from CUSTOMER where CUSTOMER_ID = 200713;
dbms_output.put_line (kpp_value);
Return kpp_value;
end test_funkcija_nebosja;');
v_check PLS_INTEGER;
CURSOR c_text is
SELECT USER_SOURCE.TEXT
FROM USER_SOURCE
WHERE USER_SOURCE.name = 'FUNCTION_NAME'
AND USER_SOURCE.type = 'FUNCTION'
order by line;
v_single_text varchar2(4000);
v_tmp_text varchar2(10000) := ' ';
/*v_string varchar2(10000);*/
insert_flag char := '-';
read_flag char := '-';
update_flag char := '-';
delete_flag char := '-';
underline char(42) := '==========================================';
/*v_txt varchar2(10000) := ' ';*/
result_table varchar2(1000) := '/';
begin
/*execute immediate 'create table crud_table_matrix(tables_used varchar2(20), operations_used varchar2(20))';
insert into crud_table_matrix(tables_used, operations_used) values ('empty', insert_char || read_char || update_char || delete_char); */
open c_text;
loop
fetch c_text
into v_single_text;
exit when c_text%notfound;
v_tmp_text := v_tmp_text || chr(10) || rtrim(v_single_text);
/* print source code*/
/*dbms_output.put_line(v_single_text);*/
end loop;
close c_text;
dbms_output.put_line(' ');
dbms_output.put_line('==========' || 'TABLE_NAME' || '==========' ||
'OPERATIONS' || '==');
dbms_output.put_line(' ');
/*DELETE SEARCH*/
v_check := instr(v_string_fnc, 'DELETE ');
if v_check > 0 then
/*dbms_output.put_line('THERE IS NO DELETE COMMAND');*/
/*else
/*dbms_output.put_line('THERE IS A DELETE COMMAND');*/
delete_flag := 'D';
v_check := instr(v_string_fnc, 'FROM ');
v_check := v_check + 5;
result_table := substr(v_string_fnc, v_check);
result_table := substr(result_table, 0, instr(result_table, ' '));
dbms_output.put_line(' ' || result_table || ' ' ||
insert_flag || read_flag || update_flag ||
delete_flag);
end if;
/*SELECT SEARCH*/
v_check := instr(v_string_fnc, 'SELECT ');
if v_check > 0 then
/*dbms_output.put_line('THERE IS NO READ COMMAND');*/
/*else
/*dbms_output.put_line('THERE IS A READ COMMAND');*/
read_flag := 'R';
v_check := instr(v_string_fnc, 'FROM ');
v_check := v_check + 5;
result_table := substr(v_string_fnc, v_check);
result_table := substr(result_table, 0, instr(result_table, ' '));
dbms_output.put_line(' ' || result_table || ' ' ||
insert_flag || read_flag || update_flag ||
delete_flag);
end if;
/*UPDATE SEARCH*/
v_check := instr(v_string_fnc, 'UPDATE ');
if v_check > 0 then
/*dbms_output.put_line('THERE IS NO UPDATE COMMAND');*/
/*else
/*dbms_output.put_line('THERE IS A UPDATE COMMAND');*/
update_flag := 'U';
v_check := instr(v_string_fnc, 'FROM ');
v_check := v_check + 5;
result_table := substr(v_string_fnc, v_check);
result_table := substr(result_table, 0, instr(result_table, ' '));
dbms_output.put_line(' ' || result_table || ' ' ||
insert_flag || read_flag || update_flag ||
delete_flag);
end if;
/*INSERT SEARCH*/
v_check := instr(v_string_fnc, 'INSERT ');
if v_check > 0 then
/*dbms_output.put_line('THERE IS NO CREATE COMMAND');*/
/*else
/*dbms_output.put_line('THERE IS A CREATE COMMAND');*/
insert_flag := 'C';
v_check := instr(v_string_fnc, 'FROM ');
v_check := v_check + 5;
result_table := substr(v_string_fnc, v_check);
result_table := substr(result_table, 0, instr(result_table, ' '));
dbms_output.put_line(' ' || result_table || ' ' ||
insert_flag || read_flag || update_flag ||
delete_flag);
end if;
/*dbms_output.put_line(' ');
dbms_output.put_line('==========' || 'TABLE_NAME' || '==========' ||
'OPERATIONS' || '==');*/
/*dbms_output.put_line(empty_space || insert_flag || read_flag ||
update_flag || delete_flag);*/
dbms_output.put_line(' ');
dbms_output.put_line(underline);
end test_;
E sad, kako da strpam output koji mi ispisuje text source cod funkcije u promjenljivu, da ne moram deklarisati u zaglavlju promjenljivu. I savjeti za dalje vezanje operacije tipa SELECT, UPDATE sa tabelom nad kojom se operacija vrši?
Svi savjeti su totalno dobrodošli :)