CIMB Bank Berhad
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
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 |
