Dynamic Query in Oracle

Question

I am trying the below dynamic query where I get the column names , table names from one query and I need those values in a variable which I will make it as a global variable.

The column names can change ranging from 5 to 50 columns. I wrote the below query but its not working. giving the below message anonymous block completed MESSAGE :-905-ORA-00905: missing keyword

Issue is with the parse statement, DBMS_SQL.PARSE not sure who to proceed

            DECLARE  
    TYPE lc_pdh_cur IS REF CURSOR;
            ont_pdh  lc_pdh_cur;
            lv_sql_stmt VARCHAR2(3000);
            lv_value   VARCHAR2(1000);
            lv_view_name VARCHAR2(1000);
            lv_columns VARCHAR2(1000);
            lv_var_columns VARCHAR2(1000);
            LV_VAR_STMT VARCHAR2(1000);
            LV_COL_STMT  VARCHAR2(1000);
            v_cursor integer;
            v_dname  char(20);
    v_rows   integer;
        TYPE l_column_pair IS TABLE OF VARCHAR2(2400)  -- Associative array type
        INDEX BY VARCHAR2(200);            --  indexed by string
        l_pairs  l_column_pair;        -- Associative array variable
        CURSOR lc_agv_name
        is 
            SELECT database_column,decode (value_set_name,null,substr(attr_name,0,24),substr(attr_name,0,24)||'_disp') attr_name FROM ego_attrs_v a WHERE attr_group_name = 'Claims';
    BEGIN
            SELECT agv_name INTO lv_view_name FROM ego_attr_groups_v WHERE attr_group_name = 'Claims';
            BEGIN
            FOR lc_agv_name_rec in lc_agv_name
            LOOP
                lv_col_stmt := lv_col_stmt || lc_agv_name_rec.attr_name ||',';
                lv_var_stmt := lv_var_stmt || 'l_pairs('''|| lc_agv_name_rec.database_column ||''') ,';
            END LOOP;
                lv_columns := SUBSTR(lv_col_stmt,0,length(lv_col_stmt)-1);
                lv_var_columns := SUBSTR(lv_var_stmt,0,length(lv_var_stmt)-1);
    EXCEPTION
        when others
        then
            dbms_output.put_LINE('mESSAGE :'||lv_columns||'--'||lv_var_columns||'-'||SQLCODE||'-'||SQLERRM);
            END;
            BEGIN
                v_cursor := DBMS_SQL.OPEN_CURSOR;
                DBMS_SQL.PARSE(v_cursor, 'SELECT '||lv_columns||' INTO '||lv_var_columns|| ' FROM '||lv_view_name||' WHERE header_id = 8175', DBMS_SQL.NATIVE);
                v_rows :=DBMS_SQL.EXECUTE(v_cursor);
                loop
                if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
        dbms_output.put_LINE('No Data :View '||lv_view_name||' Columns '||lv_columns);
                exit;
                end if;
            end loop;
                DBMS_SQL.CLOSE_CURSOR(v_cursor);
        dbms_output.put_LINE('Values :'||l_pairs('Coulmn1') ||'some variable'||l_pairs('Coulmn2') );
        EXCEPTION
        when others
        then
            dbms_output.put_LINE('Message :'||lv_sql_stmt||SQLCODE||'-'||SQLERRM);
            END;
        END;
    /

Remove the exception handler, then you will also the the line where error occurs. — Wernfried Domscheit
– Wernfried Domscheit, Commented Aug 30, 2017 at 11:31
INTO is not valid SQL, it is PL/SQL. You need to use DBMS_SQL.COLUMN_VALUE to extract the values one at a time from the data returned by FETCH_ROWS. — Tony Andrews
– Tony Andrews, Commented Aug 30, 2017 at 12:21

Wernfried Domscheit · Accepted Answer · 2017-08-30 11:59:53Z

Have a look at this part

BEGIN
        SELECT agv_name INTO lv_view_name FROM ego_attr_groups_v WHERE attr_group_name = 'Claims';
        BEGIN
        FOR lc_agv_name_rec in lc_agv_name
        LOOP
            lv_col_stmt := lv_col_stmt || lc_agv_name_rec.attr_name ||',';
            lv_var_stmt := lv_var_stmt || 'l_pairs('''|| lc_agv_name_rec.database_column ||''') ,';
        END LOOP;
            lv_columns := SUBSTR(lv_col_stmt,0,length(lv_col_stmt)-1);
            lv_var_columns := SUBSTR(lv_var_stmt,0,length(lv_var_stmt)-1);
EXCEPTION
    when others
    then
    ....

I think an exception in this part is very unlikely, that means you will never run the main part of dynamic SQL.

Anyway, this one does not work:

DBMS_SQL.PARSE(v_cursor, 'SELECT '||lv_columns||' INTO '||lv_var_columns|| ' FROM '||lv_view_name||' WHERE header_id = 8175', DBMS_SQL.NATIVE);

You must do it similar to this:

    TYPE l_column_pair IS TABLE OF VARCHAR2(2400)  -- PL/SQL table
    l_pairs  l_column_pair := l_column_pair();
    ColumnCount INTEGER := 0;
BEGIN

FOR lc_agv_name_rec in lc_agv_name LOOP
    ColumnCount := ColumnCount + 1;   
    lv_col_stmt := lv_col_stmt || lc_agv_name_rec.attr_name ||',';
END LOOP;
lv_columns := SUBSTR(lv_col_stmt,0,length(lv_col_stmt)-1);


DBMS_SQL.PARSE(v_cursor, 'SELECT '||lv_columns|| ' FROM '||lv_view_name||' WHERE header_id = :headerId', DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE (v_cursor, ':headerId', 8175);
FOR c_agv_name_rec in lc_agv_name LOOP
    DBMS_SQL.DEFINE_COLUMN(v_cursor, lc_agv_name_rec.ROWNUM, lc_agv_name_rec.attr_name, 2400);
END LOOP;
res := DBMS_SQL.EXECUTE_AND_FETCH(v_cursor, TRUE);
FOR c in 1..ColumnCount  LOOP
   l_pairs.EXTEND;
   DBMS_SQL.COLUMN_VALUE(v_cursor, c, l_pairs(c));
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);

Note, DBMS_SQL.EXECUTE_AND_FETCH(v_cursor, TRUE); fetches only one (i.e. the first) row. If your query may return more than one row you must do it like this:

l_pairs.EXTEND(ColumnCount);

res := DBMS_SQL.EXECUTE(v_cursor);
WHILE (DBMS_SQL.FETCH_ROWS(v_cursor) > 0) LOOP
    FOR c in 1..ColumnCount  LOOP
       DBMS_SQL.COLUMN_VALUE(v_cursor, c, l_pairs(c));
    END LOOP;       
END LOOP;

Collectives™ on Stack Overflow

Dynamic Query in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related