Task

 CIMB Bank Berhad

Address:	Prima 5-A, Jalan Teknokrat 5, 63000 Cyberjaya, Selangor
Tel:	1 300 880 900
Fax:	+603-8318 3421
Email:	callcentre@cimb.com
Website:	http://www.cimbbank.com.my

BCP 
https://www.youtube.com/watch?v=op_Hx4yelO4
https://www.youtube.com/watch?v=ELjUro80fWA

https://www.youtube.com/watch?v=GFFjYRdTbtg

https://www.careersatcoolblue.com/
bcp -h
https://solutioncenter.apexsql.com/how-to-export-sql-server-data-to-a-csv-file/
https://www.excel-sql-server.com/sql-server-export-to-excel-using-bcp-sqlcmd-csv.htm
declare @bcp_cmd varchar(2000);
DECLARE @stmt_c VARCHAR(8000);
DECLARE @dbname VarChar(100);
DECLARE @bcp_passwd VarChar(100);
DECLARE @bcp_user VarChar(100);
DECLARE @tablename VarChar(100);
DECLARE @data_start_dt Datetime = '2017-08-25 00:00:00.000';
DECLARE @data_end_dt Datetime = '2017-08-28 13:00:00.000';
declare @packet_size int

set @dbname= 'TestDB';
set @tablename = 'tblTest';
set @bcp_user ='sa';
set @bcp_passwd ='sa';
SET @bcp_cmd = 'bcp.exe "SELECT * FROM ' + @dbname + '.dbo.'+ @tablename
set @packet_size=1024;

SET @bcp_cmd = @bcp_cmd + ' WITH (NOLOCK) WHERE data_start_dt >= '''
  --print @bcp_cmd
SET @bcp_cmd = @bcp_cmd + +CONVERT(varchar, @data_start_dt) + ''''  +' AND data_end_dt < '+''''
 --print @bcp_cmd

SET @bcp_cmd = @bcp_cmd + CONVERT(varchar, @data_end_dt) + '''' + '" '
 --print @bcp_cmd
--SET @bcp_cmd= @bcp_cmd +  ' QUERYOUT "D:\TextDB\information_schema5.xls" ' +  '-c -t,' +' -T -S ' + @@SERVERNAME;
SET @bcp_cmd= @bcp_cmd +  ' QUERYOUT "D:\TextDB\information_schema5.xls" ' +  '-c -t,' +' -S ' + @@SERVERNAME

SET @bcp_cmd = @bcp_cmd +' -U "'+@bcp_user+'" -P "'+@bcp_passwd +'" -a '+cast(@packet_size as varchar(10))--+' "^" -r "\n" > '


 print @bcp_cmd
EXEC master..xp_cmdshell @bcp_cmd;




Linked Server

https://www.mssqltips.com/sqlservertip/1433/how-to-setup-linked-servers-for-sql-server-and-oracle-64-bit-client/

https://www.youtube.com/watch?v=IJ0J9t8mkuI


SELECT *
  FROM [WDMRP6.WORLD]..[QIS_CONTROLLER].[VQIS_RD2]
GO

SELECT *
  FROM [P6ORADEV.WORLD]..[ABFADMIN].[A1]
GO
SELECT *
  FROM [P6ORADEV.WORLD]..[ABFTEST].[VIEW_DRV_LOCATION]
GO
SELECT *
  FROM [172.22.24.156\MYBR1].[MYBR1].[dbo].[abd_test]
GO

https://www.youtube.com/watch?v=UHnmcbxk17s

https://www.youtube.com/watch?v=TKEruUBj-aA
http://www.mysqlab.net/knowledge/kb/detail/topic/oracle/id/5386

https://dev.mysql.com/downloads/mysql/

are u able to configure mysql to connect to oracle database WDMR2G ?

Hi Nazmul,

Please do a research and testing on mysql for the requirement below:

Using MYSQL and create procedure and export VQIS_ET2 view in WDMR2G using repuser between timestamp2 “2018-04-05 00:00:00” amd “2018-04-05 00:09:59”:

1)Export csv file from oracle database REPUSER@WDMR2G with delimeter “^” and next line delimeter “=|” \n. Filename same as .42 server.

2)Zip it as gz.

3)Export json format same as in .42 server based on query below:

select '{"fields":[' ||

(

select stragg2('{'

       ||'"COLUMN_NAME":"'||column_name||'"'

       ||',"DATA_TYPE":"'||data_type||'"'

       ||',"MAX_LENGTH":'||data_length||''

       ||',"PRECISION":'|| case when data_precision is not null then '' || data_precision || '' else 'null' end||''

       ||',"SCALE":'||case when data_scale is not null then '' || data_scale  || '' else 'null' end||''

       ||',"IS_NULLABLE":"'||NULLABLE||'"'    

       ||'}' )

from

(

select

    column_name,

    data_type,

    data_length,

    data_precision,

    data_scale,

    nullable  

from all_tab_columns

where table_name='VQIS_ET2'

and owner='QIS_CONTROLLER'

order by column_id

) 

)

|| ']}' from dual

4)Export log file same as .42 server. This is count of that period of time.

MYSQL (use mysql workbench to connect)

10.165.136.42

Port 3306

Username: admin

password: admin

Let me know.

Thanks.

Regards,

CK Tan

Global IT HDD MES (Reporting)

Western Digital ®

Tel: +603-76825352

first, u need to configure mysql to connect to oracle	2:35 PM
using repuser	2:36 PM
then try to select from mysql to connect to oracle