Oracle - Listar datas do mês

 select TRUNC(SYSDATE)  + level - 1 dt

from   dual

connect by level <= (

  LAST_DAY(SYSDATE) - TRUNC(SYSDATE) + 1

)

Cursor com Oracle

 BEGIN

-- Create cursor and start loop

FOR C_PAR in (SELECT * FROM TGFPAR WHERE CODPARC < 100) LOOP

-- run commands for each item here

DBMS_OUTPUT.PUT_LINE(C_PAR.CODPARC || ' - ' || C_PAR.RAZAOSOCIAL);

END LOOP;

END;

Resultado:

Creating user to acess SQL databases on Azure

 

Open Azure Data Studio and execute these commands:

-- create login credentials on the server

USE master;

CREATE LOGIN user_name WITH password='p455w0rd';

GO

-- create user on the database

USE DATABASE_NAME;

CREATE USER user_name FROM LOGIN user_name;

GO

-- grant permission for user

USE DATABASE_NAME;

EXEC sp_addrolemember 'db_datareader', 'user_name';

EXEC sp_addrolemember 'db_datawriter', 'user_name';

EXEC sp_addrolemember 'db_backupoperator', 'user_name';

GO

SQL Backup Docker container

Create account in https://sqlbak.com/ and copy the key (You will need after)

Download sqlBak:

Run: docker run --name sqlbak_container -d -v sqlbak_volume:/opt/sqlbak pranasnet/sqlbak

Run the container with your key

Run: docker exec sqlbak_container sqlbak --register --key=YOUR_KEY_HERE -n sqlbak

Now you need to add a connection to DBMS (MySQL, PostgreSQL, MSSQL Server). 

Run: docker exec 9d8a0302a4a3 sqlbak -ac -dt <dbms_type> -h <host> -u <user_name> -p <password>

SQL Server on Docker Container

After instal Docker run following commands (depends on your main OS, maybe you need add "sudo" on begin for each command): 

// install SQL Server

docker pull mcr.microsoft.com/mssql/server:2017-latest

// run container

docker run --name sqlserver --hostname sqlserver -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourPasswordHere" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest 

// run container with autorun on restart (add --restart always)

docker run -- restart always --name sqlserver --hostname sqlserver -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourPasswordHere" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest 

// list services

docker ps -a

// open bash terminal inside into container

docker exec -it sqlserver "bash"

// run sqlcmd tool

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "YourPasswordHere"

// stop container

docker stop sqlserver

// remove 

docker rm sqlserver

Oracle - Week number per day

 Query:

SELECT TODAY, 

TO_CHAR(TODAY,'IW') "WEEK_NUM"

from (select (TO_DATE('01/01/'||TO_CHAR(2021), 'DD/MM/YYYY') - 1) + level TODAY 

from DUAL 

connect by LEVEL <= 366)

Result:

Oracle - Query retuning start and end date per week number and year

 Query:

SELECT * FROM (SELECT WEEK_YEAR, WEEK_NUM, MIN(TODAY) START_DATE, MIN(TODAY) + 6 END_DATE

FROM (SELECT EXTRACT(YEAR FROM TODAY) WEEK_YEAR, TODAY, 

TO_CHAR(TODAY,'IW') "WEEK_NUM"

from (select (TO_DATE('01/01/'||TO_CHAR(2021), 'DD/MM/YYYY') - 1) + level TODAY 

from DUAL 

connect by LEVEL <= 368))

GROUP BY WEEK_YEAR, WEEK_NUM

ORDER BY START_DATE)

WHERE WEEK_YEAR = 2021

AND NOT (EXTRACT(MONTH FROM START_DATE) = 1 AND WEEK_NUM > 10)

Result: