Monday 8 February 2016

I’ve finally worked out ` in pl/sql (flea, flee, fle)

This should have occured before now, but it’s done.  Why is the title of this about `, because the ` command in shell will execute the internals of the command immediately.

I used to always run the results of a script, but I may never do that again, I’m now going to use execute immediate and a loop.  This has changed my scripting 100%.  This is going to save a lot of copy and paste.  I’ll need to work on doing commits, but I’m sure I can get that working too.

Thanks to my freidnly DBA Satheesh for this one!

BEGIN
   FOR R IN (SELECT owner, table_name FROM dba_tables WHERE owner in ('TESTCTL','TESTDTA','CRPCTL','CRPDTA')) LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to JDE_USERS';
   END LOOP;
END;
/

The above can be used instead of generating all of the “GRANT” statements and then running them with cut and paste.

No comments: