Tag: oracle

T-SQL's equivalent of Oracle's %TYPE operator?

When writing a procedure in PL/SQL, I can declare a parameter’s type as myTable.myColumn%TYPE so that when I alter myColumn’s type from say varchar2(20) to varchar2(50) I don’t have to change the procedure’s parameter type. Is there something similar in T-SQL?

What is the Oracle equivalent of “Dual” table in SqlServer?

What is the Oracle equivalent of “Dual” table in SqlServer this is my Select SELECT pCliente, ‘xxx.x.xxx.xx’ AS Servidor, xxxx AS Extension, xxxx AS Grupo, xxxx AS Puerto FROM DUAL;

How to “Open” XML data in Oracle

Here is an example of some TSQL that I would like to rewrite in PL/SQL. DECLARE @xml XML SET @xml = ‘<theRange> <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow> <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow> <theRow><First>John</First><Last>Bates</Last><Age>40</Age></theRow> </theRange>’ ;WITH OpenedXML AS ( SELECT r.value(‘First[1]’,’varchar(50)’) AS First, r.value(‘Last[1]’,’varchar(50)’) AS Last, r.value(‘Age[1]’,’int’) AS Age FROM @xml.nodes(‘//theRange/theRow’) AS Row(r) ) SELECT * FROM OpenedXML WHERE Age BETWEEN 30 AND 35 […]

Does a Join table (association table) have a primary key ? many to many relationship

Does a Join table (association table) have a primary key ? many to many relationship. I’ve seen some Join tables with a primary key and some without can someone please explain when you would have a primary key in a join table and why? Thank you in advance;-)

Javascript date to sql date object

I’m trying to write a query that takes a Javascript date object and then puts it in an object type that is recognized by both SQL Server and Oracle database types. The issue is that I’m using webservices. So it has to be a string, not an actual passed parameter. Here’s what I mean: var […]

How do I declare and use variables in PL/SQL like I do in T-SQL?

In Sql Server, often times when I’m testing the body of a stored procedure, I copy the body into SSMS, DECLARE the variables at the top of the page, set them to some sample values, and execute the body as-is. For Example, if my proc is CREATE PROC MySampleProc @Name VARCHAR(20) AS SELECT @Name Then […]

Create a SSIS Package – To Copy data from Oracle to SQL Server

I want to write a SSIS Package for copying data from oracle to MS Sql server. and also the condition is, What are the data has to be moved from oracle to SQL will be known by selecting a table in SQL. Basically Table1.Column1 in SQL server has to be filled from oracle if the […]

Any disadvantages to bit flags in database columns?

Consider the following tables: CREATE TABLE user_roles( pkey SERIAL PRIMARY KEY, bit_id BIGINT NOT NULL, name VARCHAR(256) NOT NULL, ); INSERT INTO user_roles (bit_id,name) VALUES (1,’public’); INSERT INTO user_roles (bit_id,name) VALUES (2,’restricted’); INSERT INTO user_roles (bit_id,name) VALUES (4,’confidential’); INSERT INTO user_roles (bit_id,name) VALUES (8,’secret’); CREATE TABLE news( pkey SERIAL PRIMARY KEY, title VARCHAR(256), company_fk INTEGER […]

What is the equivalent of varchar(max) in Oracle?

What is the equivalent of varchar(max) in Oracle? CLOB?

Using OPENQUERY with an Oracle database to query a date range

We have an Oracle database that we access through OpenQuery for some stuff. You apparently can’t do date comparisons directly in OpenQuery using the OleDB driver, so to get around this you have to convert the dates to Julien dates and compare those numbers. I have the following query that we’re trying to execute this […]

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