Formatos

    Vimos como utilizar literais para incluir informações nas nossa consultas, mas há ainda como incrementar o uso de literais ao utilizar FORMATOS, que também podem ser chamados de máscaras, que permitem alterar o modo de exibição de um dado em relação a maneira que este dado está persistido no banco de dados.

Podemos utilizar formatos em números e datas ou ainda para aplicar um padrão de de exibição em um texto.

Formatos de Números

    A utilização de formatos em números é feita através das funções TO_NUMBER ou TO_CHAR. No caso da função TO_CHAR os formatos alteram a exibição de um tipo de dado NUMBER, BINARY_FLOAT ou BINARY_DOUBLE  para VARCHAR2. Já na utilização da função TO_NUMBER passamos a exibir um dados CHAR ou VARCHAR2 como NUMBER. Lembrando ainda que ao utilizar modelos de formatação os valores exibidos podem ser arredondados de acordo com o formato o que pode exibir no resultado valores # no arredondamento.

No exemplo abaixo, podemos ver a utilização de alguns padrões.

    A coluna salary da tabela HR.Employees é do tipo NUMBER, então para conversão utilizamos a função TO_CHAR() seguida da máscara. 

    Na coluna Tamanho temos a seguinte máscara '$09999.99', onde a inclusão do caractere $ a frente do valor vai exibir este mesmo símbolo no retorno e o 0 indica que caso o número tenha menos dígitos será complementado com 0 a esquerda como no caso da linha 20 onde o valor exibido é $09600.00.

    Na coluna Tamanho1 temos o exemplo que caso o valor tenha mais dígitos que a máscara o valor será substituído por #, como vemos nas linhas 16, 17, 18 e 19 do retorno, pois o formato definido é $9999.99 e temos valores como 10000 que excede em uma posição o formato suportado.

    Nas colunas CasaDecimal e CasaDecimal1 vemos a utilização de casas decimais nos formatos, na primeira coluna, $999999D99, o D indica que o separador será o que está configurado na parametrização do banco de dados, enquanto na segunda coluna, $9999.99, temos o separador explicito, em ambos os casos, caso o número convertido não tenha casas decimais, será exibido como 00, caso tenha casas decimais será exibido esse valor, conforme exemplo 58.

    Na coluna NOME_MOEDA é possível incluir o nome da moeda local de acordo com parametrização do banco de dados, no caso do meu banco de dados sai o BRL, também é possível incluir o símbolo da moeda conforme utilizado na coluna NOTACAO_MOEDA, ainda sobre moedas, temos a coluna NOTACAO_ALTERNATIVA, onde é possível exibir uma outra notação da moeda. Para exibir esses dados é bastante simples, bastando incluir a respectiva letra antes do formato desejado C para o nome da moeda, L para o símbolo da moeda e U para o símbolo alternativo.

    Na coluna ApenasInteiro temos um arredondamento do valor, sendo ignorados os valores das casas decimais.

E por fim a coluna SINAL onde podemos exibir o sinal de positivo ou negativo do número, bastando incluir a letra S no formato, podendo ser exibido no início ou final.

Formatos de Datas

    Assim como com números é possível utilizar formatos para alterar a exibição de datas, para isso precisamos utilizar uma das funções de conversão TO_DATE, TO_TIMESTAMP ou TO_TIMESTAMP_TZ, também é possível utilizar a função TO_CHAR para conversão de datas em caracteres.

    Na imagem abaixo, temos exemplos do uso dos formatos de datas com as funções TO_CHAR, TO_DATE e TO_TIMESTAMP, podemos encontrar os seguintes formatos no exemplo abaixo.

DD     -> Indica o dia

MM     -> Indica o mês

YYYY   -> Formato para o ano

HH      -> Formato para horário de 12 horas

HH24  -> Formato para horário de 24 horas

MI      -> Formato para minutos

SS      -> Formato para segundos

FF9     -> Formato para fração de segundos, onde o 9 é a quantidade de casas decimais, podendo ser entre 1 e 9, caso a precisão seja ignorada retornará o padrão.

Opções para exibir o dia.

    Além da opção DD que exibe o dia, podemos utilizar o formato DAY que exibirá o nome do dia ou então DY que irá exibir as 3 primeiras letras do nome do dia, a exibição irá depender da maneira como for colocado o formato, por exemplo, DAY irá retornar DOMINGO, enquanto Day retornará Domingo e day o retorno será domingo. Da mesma maneira DY trará DOM, enquanto Dy o resultado será Dom e dy dom.

    Ainda para dias, temos as opções de exibir a data informando o dia da semana na forma de número onde 1 é domingo e 7 sábado para isso utilizamos a formato d, ou exibir o data na forma de dia do ano utilizando o formato ddd.

Opções para exibir o mês

    Assim como para os dias, para os meses também é possível exibir no formato por extenso ou abreviado, para exibir abreviado utilizamos o formato MON e por extenso o formato MONTH e também como os dias, a forma que o formato é passado na máscara será exibido no retorno, caso MONTH será MAIO, caso Month o retorno será Maio e no caso de month teremos maio. Nos casos que o formato configurado seja da maneira abreviada, teremos o retorno apenas das 3 primeiras letras, conforme o exemplo o retorno seria MAI. Existe também a possibilidade de exibir o mês em algarismo romano, para isso é necessário utilizar o formato RM.

Formatos DL e DS

    Os formatos DL e DS são formatos exibidos de acordo com as configurações NLS do banco, onde o DL é o formato longo e o DS o formato curto.

Formatos de semana

    Também temos formatos para retornar uma data com dados da semana. Onde através do formato W temos a semana do mês da data informada e usando o formato WW temos a semana do ano da data informada. Há ainda a possibilidade de utilizar o formato IW que é a semana do ano de acordo com o padrão ISO, ou seja, a primeira semana não necessariamente será semana do dia 01 de janeiro, mas sim a primeira semana que tem ao menos 4 dias no novo ano.

Sufixos para datas

    Há também a possibilidade de incluir sufixos para as formatações de datas da forma que as datas sejam exibidas de forma ordinal, escrita ou combinada. As opções para as máscaras de formatos são: SP, TH ou THSP/SPTH. Vamos ver alguns exemplos práticos de uso.

    Durante provas de certificação atentar nesse ponto, pois existem pegadinhas com a exibição de dados utilizando essas formatações. Por exemplo, embora a sintaxe TH esteja maiúscula, caso o dia(DD) esteja minúsculo será exibido minúsculo.

FX e FM

    Nas sintaxes de formatos para datas é possível incluir FX ou FM, e o que acontece ao adicionar FX ou FM no formato? Basicamente, FX se refere ao formato exato, ou seja, a máscara deve estar idêntica ao conteúdo que se deseja converter. Enquanto o FM é o modo de preenchimento, em que o conteúdo pode ser preenchido com espaços ou 0 para completar o formato, no caso do FM ainda é possível utilizar um separador diferente do formato.

Formato do Ano

    Para anos temos 2 possibilidades de formatos podemos utilizar o RRRR (RR) ou o formato YYYY (YY). O recomendado é a utilização do formato YYYY, pois esse formato elimina a ambiguidade e não prejudica a otimização das consultas. O formato RR é similar ao YY, contudo tem particularidades referentes ao século que basicamente permite armazenar anos de século 20 estando no século 21. Quando utilizamos o formato YY, os 2 primeiros dígitos serão sempre iguais do ano atual, porém quando utilizamos o formato RR os 2 primeiros dígitos vão depender dos 2 últimos dígitos da data e também dos 2 últimos dígitos do ano atual.

    Caso os 2 dígitos retornados para o ano sejam entre 00 e 49:

    - Se os 2 últimos dígitos do ano atual estiverem entre 00 e 49:

    será retornado os mesmos 2 dígitos do ano atual.

    - Se os 2 últimos dígitos do ano atual estiverem entre 50 e 00:

    será retornado 1 número maior que os 2 dígitos do ano atual.

    Caso os 2 dígitos retornados para o ano sejam entre 50 e 99:

    - Se os 2 últimos dígitos do ano atual estiverem entre 00 e 49:

            será retornado 1 número menor que os 2 dígitos do ano atual.

    - Se os 2 últimos dígitos do ano atual estiverem entre 50 e 00:

    será retornado os mesmos 2 dígitos do ano atual.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Format-Models.html#GUID-DFB23985-2943-4C6A-96DF-DF0F664CED96

