Obtener Open Query - Oracle

Este procedimiento almacenado dinámico importará datos de una base de datos Oracle vinculada a SQL Server mediante OpenQuery.

Acerca de

El módulo Oracle permite importar datos de una base de datos Oracle vinculada a SQL Server, ya sea de forma aislada o como parte del módulo creado en el documento principal, mediante el uso de SQL dinámico para generar las secuencias de comandos pertinentes.

Crear procedimiento almacenado

CREATE PROC [dbo].[GetOpenQuery_Oracle](@LinkServ NVARCHAR(100),@Database NVARCHAR(100),@Schema NVARCHAR(MAX),@Table NVARCHAR(100),@WHERE NVARCHAR(1000),@TOP INT,@SrcScheme NVARCHAR(100)='LIVE',@SkipCols NVARCHAR(MAX)='',@INNER NVARCHAR(MAX)='',@SkipTruncate BIT=0) AS BEGINSET NOCOUNT OFF;IF @INNER=0 SET @INNER=''DECLARE @T NVARCHAR(100)=''IF @INNER<>'' SET @T='''''+'''+@Table+'''+''.'''SET @SkipCols=ISNULL(@SkipCols,'')SET @SrcScheme=ISNULL(@SrcScheme,'LIVE')DECLARE @OUPUT NVARCHAR(MAX)='@CREATE NVARCHAR(MAX) OUTPUT,@ORACLE NVARCHAR(MAX) OUTPUT'DECLARE @CREATE NVARCHAR(MAX),@ORACLE NVARCHAR(MAX),@SQL NVARCHAR(MAX)='SELECT @CREATE=COALESCE(@CREATE+'','','''')+QUOTENAME(COLUMN_NAME)+'' ''+SQLType,@ORACLE=COALESCE(@ORACLE+'','','''')+'+@T+'+''"''+COLUMN_NAME+''"''FROM (SELECT COLUMN_ID,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,(CASE DATA_TYPEWHEN ''NUMBER'' THEN ''DECIMAL(''+CONVERT(NVARCHAR(10),DATA_LENGTH)+'',''+CONVERT(NVARCHAR(10),DATA_PRECISION)+'')''WHEN ''ROWID'' THEN ''INT'' WHEN ''LONG'' THEN ''BIGINT'' WHEN ''FLOAT'' THEN ''FLOAT''WHEN ''VARCHAR2'' THEN ''NVARCHAR(''+CONVERT(NVARCHAR(10),DATA_LENGTH)+'')'' WHEN ''NVARCHAR2'' THEN ''NVARCHAR(''+CONVERT(NVARCHAR(10),DATA_LENGTH)+'')'' WHEN ''CHAR'' THEN ''NVARCHAR(''+CONVERT(NVARCHAR(10),DATA_LENGTH)+'')''WHEN ''CLOB'' THEN ''NVARCHAR(MAX)'' WHEN ''LONG RAW'' THEN ''BINARY(MAX)'' WHEN ''DATE'' THEN ''DATETIME2''ELSE NULL END) SQLTypeFROM OpenQuery('+@LinkServ+', ''select * FROM DBA_TAB_COLUMNS  WHERE OWNER = '''''+@SrcScheme+''''' AND table_name = '''''+@Table+''''''')) xWHERE '','+@SkipCols+','' NOT LIKE ''%,''+COLUMN_NAME+'',%'''PRINT @SQLEXEC sp_executesql @SQL,@OUPUT,@CREATE OUTPUT,@ORACLE OUTPUT--PRINT @CREATE--PRINT @ORACLEIF @WHERE IS NOT NULL SET @WHERE=' '+@WHERE+' 'IF @WHERE IS NULL SET @WHERE=''IF @INNER IS NOT NULL SET @INNER=' '+@INNER+' 'IF @INNER IS NULL SET @INNER=''DECLARE @DROPTABLE NVARCHAR(MAX)='IF NOT OBJECT_ID(''['+@Database+'].['+@Schema+'].['+@Table+']'') IS NULL DROP TABLE ['+@Database+'].['+@Schema+'].['+@Table+']'DECLARE @CREATETABLE NVARCHAR(MAX)='CREATE TABLE ['+@Schema+'].['+@Table+']('+@CREATE+')'DECLARE @SELECTTOP NVARCHAR(100)=''IF ISNULL(@TOP,0)<>0 SET @SELECTTOP=' TOP '+CONVERT(NVARCHAR(10),@TOP)DECLARE @SELECT NVARCHAR(MAX)='SELECT'+@SELECTTOP+' * INTO ['+@Database+'].['+@Schema+'].['+@Table+']FROM OpenQuery('+@LinkServ+', ''SELECT '+@ORACLE+'FROM '+@SrcScheme+'.'+@Table+''+@INNER+''+@WHERE+' '')'EXEC sp_executesql @DROPTABLEPRINT @SELECTEXEC sp_executesql @SELECTEND

Debido a algunos problemas con la forma en que el proceso de Oracle estaba funcionando, hemos codificado este proceso para SUPRIMIR y luego CREAR una tabla nueva. Si necesitas indexación en ella, tendrás que crearla después de que la tabla haya sido creada.

La solución actual para reducir el tamaño de los datos es utilizar una tabla temporal para este proceso e insertar desde ella en la versión principal.