Get Open Query - Oracle

This dynamic Stored Procedure will import data from an Oracle Database linked into SQL Server using OpenQuery

About

The Oracle module will enable you to import from data from an Oracle Database linked into SQL Server, either as a one off or as part of the module created in the main document by using dynamic SQL to generate the relevant scripts.

Create Stored Procedure

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

Due to some issues with the way the Oracle process was working, we have coded this process to DROP and then CREATE a new table. If you need indexing on it, you will need to create them after the table has been built.

The current solution for smaller data size is to use a temporary table for this process and insert from there into your main version.