Literais

Literais

    Quando falamos sobre um valor literal em uma consulta de banco de dados estamos nos referindo há um valor fixo, isso significa que esse valor se repetirá em todas as linhas retornadas pela consulta. Temos 4 tipos de literais textos, numéricos, datas e intervalos, abaixo alguns exemplos do uso, note que embora o valor dentro do literal pareça igual nas colunas "Número" e "Caractere" uma o valor 101 é exibido em formato numérico enquanto na outra em formato de sequência de caracteres, essa indicação é facilmente identificada através do alinhamento dos valores dentro da coluna.

Texto Literal

    Textos podem ser utilizados como literais de maneira bem simples basta incluir os caracteres entre aspas simples, podendo receber um ALIAS, caso o alias seja omitido o título da coluna será o valor utilizado no literal excluindo os espaços.

    Quando há necessidade de incluir aspas no texto que será exibido no literal é necessário fazer a indicação de um delimitador alternativo chamado de quote_delimiter que basicamente é a substituição da delimitação através de aspas simples por outra indicada podendo ser (), {}, [] ou <>. A letra 'Q' no delimitador pode ser maiúscula ou minúscula, e as símbolos usados como delimitador podem ser incluídos dentro do literal. Há ainda a possibilidade de duplicar as aspas para indicar que a mesma está sendo utilizada dentro do literal. Abaixo exemplos de uso dos delimitadores.

    O Oracle permite até 4000 bytes em um único literal, podendo ser 32767 caso o parâmetro MAX_STRING_SIZE esteja com o valor igual a EXTENDED.

