I’m a beginner in SQL.
I’m using DBeaver v 24.1.3 on a mac to access an oracle database.
I’m using code to generate a dynamic query that calculates the percent of non-null rows for all columns in a table. The query creates a table with the column name and the percent non-null values. I’d like to generate the query results and save to file. I’ll be doing this for multiple tables.
I can use DBMS_OUTPUT.PUT_LINE(sql_query); to print the query.
I’m then using
EXECUTE IMMEDIATE sql_query; to run the query. I’m doing this within a BEGIN / END; structure.
The code runs but no output table is generated.
If I copy and past the printed query in the SQL editor and run I do get the output table.
How can I generate the output table with a dynamic query?
Thanks very much in advance.
Full code below.
DECLARE
column_name VARCHAR2(255);
sql_query VARCHAR2(32767) := 'WITH column_non_nulls AS (';
dynamic_select VARCHAR2(1000);
total_count NUMBER;
first_column BOOLEAN := TRUE;
BEGIN
-- Get the total number of rows in the table
SELECT COUNT(*) INTO total_count FROM GENERIC.TABLENAME;
-- Loop through each column in the table and dynamically construct the query
FOR col IN (
SELECT column_name
FROM all_tab_columns
WHERE table_name = 'TABLENAME'
AND owner = 'GENERIC'
)
LOOP
column_name := col.column_name;
-- Construct the dynamic SELECT part for each column
dynamic_select := 'SELECT ''' || column_name || ''' AS column_name, ' ||
'ROUND(100 * COUNT(' || column_name || ') / ' || total_count || ', 2) AS percent_non_null ' ||
'FROM GMD.STUDIES WHERE ' || column_name || ' IS NOT NULL';
-- Append the dynamic SELECT part to the SQL query
IF first_column THEN
sql_query := sql_query || dynamic_select;
first_column := FALSE;
ELSE
sql_query := sql_query || ' UNION ALL ' || dynamic_select;
END IF;
END LOOP;
-- Close the CTE and the main SELECT query
sql_query := sql_query || ') SELECT * FROM column_non_nulls ORDER BY percent_non_null DESC';
-- Print the dynamically constructed SQL query for debugging purposes
DBMS_OUTPUT.PUT_LINE(sql_query);
-- Execute the dynamically generated SQL query
EXECUTE IMMEDIATE sql_query;
END;
3
Try exporting the dynamically generated query to a file, and then you can execute the script separately in DBeaver. Modify the PL/SQL block to write the generated query to a file (using UTL_FILE) or print it for you to copy
BEGIN
-- Your PL/SQL block
DBMS_OUTPUT.PUT_LINE(sql_query);
END;
Run the printed query in DBeaver to export the results. This method won’t automate everything fully, but it ensures you can handle the output correctly by executing the SQL query in a standard SQL context where DBeaver can generate the results.
OR
If you want to store the results of your dynamic query, you can insert them into a temporary or permanent table.
CREATE TABLE column_non_null_percentages (
column_name VARCHAR2(255),
percent_non_null NUMBER
);
And modify the code to insert the dynamic query results into this table instead of trying to execute the query directly
from: 'FROM GMD.STUDIES WHERE ' || column_name || ' IS NOT NULL';
to: 'FROM GENERIC.TABLENAME WHERE ' || column_name || ' IS NOT NULL';
from: EXECUTE IMMEDIATE sql_query;
to: EXECUTE IMMEDIATE 'INSERT INTO column_non_null_percentages ' || sql_query;