Hello Guys, Xev here.
Hopefully I will meet some new people over there in this in this space. I hope this is the correct space to ask this, i see others have asked questions about sqlplus and they where allowed to post it...
I have a pl/sql script that executes find in sql developer 4, but the exact same script will not execute in command line sqlplus?
When I run the script, it gives me this, but as you can see with this script that works fine in sql developer, it doesn't work in command line sqlplus.
ERROR at line 3:
ORA-06550: line 3, column 28:
PLS-00201: identifier 'DYNAMIC_COUNTS' must be declared
ORA-06550: line 3, column 4:
PL/SQL: Item ignored
ORA-06550: line 39, column 17:
PLS-00487: Invalid reference to variable 'DYNAMIC_COUNTS%ROWTYPE'
ORA-06550: line 39, column 1:
PL/SQL: Statement ignored
ORA-06550: line 41, column 17:
PLS-00487: Invalid reference to variable 'DYNAMIC_COUNTS%ROWTYPE'
ORA-06550: line 41, column 1:
PL/SQL: Statement ignored
ORA-06550: line 43, column 17:
PLS-00487: Invalid reference to variable 'DYNAMIC_COUNTS%ROWTYPE'
ORA-06550: line 43, column 1:
PL/SQL: Statement ignored
ORA-06550: line 45, column 17:
PLS-00487: Invalid reference to variable 'DYNAMIC_COUNTS%ROWTYPE'
ORA-06550: line 45, column 1:
PL/SQL: Statement ignored
ORA-06550: line 82, column 15:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 82, column 3:
PL/SQL: SQL Statement ignored
Here is the script I am using.....Do you have to set some type of thing in command line sqlplus?
set serveroutput on size unlimited
execute DBMS_OUTPUT.ENABLE (buffer_size => NULL);
DECLARE
TYPE nt_tab IS TABLE OF dynamic_counts%ROWTYPE;
v_nttab nt_tab := nt_tab();
FND_LOOK INTEGER;
v_incr NUMBER := 0;
------------------------------------------------------------------------------------------------------------------------
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 1 Alpha and 12 Digits---
',''^([[:alpha:]]|[[:alpha:]]{3})[0-9]{12}$'')
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_LOOK ;
------------------------------------------------------------------------------------------------------------------------
IF FND_LOOK > 0 THEN
v_incr := v_incr +1;
v_nttab.EXTEND;
v_nttab(v_incr).t_owner := t.owner;
v_nttab(v_incr).t_table_name := t.table_name;
v_nttab(v_incr).t_column_name := t.column_name;
v_nttab(v_incr).fnd_govids := FND_LOOK;
END IF;
------------------------------------------------------------------------------------------------------------------------
/*This is for the DBMS Output so we have 2 differnt ways we can see the results, in the dynamic_counts table
and in the dbms_output
*/
IF FND_LOOK > 0 THEN
DBMS_OUTPUT.put_line (t.owner || '.' || t.table_name || ' ' || t.column_name || ' ' || FND_LOOK);
END IF;
------------------------------------------------------------------------------------------------------------------------
/* Exception Handeling */
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Generic Error '
|| t.column_name
|| ' from '
|| t.owner
|| '.'
|| t.table_name);
END;
END LOOP;
------------------------------------------------------------------------------------------------------------------------
FORALL i IN 1..v_nttab.COUNT
INSERT INTO dynamic_counts(t_owner,
t_table_name,
t_column_name,
fnd_govids)
VALUES (v_nttab(i).t_owner,
v_nttab(i).t_table_name,
v_nttab(i).t_column_name,
v_nttab(i).fnd_govids);
COMMIT;
END;
/