Home / Questions / 3295
Accepted
Goerman
Asked: 2020-01-29 06:36:31 +0800 CST 2020-01-29 06:36:31 +0800 CST

How to generate sql statements automatically?

  • 772

It is required to execute repetitive DCL (Data Control Language) statements on certain tables in the database. How can I generate them automatically?

Sentence to generate:

GRANT DELETE, INSERT, SELECT, UPDATE ON NOMBRETABLA TO ADMINISTRADOR;

It must be applied on all the tables that start with FAM_

Expected result after executing the query:

GRANT DELETE, INSERT, SELECT, UPDATE ON FAM_TABLA_A TO ADMINISTRADOR;
GRANT DELETE, INSERT, SELECT, UPDATE ON FAM_TABLA_B TO ADMINISTRADOR;
GRANT DELETE, INSERT, SELECT, UPDATE ON FAM_PRUEBA TO ADMINISTRADOR;
GRANT DELETE, INSERT, SELECT, UPDATE ON FAM_TABLAC TO ADMINISTRADOR;

Updates:

Use a select to concatenate GRANT DELETE, INSERT, SELECT, UPDATE ON with the name of all the tables that start with FAM_

...

sql

1 Answers

  1. Best Answer

    First idea: put the tables you need to work on in a new table, let's say call it, listatablasand then run something like

    SELECT 'GRANT DELETE, INSERT, SELECT, UPDATE ON ' || lt.tabla || ' TO ADMINISTRADOR;'
FROM listatablas lt

    Second idea: if you have sufficient permissions with the user with which you connect to your database, you can get all the tables from the view dba_tables:

    SELECT 'GRANT DELETE, INSERT, SELECT, UPDATE ON ' || table_name || ' TO ADMINISTRADOR;'
FROM dba_tables
WHERE table_name LIKE 'FAM_%'

    If your user does not have sufficient permissions, you could use all_tables instead of dba_tables:

    SELECT 'GRANT DELETE, INSERT, SELECT, UPDATE ON ' || table_name || ' TO ADMINISTRADOR;'
FROM all_tables
WHERE table_name LIKE 'FAM_%'

    Based on: Get list of all tables in Oracle?

    • 5