オープンクエリを取得 - Oracle

この動的ストアドプロシージャは、OpenQuery を使用して SQL Server にリンクされた Oracle データベースからデータをインポートします。

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.