Criação de tabela via PL/SQL

[fsitjaParticipante]

Segue abaixo o exemplo de como fazer, mas sem querer dizer que deve ser feito isso. 😉


Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as fsitja

SQL>
SQL> create user usuario identified by abc default tablespace users quota 10M on users;

User created

SQL> grant create session to usuario;

Grant succeeded

SQL> grant create table to usuario;

Grant succeeded

SQL> CREATE OR REPLACE PROCEDURE cria_tabela(v_parametro VARCHAR2) AUTHID CURRENT_USER IS
2 v_comando VARCHAR2(2000);
3 BEGIN
4 v_comando := 'create table usuario.cliente_fin_' || v_parametro ||
5 ' as select 1 cd_cliente, 1 cd_grupo from dual';
6 --' as select cd_cliente, cd_grupo from cliente_fin';
7 EXECUTE IMMEDIATE v_comando;
8 DBMS_OUTPUT.PUT_LINE('Criada tabela: ' || upper('usuario.cliente_fin_' || v_parametro));
9 END;
10 /

Procedure created

SQL> grant execute on cria_tabela to usuario;

Grant succeeded

SQL> conn usuario/abc@ORCL
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as usuario

SQL> set serveroutput on
SQL> exec fsitja.cria_tabela('MINHATAB')

Criada tabela: USUARIO.CLIENTE_FIN_MINHATAB

PL/SQL procedure successfully completed

SQL> select * from USUARIO.CLIENTE_FIN_MINHATAB;

CD_CLIENTE CD_GRUPO

---

     1          1

SQL> conn fsitja/xxxxxxxxx@ORCL as SYSDBA
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as SYS

SQL> drop user usuario cascade;

User dropped

SQL> drop procedure cria_tabela;

Procedure dropped

SQL>