Tag: oracle

Converting Sql Server If not exists to Oracle

I have a snippet of a SQL Server procedure that looks like this: IF NOT EXISTS(SELECT ID FROM IDTABLE WHERE ID=@ID) BEGIN DECLARE @MSG VARCHAR(100) SET @MSG = ‘The ID (ID: ‘ + CONVERT(VARCHAR(50), @ID) + ‘) does not exist’ EXEC SP_LOGAUDITMESSAGE ‘Warning’, ‘Bill report’, @MSG SET @RETVAL = 0 RETURN END This runs the […]

Hierarchical Query in MySQl vs SQLServer vs Oracle

I am trying to implement the Connect By query in oracle/mysql/sqlserver to understand the difference in working. Trying to understand how it works. So I have a simple table which looks likes: empno ename mno 1 KS null 2 AB 2 3 BC 1 4 TR 3 5 QE 2 6 PL 3 7 LK […]

Error: The module depends on the missing object 'sysdb.ssma_oracle.db_check_init_package'

i migrated an entire oracle Database to SQL Server 2014 Database using SSMA. (SQL Server Migration assistant) The migration resulted in introduction of multiple Execution calls in my stored procedures (like below): The module ‘INFRA_IP_WEBLOGGER$spCheckIPStatus’ depends on the missing object ‘sysdb.ssma_oracle.db_check_init_package’. The module will still be created; however, it cannot run successfully until the object […]

Joining oracle with mssql table

When i execute following line i get the result: select * from my_table@link; But when i try to join linked table with other tables like in select i get error that says “z.id – invalid identifier” SELECT e.USER_NAME,e.FULL_NAME,r.RFC_NUMBER,r.TYPE, TO_CHAR(TRUNC(a.TOTAL_TIME/3600),’FM9900′) || ‘:’ || TO_CHAR(TRUNC(MOD(a.TOTAL_TIME,3600)/60),’FM00′) || ‘:’ || TO_CHAR(MOD(a.TOTAL_TIME,60),’FM00′) as TOTAL_TIME, a.ASSIGN_DATE,a.TIME_START,a.TIME_STOP, r.SUBMITTED_BY,r.REGISTER_DATE,r.DESCRIPTION FROM table_a a JOIN […]

SSIS and sending query with date to Oracle

I am trying to create a flow to pull data from an Oracle table into a SQL Server table. I am sending the following query to Oracle to get the data: select distinct CHLD.id, nvl(chld_c_spl, ‘N’), to_char(chld_d_start, ‘YYYY-MM-DD’) chld_d_start, to_char(chld_d_end, ‘YYYY-MM-DD’) chld_d_end from child chld, picture ptct where CHLD.id = PTCT.chld_id and nvl(chld_d_end, sysdate) >= […]

Update from aggregate in same table if aggregate value wrong – SQL Server/Oracle/Firebird

I have a table with grouped tasks: tt_plan_task_id is the id records with tt_plantype=1 represent ‘groups’ tasks in/under a group have a tt_group_id pointing to the tt_plan_task_id there are tasks that don’t belong to a group (tt_group_id is null) groups nest multiple levels I need to fix (update) the tt_fromdate field values for the group […]

Oracle to SQL Server Migration issue – On delete cascade

Folks, Iam working on Oracle to MS Sql Server migraiton. One of the issue I came across is that “ON DELETE CASCADE” works fine in Oracle, but not in Sql Server. Is there any alternative to the syntax/command or is it something we need to manually remove/fix it. Could not get a valid link/reference on […]

Is there any common function for formatting date in SQL Server and Oracle?

I am building an application and need to format the datetime field to dd/mm. I was using TO_CHAR in Oracle and which is not supported in SQL Server and got error when it is deployed with SQL Server database. Is the any common function which can be used across different databases for formatting the datetime […]

Heterogeneous Oracle to SQL Server: Using “IS NULL” in Where clause for Update statement

I am having trouble with using the IS NULL condition in an Update statement executed on an SQL Server 2000 database via an Oracle Database ( using an ODBC heterogeneous connection. Example of update statement that will not work. UPDATE TABLENAME@RemoteSQLServer2000 SET “ColumnName” = ‘SomeValue’ WHERE “AnotherColumnName” IS NULL; Result: ORA-02070: database RemoteSQLServer2000 does not […]

Assign unique ID's to three tables in SELECT query, ID's should not overlap

I am working on SQL Sever and I want to assign unique Id’s to rows being pulled from those three tables, but the id’s should not overlap. Let’s say, Table one contains cars data, table two contains house data, table three contains city data. I want to pull all this data into a single table […]

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