Hey Guys, it's Xev.
Please only pleasant people reply to this.
I have a PL/SQL Program that searches for strings and then at the end of it it prints out to DBMS_OUTPUT.put_line.
I have the owner, the table_name, the column name and the count, then it goes to DBMS_OUTPUT.put_line
What i want to do, is take the results of DBMS_OUTPUT.put_line and insert it into a table.
Here is the script I am talking about, as you can see it's simple, yet for me it works. I want to take the results of this and insert it into a table. How can i do that??
set serveroutput on size unlimited
execute DBMS_OUTPUT.ENABLE (buffer_size => NULL);
------------------------------------------------------------------------------------------------------------------------
DECLARE
FND_GOVIDS INTEGER;
------------------------------------------------------------------------------------------------------------------------
BEGIN
FOR t IN (SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE owner = upper('&SCHEMA_NAME'))
------------------------------------------------------------------------------------------------------------------------
LOOP
BEGIN
EXECUTE IMMEDIATE 'with a as ( select case when REGEXP_LIKE(' || t.column_name ||
--This searches for 8 Alpha Digits
',''^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$'')
then ''Match Found''
else ''No Match Found'' end as output from ' || t.owner || '.' || t.table_name || ')
select count(*) from a where a.output=''Match Found'' '
INTO FND_GOVIDS ;
IF FND_GOVIDS > 0 THEN
DBMS_OUTPUT.put_line (
t.owner
|| '.'
|| t.table_name
|| ' '
|| t.column_name
|| ' '
|| FND_GOVIDS);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Generic Error '
|| t.column_name
|| ' from '
|| t.owner
|| '.'
|| t.table_name);
END;
END LOOP;
END;
/