Pass cursor variable value instead of variable in PL/SQL block-CodePudding

I am trying to export available SQL profiles into a staging table before upgrade. Since there are many SQL profiles I am trying to use a PL/SQL block to load all of them in a table.

declare
    v_profname varchar2(1000);
    cursor c_profname is select name from dba_sql_profiles;
    r_profname c_profname%ROWTYPE;
    v_sql varchar2(5000);
    v_pack varchar2(8000);
begin
    open c_profname;
    v_sql := 'execute dbms_sqltune.create_stgtab_sqlprof(table_name=>''stg_sql_profiles'',schema_name=>''DBA_UTIL'');';
    execute immediate :v_sql;
    loop
        fetch c_profname into r_profname;
        exit when c_profname%NOTFOUND;
        dbms_output.put_line('sql profile name:' || r_profname.name);        
        v_pack := 'execute dbms_sqltune.pack_stgtab_sqlprof(staging_table_name =>''stg_sql_profiles'',profile_name=>''r_profname.name'');';
        execute immediate :v_pack;
    end loop;
    close c_profname;
    dbms_output.put_line('All sql profiles exported');
    
end;
/

In v_pack dynamic SQL I am getting r_profname.name instead of actual profile name.

Need assistance on how to fix this.

Present Output:

sql profile name:SYS_SQLPROF_0245a98adf750000
dynamic sql statement is:execute dbms_sqltune.pack_stgtab_sqlprof(staging_table_name =>'stg_sql_profiles',profile_name=>'r_profname.name');
sql profile name:SYS_SQLPROF_015b877f29480000
dynamic sql statement is:execute dbms_sqltune.pack_stgtab_sqlprof(staging_table_name =>'stg_sql_profiles',profile_name=>'r_profname.name');
sql profile name:SYS_SQLPROF_015b882d68ec0000
dynamic sql statement is:execute dbms_sqltune.pack_stgtab_sqlprof(staging_table_name =>'stg_sql_profiles',profile_name=>'r_profname.name');
All sql profiles exported

Expected:

execute dbms_sqltune.pack_stgtab_sqlprof(staging_table_name =>'stg_sql_profiles',profile_name=>'SYS_SQLPROF_015b882d68ec0000');

CodePudding user response：

Use a bind variable in the dynamic SQL and a USING clause on the EXECUTE IMMEDIATE to pass the value in:

declare
    v_profname varchar2(1000);
    cursor c_profname is select name from dba_sql_profiles;
    r_profname c_profname%ROWTYPE;
    v_sql varchar2(5000);
    v_pack varchar2(8000);
begin
    open c_profname;
    v_sql := 'execute dbms_sqltune.create_stgtab_sqlprof(table_name=>''stg_sql_profiles'',schema_name=>''DBA_UTIL'');';
    execute immediate :v_sql;
    loop
        fetch c_profname into r_profname;
        exit when c_profname%NOTFOUND;
        dbms_output.put_line('sql profile name:' || r_profname.name);        
        v_pack := 'execute dbms_sqltune.pack_stgtab_sqlprof(staging_table_name =>''stg_sql_profiles'',profile_name=>:1);';
        execute immediate :v_pack USING r_profname.name;
    end loop;
    close c_profname;
    dbms_output.put_line('All sql profiles exported');
    
end;
/

CodePudding user response：

The immediate answer is to concatenate in the name:

v_pack := 'execute dbms_sqltune.pack_stgtab_sqlprof(staging_table_name =>''stg_sql_profiles'',profile_name=>''' || r_profname.name || ''');';

or use a bind variable for it; but the execute is wrong (it's a client shorthand for an anonymous block), and you don't need dynamic SQL at all:

    loop
        fetch c_profname into r_profname;
        exit when c_profname%NOTFOUND;
        dbms_output.put_line('sql profile name:' || r_profname.name);        
        dbms_sqltune.pack_stgtab_sqlprof(staging_table_name =>'stg_sql_profiles',profile_name=>r_profname.name);
    end loop;