Menu SQL para SQL*Plus

Menu SQL para SQL*Plus

É comum clientes de empresas que prestam serviço de outsourcing delegarem funções de suporte a seus bancos de dados, os quais encontram-se espalhados ao redor do mundo. Neste contexto, grande parte dos ambientes onde esses bancos de dados se encontram são ambientes muito controlados por questões de segurança, afinal trata-se de bancos de dados de produção, cujos dados correspondem a grande parte do capital das empresas. Esse controle se estende claramente às formas de acesso, limitando as aplicações que se conectam aos servidores e aos bancos de dados.

Por este motivo, ferramentas visuais de gerenciamento do banco de dados, tais como Oracle SQL Developer, Toad, dentre outras, não são permitidas. Assim sendo, em muitos casos, a única solução para acessar uma base Oracle é o SQLPlus, por ser nativo.

DBAs que trabalham com manutenção e gerenciamento de bancos de dados comumente executam um conjunto restrito de scripts, dentre comandos to tipo DDL e DML, além das funções de atualização, shutdown e start das bases. Tais scripts são sempre muito parecidos, mudando-se apenas um ou outro valor nas colunas listadas na cláusula where.

A ideia inicial do uso de variáveis de substituição nestes scripts é possibilitar o reaproveitamento de todo o código, onde o responsável por sua execução entra com valores (nome de tabelas, nome de colunas ou valores para comparação) em tempo de execução. Ou seja, ao chamar o script de dentro do SQLPlus com o “@”, tais valores são digitados em tempo de execução, como mostra o exemplo a seguir.

SELECT first_name || ‘ ‘ || last_name as “Full Name”
FROM employees
WHERE employee_id = &ID_DO_EMPREGADO;

A execução do código acima solicita ao usuário que seja digitado um valor para a variável ID_DO_EMPREGADO, que posteriormente será utilizada para comparar valores da coluna employee_id da tabela Employees com o valor digitado, como mostra a figura a seguir. 

Pois bem, até o momento as variáveis de substituição podem ajudar muito, mas ainda existe um problema. Temos uma variedade grande de scripts que executamos, e mesmo havendo reaproveitamento dos scripts, temos que encontra-los, carrega-los no SQLPlus para depois executá-los. Pensando neste problema, não seria muito mais adequado se houvesse um menu com opções, onde cada opção está atrelada à execução de um script em específico? Dessa forma, depois de programar os scripts e o menu, não será mais necessário mexer em qualquer código SQL, mas sim somente interagir com o menu e os valores pedidos dentro de cada script.

Para melhor ilustrar o exemplo, construí um pequeno sistema de menu para o gerenciamento de usuários dentro do banco de dados, onde construí uma série de scripts para fazer o gerenciamento dos usuários do banco. São eles

create_user.sql – Cria um usuário

UNDEFINE USER

SET VERIFY OFF

CREATE USER &&User

IDENTIFIED BY &Password

PROFILE &Profile;

GRANT CONNECT TO &user;

UNDEFINE USER

@C:/menu/menu.sql

create_user_expired.sql – Cria um usuário expirado

UNDEFINE USER

SET VERIFY OFF

CREATE USER &&User

IDENTIFIED BY &Password

PROFILE &Profile

PASSWORD EXPIRE;

GRANT CONNECT TO &user;

UNDEFINE USER

@C:/menu/menu.sql

verify_user.sql – Verifica o status de um usuário

SET VERIFY OFF

SELECT username as "User", account_status as "Status"
FROM dba_users
WHERE username = UPPER('&User'); 

@C:/menu/menu.sql

change_pwd_user.sql – Altera a senha de um usuário

SET VERIFY OFF

ALTER USER &USER IDENTIFIED BY &Password;

@C:/menu/menu.sql

change_pwd_expire_user.sql – Altera a senha e deixa o usuário expirado

SET VERIFY OFF

ALTER USER &User IDENTIFIED BY &Password PASSWORD EXPIRE;

@C:/menu/menu.sql

unlock_user.sql – Desbloqueia um usuário

SET VERIFY OFF

ALTER USER &User ACCOUNT UNLOCK;

@C:/menu/menu.sql

lock_user.sql – Bloqueia um usuário

SET VERIFY OFF

ALTER USER &User ACCOUNT LOCK;

@C:/menu/menu.sql

drop_user.sql – Remove um usuário e todos os seus objetos

SET VERIFY OFF

DROP USER &User CASCADE;

@C:/menu/menu.sql

sair.sql

Todos os scripts, incluindo o menu, devem ficar armazenados dentro de uma mesma pasta. No Windows, criei meu exemplo na pasta scripts, que se localiza na raiz C:

O que nos resta agora é escrever o script que desenha um menu no SQLPlus, que será utilizado para chamar os demais scripts utilizados no gerenciamento dos usuários e depois invoca-lo no SQLPlus pelo @.menu.sql

SET LINESIZE 100

UNDEFINE V_SCRIPT

PROMPT

PROMPT |-------------------------------|

PROMPT |   GERENCIAMENTO DE USUARIOS   | 

PROMPT |-------------------------------|

PROMPT

PROMPT   ESCOLHA A OPCAO DESEJADA:

PROMPT

PROMPT   1 - Criar Usuario

PROMPT   2 - Criar Usuario Expirado

PROMPT   3 - Verificar Status do Usuario

PROMPT   4 - Mudar a Senha do Usuario 

PROMPT   5 - Mudar a Senha do Usuario com Opcao Expirado

PROMPT   6 - Desbloquear Usuario

PROMPT   7 - Bloquear Usuario

PROMPT   8 - Remover Usuario

PROMPT   9 - Sair

ACCEPT SELECTION PROMPT "Digite a opcao: "

SET TERM OFF

COLUMN script new_value v_script

SELECT CASE '&selection.'
        WHEN '1' THEN 'C:/menu/create_user.sql'
        WHEN '2' THEN 'C:/menu/create_user_expired.sql'
        WHEN '3' THEN 'C:/menu/verify_user.sql'
        WHEN '4' THEN 'C:/menu/change_pwd_user.sql'
        WHEN '5' THEN 'C:/menu/change_pwd_expire_user.sql'
        WHEN '6' THEN 'C:/menu/unlock_user.sql'
        WHEN '7' THEN 'C:/menu/lock_user.sql'
        WHEN '8' THEN 'C:/menu/drop_user.sql'
        WHEN '9' THEN 'C:/menu/sair.sql'
        ELSE 'C:/menu/menu.sql'
       END as script
FROM DUAL;

SET TERM ON

@&v_script.

O script usa, além das variáveis de substituição, a função CASE, que seleciona o script a ser executado dependendo da função escolhida. Como os DBAs tem sempre uma pasta reservada a eles dentro dos servidores, colocar a pasta “menu” dentro dessa pasta é uma boa opção.

Só lembre-se de atualizar o caminho dos scripts, que no exemplo apontam sempre para ‘C:/menu/”.

Espero ter ajudado!