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

Question

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');

Alex Poole · Accepted Answer · 2022-05-12 08:07:51Z

1

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;

answered May 12, 2022 at 8:07

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2022-05-12 08:06:26Z

0

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;
/

answered May 12, 2022 at 8:06

MT0

173k12 gold badges70 silver badges136 bronze badges

1 Comment

Bhanu Pratap Over a year ago

Thank You will let you know the outcome.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related