Número Literal

    Diferente dos caracteres, números não precisam de aspas simples ( ' ) para informar que são literais, basta incluí-los na consulta, mas assim como os caracteres literais, os números literais também tem suas particularidades.

    Na imagem abaixo, podemos ver formas diferentes de usar literais numéricos. Vale destacar que para números inteiros positivos não é necessário utilizar o sinal de mais ( + ) e que quando se usa casas decimais o limite é de 38 dígitos. Para que o retorno seja no formato BINARY_FLOAT devemos utilizar 'f' após o número e no caso de DOUBLE_FLOAT devemos utilizar 'd' enquanto a letra 'e' simboliza uma notação científica.

    Ao utilizar literais com números devemos ter cuidado quanto ao separador. Para verificar qual o padrão está definido podemos realizar uma consulta e verificar como está definida a parametrização para a sessão ou para o banco de dados através das consultas abaixo:

        select * from nls_session_parameters

        where parameter = 'NLS_NUMERIC_CHARACTERS';

        select * from nls_database_parameters

        where parameter = 'NLS_NUMERIC_CHARACTERS';

    No exemplo abaixo, temos 3 expressões que deveriam ter o mesmo resultado, mas por erro ao escrever a consulta não traz o resultado esperado. Quando o literal utilizado for do tipo numérico devemos utilizar o ponto ( . ) como separador das casas decimais, caso for utilizado a vírgula ( , ) como separador o literal deve ser passado como caractere, ou seja, dentro de aspas simples ( ' ). Caso o separador seja a vírgula e não esteja dentre de aspas simples o banco de dados irá identificar a vírgula como separador de colunas conforme exemplo abaixo.

Data e Horário Literal

    Para os literais de datas é possível informa-los como texto ou utilizar a função TO_DATE e realizar a conversão para formato de data, ou ainda utilizando a função SYSDATE ou SYSTIMESTAMP, sendo ainda permitido efetuar operações com as funções.

    Para identificar o formato de datas utilizado pelo banco de dados, basta realizar uma consulta em NLS_DATABASE_PARAMETERS procurando pelo parâmetro NLS_DATE_FORMAT.

   Caso o horário seja omitido em um literal de data e hora, a hora será interpretada como '00:00:00' ou '12:00:00' dependendo do padrão de horário ser 24 horas ou 12 horas.

    Da mesma maneira, que podemos utilizar as datas, podemos utilizar o formato timestamp como literal, inclusive utilizando as variantes de local time zone ou com um time zone específico.

Intervalo Literal

    Os intervalos especificam períodos de tempo podendo ser anos e meses ou dias, horas, minutos e segundos, de acordo com o tipo de dado utilizado YEAR TO MONTH ou DAY TO MINUTE, conforme visto aqui e também podemos utilizar literais com os intervalos para especificar esses períodos. Nesse tipo de literal informamos 1 ou 2 campos, onde o primeiro é a maior unidade de medida e o segundo a menor unidade de medida, por exemplo, ao passar um literal '123-2' para o tipo YEAR TO MONTH, estamos dizendo que temos 123 anos e 2 meses. 

    Abaixo exemplos do uso do intervalo com anos e meses, nos exemplos podemos notar o uso dos intervalos literais, como a precisão para anos é de 2, precisamos informar a precisão nos casos dos literais maiores que 2 casas, no caso dos mesmo o padrão é 3. no caso dos literais '123-9' estamos é necessário a utilização do year(3) to month enquanto nos outros podemos apenas informar year ou month.

    Lembrando que caso o literal informado não esteja no padrão esperado ou estoure o tamanho da precisão a consulta não irá executar retornando um erro.

    Da mesma maneira, podemos utilizar os literais para DAY TO MINUTE e suas partes. Podendo utilizar apenas uma parte ou mais de uma parte, desde que respeitado o formato necessário.

    O uso de intervalos literais costuma ser um tema recorrente nas provas de certificação 1Z0-071, por isso deve ser um tema estudado e testado bastante.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Literals.html#GUID-4C258D8F-3DF2-4D45-BE3E-14864DD77100

Tipos de Dados - Demais tipos

    Além dos tipos de dados que já vimos, onde podemos manipular e armazenar, caracteres, dígitos e datas, temos outros tipos de dados que veremos abaixo.

LONG

    Atualmente não é mais recomendável utilizar colunas do tipo LONG, pois estão defasadas, devendo ser utilizadas BLOB, CLOB ou NCLOB, sendo recomendando também a conversão de colunas LONG para LOB. Contudo, o banco de dados ainda suporta esse tipo de dados por questões de retro compatibilidade.

O tipo de dados LONG é um tipo que permite armazenar sequências de caracteres de até 2Gb. Esse tipo de dados possui várias peculiaridades entre elas:

• Somente uma coluna long por tabela;
• Colunas long não podem ser utilizadas em índices;
• Não podem ser utilizadas em expressões regulares;
• Não podem ser utilizadas na cláusula WHERE ou em constraints de integridade.
• Não podem ser utilizadas em GROUP BY, ORDER BY, CONNECT BY ou no select com função DISTINCT.

RAW e LONG RAW

    Os tipos RAW e LONG RAW são utilizados para armazenar dados que não são convertidos explicitamente pelo banco de dados Oracle quando move os dados entre diferentes sistemas. Podem ser utilizados para armazenamento de áudios, gráficos, imagens ou documentos. Assim como ocorre com o tipo LONG, é recomendável utilizar os tipos de dados LOB ao invés do LONG RAW. Tipos RAW funcionam como VARCHAR2, pois tem tamanho variável. Os tipos RAW podem ser implicitamente convertidos em CHAR, VARCHAR2 ou LONG e também podem ser explicitamente convertidos em hexadecimal utilizando a função RAWTOHEX e HEXTORAW. 

ROWID

    Todas as linhas no armazenadas no banco de dados Oracle tem um endereço específico que é utilizada para a organização dos dados dentro do banco, esses endereços de cada linha são chamados de rowids. Para saber qual o endereço de um linha basta incluir a pseudocoluna ROWID na área de select da consulta. A pseudocoluna ROWID também pode ser utilizada no campo WHERE.

    Os valores de rowid são formados na base de 64, podem conter caracteres de a-z ou A-Z, dígitos de 0-9 e os sinais + (mais) e / (barra). O tamanho do conteúdo de rowid vai depender também do sistema operacional utilizado no banco de dados.

UROWID

    Urowid significa universal rowid e são utilizados quando os rowids não estão armazenados dentro do banco de dados Oracle, podendo ser tabelas externas do banco ou mesmo de outros bancos de dados acessados pelo Oracle por meio de Gateways.

Tipos LOB

    O tipo de dados LOB significa Large Objects, ou seja objetos grandes. Podemos classificar o dados LOB em internos e externos de acordo com a maneira que é armazenado no banco de dados, os  LOBs internos são armazenados dentro do banco de dados e podem ser do tipo CLOB, NCLOB ou BLOB, enquanto o LOB externo é armazenado fora do banco de dados e é chamado de BFILE. Esses tipos de dados servem para armazenar informações grandes ou fora de padrões, como áudios e imagens. Os tipos LOB podem armazenar até 4000 bytes na forma de coluna, no caso de ter mais de 4000bytes serão sempre armazenados externamente. Colunas LOBs possuem localizadores que referenciam se os valores estão armazenados internamente ou externamente, quando fazendo um select em uma coluna LOB, o banco de dados retorna os localizadores dos registros dessa coluna.

CLOB

    O tipo de dados CLOB armazena dados em formato caractere tanto de byte único como multibyte, são suportados caracteres de tamanho fixo ou variável. Objetos CLOB tem suporte completo para transações, podendo ter commits ou rollbacks em seus dados. As colunas do tipo CLOB podem armazenar até (4Gb-1) * (valor do parâmetro CHUNK), caso o valor para o parâmetro CHUNK padrão tenha sido utilizado na definição da coluna nesse caso o valor que poderá ser armazenado será definido por (4Gb -1) * (tamanho do bloco do banco de dados).

NCLOB

    O tipo de dados NCLOB armazena dados em formato Unicode, são suportados caracteres de tamanho fixo ou variável com ambos utilizando o conjunto de caracters nacional. Objetos NCLOB tem suporte completo para transações, podendo ter commits ou rollbacks em seus dados. As colunas do tipo NCLOB podem armazenar até (4Gb-1) * (valor do parâmetro CHUNK), caso o valor para o parâmetro CHUNK padrão tenha sido utilizado na definição da coluna nesse caso o valor que poderá ser armazenado será definido por (4Gb -1) * (tamanho do bloco do banco de dados).

BLOB

    BLOB significa Binary Large OBjects e armazena dados binários grandes e não estruturados, objetos BLOB tem suporte completo para transações, podendo ter commits ou rollbacks em seus dados. As colunas do tipo BLOB podem armazenar até (4Gb-1) * (valor do parâmetro CHUNK), caso o valor para o parâmetro CHUNK padrão tenha sido utilizado na definição da coluna nesse caso o valor que poderá ser armazenado será definido por (4Gb -1) * (tamanho do bloco do banco de dados).

BFILE

    O tipo BFILE serve para acessar arquivos binários que estão armazenados fora do banco de dados. É possível realizar a troca do caminho onde o arquivo está armazenado ou mesmo o nome do arquivo sem afetar os dados do mesmo utilizando a função BFILENAME. Os dados dentro de um BFILE são apenas para leitura, não sendo possível realizar alterações e é tarefa do DBA garantir que os processos do banco de dados Oracle possuam permissão de leitura nos arquivos BFILE. Um arquivo BFILE poderá ter no máximo 264-1 bytes, contudo esse valor poderá ser menor de acordo com o sistema operacional.

JSON

    Introduzido a partir da versão 21c serve para armazenar arquivos JSON de maneira nativa em formato binário, isso melhora o desempenho, pois não há mais necessidade de conversão. Além do tipo de dados JSON, os arquivos JSON são suportados pelos tipos VARCHAR2, BLOB e CLOB.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483

Tipos de Dados

    Dentro do banco de dados Oracle temos as tabelas e cada tabela é formada por colunas, que representam tipos de dados semelhantes, ou seja, em cada linha da tabela os tipos de dados de cada coluna será igual.

E como podemos descobrir qual o tipo de dados de cada coluna de uma tabela? Simples, basta executar o comando abaixo.

    describe nome_tabela;

ou ainda, podemos abreviar o comando da seguinte maneira

    desc nome_tabela;

    O resultados destes comando é uma listagem das colunas da tabela com seu respectivo tipo de dados.

    Agora que já sabemos como identificar o tipo de dados das colunas de uma tabela precisamos saber o que são cada um deles. A importância do tipo de dados se deve a otimização de um banco de dados, por exemplo, em um banco bem modelado, um campo que contém datas deverá ser do tipo DATE, podemos até armazenar em uma coluna que armazene uma cadeia de caracteres, porém, isso não é o ideal, a maneira que o banco de dados trabalha será prejudicada nesse caso, da mesma maneira um número, podemos armazena-lo em uma coluna que não seja do tipo NUMBER, mas nesse caso ele será tratado pelo banco de dados não como número e sim como um caractere alfanumérico.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483

Utilizando ALIAS

    Já vimos como fazer uma consulta básica utilizando SQL no neste tópico, agora vamos aprofundar o conhecimento.

    Muitas vezes temos nomes de colunas que não são muito amigáveis para exibição. Por exemplo, podemos ter o nome de uma coluna em outro idioma do qual queremos exibir os resultados da consulta, nesse caso, podemos criar uma solução com a utilização de um alias. Um alias basicamente é um apelido para a coluna, que é válido para a exibição dos resultados da consulta em questão.

    Exemplo prático, utilizando a tabela EMPLOYEES do schema HR, precisamos retornar apenas 5 colunas, para isso basta escrever o nome das colunas que queremos exibir separadas por virgula ( , ).

O resultado é exibido com o nome das colunas em letra maiúsculas.

Para utilizar um alias podemos simplesmente incluir a palavra que queremos exibir após o nome da coluna e antes da virgula (,). Contudo, existem algumas regras.

Se for um nome composto ou que possua caracteres especiais devemos colocar entre aspas duplas ( " )

Se for necessário manter a formatação de maiúsculas e minúsculas também devemos utilizar as aspas duplas, caso o alias seja colocado sem aspas duplas, será sempre exibido em letras maiúsculas.

No exemplo acima, podemos ver vários exemplos de alias em uso. A sintaxe básica é:

coluna AS "alias"

A palavra reservada AS pode ser omitida, bem como as aspas duplas em alguns casos.

Os alias não podem ser utilizados na cláusula WHERE, porem, podemos utilizá-los na cláusula ORDER BY.

Ao tentar utilizar um alias no WHERE temos como retorno o erro: ORA-00904.

Abaixo exemplo utilizando o alias na ordenação.

Por que podemos utilizar um alias na ordenação mas não na filtragem?

    Isso ocorre pela maneira como o Oracle executa a consulta. Primeiro é realizada a busca na tabela pelas condições de filtragem, ou seja, os dados que estão na cláusula WHERE, após isso os dados são preparados para exibição, nesse ponto entra o alias para a coluna, e por fim a ordenação dos dados e nesse ponto o alias já existe para a coluna.

Schemas e Schema Human Resources (HR)

 Schemas

       Para entender de maneira simples o que é um SCHEMA no Banco de Dados Oracle, podemos resumir que um schema é um usuário e tudo que tem dentro dele.

    O schema é criado automaticamente, quando um usuário é criado, embora possa ser criado através do comando CREATE SCHEMA. Dentro desta estrutura lógica ficam tabelas, procedures, índices entre outros objetos. E cada schema pertence apenas a seu usuário. Segue lista de objetos que fazem parte de um schema.

• Analytic views
• Attribute dimensions
• Clusters
• Constraints
• Database links
• Database triggers
• Dimensions
• External procedure libraries
• Hierarchies
• Index-organized tables
• Indexes
• Indextypes
• Java classes
• Java resources
• Java sources
• Join groups
• Materialized views
• Materialized view logs
• Mining models
• Object tables
• Object types
• Object views
• Operators
• Packages
• Sequences
• Stored functions
• Stored procedures
• Synonyms
• Tables
• Views
• Zone maps

Embora muitas estruturas sejam armazenadas logicamente dentro de um schema, existem objetos que não fazem parte de um schema, tais objetos são os seguintes:

• Contexts
• Directories
• Editions
• Flashback archives
• Lockdown profiles
• Profiles
• Restore points
• Roles
• Rollback segments
• Tablespaces
• Tablespace sets
• Unified audit policies
• Users

Schemas Sample

    Os schemas sample são estruturas disponibilizadas pela Oracle como exemplos, nela temos usuários, tabelas, índices, constraints, preenchidos com informações, o que torna possível aprender SQL utilizando os mesmos, pois é possível aplicar os conceitos de SQL, manipulando esses dados, criando joins entre as tabelas, testando o funcionamento de funções, enfim, um banco de dados completo e pronto para o uso. A familiaridade com esses schemas dummies, ajuda até mesmo nas provas de certificações da Oracle, visto que esses são utilizados nas questões, logo conhecendo a estrutura dos mesmo pode ajudar na hora da prova, onde se tem muitas questões e pouco tempo para pensar.

• HR Schema (Human Resources) -
• usado para tópicos de introdução ao SQL básico;
• OE Schema (Order Entry)
• usado para tópicos de dificuldade intermediária, possui mais tipos de dados;
• OC Schema (Online Catalog) 
• é uma coleção de objetos relacionais do utilizado no schema OE.
• PM Schema  (Product Media)
• dedicado para dados do tipo mídia;
• CO Schema (Customer Orders)
• é um schema moderno que possui demostrações de transações de e-commerce, permitindando armarzenar dados semi-estruturados usando JSON;
• IX Schema (Information Exchange)
• é um conjunto de schemas, usado para demonstrar as capacidades avançadas de consultas Oracle;
• SH Schema (Sales History)
• desenvolvido para demonstrar grandes quantidades de dados, possui suporte para processamento analítico.

    Até a versão 12 do banco do dados Oracle, era possível incluir a instalação desses schemas juntamente com a instalação do banco, nas versões mais recentes é necessário a instalação manualmente após o banco estar devidamente instalado.

    Maiores detalhes a respeito de cada um dos schemas de exemplo podem ser encontradas neste link.

    Para obter os samples schemas é necessário o download através do GitHub, disponível nesse link.

Instalando o Schema HR

    Como dito anteriormente, nas versões antigas do Oracle Database era possível definir a instalação dos schemas durante a instalação da aplicação, nas versões acima de 18c não temos essa opção, contudo, o schema HR fica disponível na instalação do Oracle Database no caminho: '$ORACLE_HOME/demo/schema/human_resources'.

    Após acessar o diretório onde se encontra os arquivos de instalação do schema, devemos acessar o SQLPlus utilizando um usuário com privilégios de administrador, podemos utilizar o comando abaixo para tal ação.

    $ sqlplus connect sys as sysdba

    Logo em seguida devemos executar o script hr_main.sql que contém os passos automatizados da instalação.

    SQL> @?/demo/schema/human_resources/hr_main.sql

Assim que o script for inicializado, serão apresentados prompts solicitando a inclusão de argumentos, na seguinte ordem:

    - Senha para o usuário HR;

    - Default tablespace para o schema HR;

    - Tablespace temporário para o schema HR;

    - Senha do usuário sys;

    - Diretório de log.

Seguido das informações solicitadas, é necessário aguardar finalizar a instalação.

    Assim que concluir a instalação devem ser verificados os logs para validação da instalação, em alguns casos podem ocorrer erros na criação do usuário HR, nesse caso, o restante da instalação também irá falhar, o erro pode estar relacionado com o tipo de sessão do usuário sys, nesse caso é necessário alterar o tipo de sessão para que e reexecutar o script para que seja concluído com sucesso.                            

    $ alter session set "_ORACLE_SCRIPT"=true;

Após a alteração da sessão, a instalação corre conforme o esperado.

E por fim temos schema devidamente instalado.

Para um detalhamento de todas as ações realizadas pelo script de instalação a Oracle disponibiliza essas informações nessa página.

Schema HR

    O schema HR mostra uma estrutura de recursos humanos de uma empresa, se divide nas seguintes tabelas:

• EMPLOYEES
• JOBS
• JOB_HISTORY
• DEPARTMENTS
• LOCATIONS
• REGIONS
• COUNTRIES

 Segue abaixo modelo de dados do schema HR.

Finalmente com o ambiente completamente finalizado mãos a obra!

Fontes:

https://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/index.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/HR-sample-schema-scripts-and-objects.html

https://docs.oracle.com/database/121/COMSC/scripts.htm#COMSC00021

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Objects.html#GUID-31BE00A7-7FF9-41CB-852A-F1416912CA9E

Instalação do Oracle Database

    Após a criação de uma máquina virtual (VM) e instalar um sistema operacional (Oracle Linux 8) nesta VM, o próximo passo para finalizar um ambiente de estudos é a instalação do SGBD (DBMS) nesta máquina.

    Atualmente o Oracle Database está em sua versão 19c. As versões anteriores são  18c, 12c, 11g, pra ficar apenas nas mais recentes. Podemos notar que a partir da versão 18c houve uma mudança na nomenclatura, com o g dando lugar ao c, isso basicamente indica a mudança da estrutura da aplicação que saiu da versão grid para a versão cloud. As versões grid surgirão na versão 10 do Oracle e traziam a ideia da computação com facilidade para escalabilidades vertical e horizontal, enquanto as versões atuais de cloud indicam que estão de acordo com as novas tendências da computação em nuvem.

    Para iniciar a instalação devemos definir algumas configurações. No arquivo '/etc/hosts' vamos editar colocando o IP e nome completo da máquina.

Logo em seguida, definir o nome da máquina editando o arquivo '/etc/hostname'

    Após as devidas configurações, partimos para a pre instalação do Oracle Database. Podemos executar o comando abaixo, que faz o download de pacotes necessários para o banco de dados.

        # yum install oracle-database-preinstall-19c

Após esse passo é necessário algumas configurações adicionais.

1 - Definir uma senha para o usuário oracle.

    # passwd oracle

    Após esse comando será solicitado para digitar a senha que será definida para o usuário.

2 - Alterar o sistema operacional para o modo permissivo.

    Editar o arquivo '/etc/selinux/config' deixando o parâmetro SELINUX conforme exemplo abaixo.

    SELINUX=permissive

    Após a edição do arquivo, temos 2 opções para o novo argumento do parâmetro ter efeito, podemos reiniciar a máquina ou executar como root o comando abaixo.

    # setenforce Permissive

    Voltando para as configurações adicionais, desligamos o firewall, pois geralmente as empresas tem um sistema de firewall externo não necessitando que o mesmo seja configurado no sistema operacional do banco de dados.

    # systemctl stop firewalld

    # systemctl disable firewalld

    Após desabilitar o firewall devemos efetuar a criação das pastas que serão utilizadas pelo Oracle Database.

    #mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1

    #mkdir -p /u02/oradata

Após criar as pastas é necessário dar as devidas permissões para as mesmas.

    #chown -R oracle:oinstall /u01 /u02

    #chmod -R 775 /u01 /u02

    Agora é necessário descompactar o pacote do instalador na pasta onde será instalado o banco de dados, mas antes disso, devemos sair do usuário root e fazer login usando o usuário oracle, para isso é necessário acessar o diretório "cd /u01/app/oracle/product/19.0.0/dbhome_1/" e em seguida executar o comando para descompactar.

    $unzip -oq /home/oracle/LINUX.X64_193000_db_home.zip 

    Assim que o arquivo da instalação estiver descompactado, enfim pode ser iniciada a instalação, para isso é necessário executar o instalador.

    $ ./runInstaller

    Após finalizar a instalação, no servidor precisamos subir o banco de dados. Para isso devemos ir até a pasta '$ORACLE_HOME/bin' exportas as variáveis do ambiente oracle com o comando abaixo.

    $ . oraenv

    Após validar o listener do banco, com o comando abaixo.

$ lsnrctl status

Caso o listener não esteja ativo, subir com o comando abaixo:

$lsnrctl start

Após o banco estar operativo, podemos conectar com sucesso!

Agora que o banco de dados já está operante, basta criar uma conexão na aplicação SQL Developer e passar a utilizar o banco de dados.