What's the best way to loop through column names in Oracle PL/SQL?

Data and Databases
1

I’m working on a project where I need to go through column names in Oracle PL/SQL for a dynamic query. But I’m running into issues when I try to loop through these names. I keep getting an error that says something about a PL/SQL compilation problem.

Here’s what I’ve tried so far:

DECLARE
  col_cursor CURSOR IS
    SELECT col_name FROM system_columns WHERE tbl='my_table';
BEGIN
  FOR col_record IN col_cursor LOOP
    dbms_output.put_line(col_record.col_name);
    -- Some dynamic SELECT stuff here
    query_str := query_str || ',' || col_record.col_name;
    dbms_output.put_line(query_str);
  END LOOP;
END;

What I want to do is count how many rows I get each time I add a new field to my SQL query and save that number. Any ideas on how to fix this or a better way to do it? Thanks!

2

hey maya! have u thought about using DBMS_SQL package? it’s great for dynamic queries! u can build ur query string, parse it, and execute it all in one go. plus, u can easily get the row count with DBMS_SQL.EXECUTE_AND_FETCH. wanna explore that option together?

3

hey maya, try using a sys_refcursor instead of a regular cursor. it’s more flexible for dynamic queries. also, make sure ur system_columns table exists and has the right perms. for counting rows, u could use SQL%ROWCOUNT after executing the dynamic query. hope this helps!

4

I’ve encountered similar challenges with dynamic queries in Oracle PL/SQL. One effective approach is to use the ALL_TAB_COLUMNS view instead of a custom table. This view provides information about all columns in the database, including their names and data types. Here’s a modified version of your code that might work better:

DECLARE
v_query VARCHAR2(4000) := 'SELECT ‘;
v_count NUMBER;
BEGIN
FOR col IN (SELECT column_name FROM ALL_TAB_COLUMNS WHERE table_name = ‘MY_TABLE’) LOOP
v_query := v_query || col.column_name || ‘,’;
END LOOP;
v_query := RTRIM(v_query, ‘,’) || ’ FROM MY_TABLE’;
EXECUTE IMMEDIATE v_query INTO v_count;
DBMS_OUTPUT.PUT_LINE('Row count: ’ || v_count);
END;

This approach should be more reliable and easier to maintain. It dynamically builds the query string and executes it, storing the result in v_count.