The execute immediately only supports return values of type char, varchar2 or clob.
The STANDARD_HASH functions that is used in the dynamic query returns RAW.
Can you think of a way I can CAST the RAW data type returned by STANDARD_HASH to either varchar2 or clob code does not throw a ORA:00902 Invalid datatype error thrown by the execute immediate statement
The codes design is to generate HASH values for a row of data in a table.
Please note: I have simplified the code for ease of understanding, the reason for having the dynamic query is because owner and table name in QUERY_STR are generated dynamically at runtime via Oracle data dictionary calls using Oracle metadata package hence why query needs to be dynamic.
What I would like to understand is how can I structure code to CAST dynamic query so execute immediate does not throw ORA:00902 Invalid datatype error.
I appreciate it is not a good idea to CAST STANDARD_HASH results but I don't know how to return HASH value via execute immediate statement if I don't CAST to varchar2 or clob data type.
-- Generated HASH Value of all columns and store in varray variable
declare
cursor CUR_TABLE is
select OWNER, TABLE_NAME
from ALL_TABLES
where OWNER in (
'your schema' -- enter your schema name here
-- this will be a list of schemas however to simplify the code I have only included one for the time being
)
order by OWNER, TABLE_NAME;
QUERY\_STR varchar2(4000);
i integer :=0;
type HASH\_VALUE\_TYPE is varray(1000000)
of RAW not null;
HASH\_VALUE HASH\_VALUE\_TYPE := HASH\_VALUE\_TYPE ();
begin
for IN_REC in CUR_TABLE
loop
QUERY_STR :=
'select STANDARD_HASH('
|| col_1
|| '||'
|| col_2
|| '||'
|| col_3 -- and so on....
', ''SHA256'') from '
|| OWNER -- This will be performed for many schemas & tables which will be generated via Oracle metadata calls. I have simplified code for easy of understanding
|| '.'
|| TABLE_NAME
;
execute immediate QUERY_STR bulk collect into HASH_VALUE; -- execute immediate only supports return types of char. varchar2 or clob
-- code throws an error because STRANDARD_HASH return type is RAW
-- is there anyway I can CAST return type for dynamic query string QUERY_STR to varch2 or clob?
end loop;
end;