Tag: oracle

Issue when Transfering Data to Oracle from SQL using SSIS

As part of a project step I need to run an SSIS package which will transfer the results of the previous step to the clients Siebel / Oracle database. There can be up to 5 million rows in the results table If we just use SQL to do an insert (using a stored procedure) it […]

How to insert into SQL Server table from Oracle table?

I wanted to insert into certain column in SQL Server from Oracle. It shows error (always the last column choose error with : invalid identifier). Can guide me on how to insert some values into a SQL Server table from Oracle? INSERT INTO acc_trx_pymt_noti_ack@mssql (ATPNA_ERN, ATPNA_Status, ATPNA_Err_Desc, ATPNA_Create_Dt) SELECT ord_key, truck_id, create_by, create_dt FROM ORD_PYMT; […]

Passing Stored Procedure from SQL Server to ORACLE linked server

I’m trying to pass the following SQL query as a linked server stored procedure, but I get an error, please advise, thanks in advance! Msg 7215, Level 17, State 1, Line 17 Could not execute statement on remote server ‘CAFUAT’. Explanations: CAFUAT = Oracle Linked server CDBWEB = User STP_CAF_PERSON = stored procedure Code: DECLARE […]

Replace REGEXP_SUBSTR in SQL Server

I’m Looking for a way of replacing the use of INSTR(…) and REPLACE(REGEXP_SUBSTR(…)) oracle functions in SQL Server. Original Oracle: SELECT Name, CASE WHEN SUBSTR (NAME, 1, 2) = ‘CG’ THEN SUBSTR (NAME,INSTR (NAME,’_’,1,2)+ 1,LENGTH (NAME)) ELSE REPLACE (REGEXP_SUBSTR (NAME,’_[^_]+’,1,2),’_’,”) END AS OPT, CASE WHEN SUBSTR (NAME, 1, 2) = ‘CG’ THEN SUBSTR (NAME,INSTR (NAME, […]

Missing nvarchar columns when reading SQL Server database table from Oracle

I have a SQL Server database with a table that has a column of nvarchar(4000) data type. When I try to read the data from Oracle through a dblink, I don’t see the nvarchar(4000) column. All the other column’s data is displayed properly. Can anyone help me to find the issue here and how to […]

How to Copy Only First 4000 Characters from Oracle NCLOB to NVARCHAR2(4000) Value?

I am a programmer, and I am writing some SQL code to pull data from a remote Oracle database and insert it into some tables on our local MS SQL Server. I need to bring an Oracle NCLOB column to an NVARCHAR column in our MS SQL Server database. We have no DBA staff to […]

After performing Oracle to SQL Server Migration I lost my SysDB database. How can I recreate it?

I recently did a Oracle to SQL Server 2008 R2 Migration. The PL/SQL stored procedures were converted to T/SQL by SSMA. The converted T/SQL has many references to the sysdb database, for instance sysdb.ssma_oracle.db_check_init_package I then exported the SQL Server 2008 R2 database, and imported it to SQL Server 2012. However I forgot to copy […]

SQL Server Equivalent of Bulk Collect in Oracle

I want to replicate the following code in SQL Server. I’ve had a look around and it seems like the only option is to just use a straight cursor, but is there a better way: DECLARE CURSOR cursor1 IS SELECT * FROM table1; TYPE cursor_aat IS TABLE OF cursor1%ROWTYPE;         l_cursor cursor_aat; BEGIN OPEN cursor1; LOOP FETCH cursor1 BULK COLLECT INTO l_cursor LIMIT 200; FOR INDX IN 1 .. l_cursor.COUNT LOOP IF (CheckSomeData() = 0) THEN INSERT INTO new_table (col1, col2) […]

how to compare dates which is stored as String(varchar) in database?

i have a database(table), in which 2 fields are: fromdate varchar(20) todate varchar(20) dates are stored in this fashion YYYY-MM-DD HH:mm:ss for eg :”2014-10-30 10:10:10″ in database. Now i want to compare two dates and fetch records from database by using query, 2014-09-10 10:10:10(fromdate) to 2014-10-10 10:10:10(todate) how to fetch all accurate records.. Is there […]

I need help converting T-SQL query to Oracle supported query

I am executing this query in SQL Server and it is working fine but when I try to execute it in Oracle, it is not giving the same results. You can see in my attached photo the data of one customer, which have got the code 1, 2,4, 8 and he should get 0.70 value […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.