Generating SQL with SQL I'm trying to generate SQL statements using SQL to create SQL code that will be highlighted and run by an admin to process the statement. My issue is with the code I have created. It's not efficient at the moment as it is using fixed values.

Is there a way to get the code to auto generate a script that produces several statements for users, by looping through my table of users and extracting the usernames, placing these values into the declared variable U_USERNAME?

Would I have to use a SELECT INTO the U_USERNAME variable? How would that look syntax wise in this instance?

DECLARE
u_perm VARCHAR2(25);
u_table VARCHAR2(25);
u_username VARCHAR2(25);
u_code VARCHAR2(2000);

BEGIN
u_perm := 'INSERT';
u_username := 'J123';
u_table := 'TABLE_NAME';
u_code :='';
dbms_output.put_line('Grant ' || u_perm || ' on ' || u_table || ' to ' || 
u_username || ';' ); 

u_code := ('Grant ' || u_perm || ' on ' || u_table || ' to ' || u_username || 
';') ; 
dbms_output.put_line ('u_code = ' || u_code|| '');


END;


---The user table that will store the username I will being trying to extract from.

CREATE TABLE  tblss_user(
user_id       INTEGER NOT NULL,
user_name     VARCHAR(15),
reg_date      DATE
You should be GRANT-ing to a ROLE, not the individuals.
One exception to this:  one Schema is creating code that reference another schema's table.

Grant to Multiples schemas/roles at once

This is legitimate code:
GRANT INSERT ON SCOTT.EMP TO ADAMS,ALLEN;

If you are on 11g or higher, you can use LISTAGG() to generate the comma separated list

declare
  local_variable  varchar2(2000);
begin
  select listagg( e.ename,',' ) within group (order by e.empno)
    into local_variable
  from scott.emp e
  where e.ename like 'A%';
end;
/


But, you'll need to limit the number of rows.  LISTAGG will error if it is over a certain length (eg 4000 bytes for 11g)

LOOPing

You'll want to LOOP over your table:

begin
  for curr in ( select e.ename from scott.emp e order by e.ename)
  loop
    dbms_output.put_line( 'grant for ' || curr.ename );
  end loop;
end;
/


Master's Technique

OK, ok.  This is my technique.  (maybe others)

Use one of the many freely available template based code generators.
This is probably only useful if you are going to do this on a repetitive cycle as new tables are created.

Again, you should be granting to a ROLE, not individual users.