473,326 Members | 2,815 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Convert MSSQL syntax to Oracle - variable declaration

I'm trying to convert the below SQL into Oracle but am having some issues with the variable declarations.

SQL commands
declare @sqlstring varchar(500)
select @sqlstring= (select concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a )

exec('select * from'+@sqlstring+'')

End SQL


Basically the end result is a Select * from XXXXX where XXXXX is the variable value.


I would like to define a varchar2 variable SQLSTRING as the following statement

select concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a from dual

This returns a 5-10 varchar string which I then need to put into the select * from SQLSTRING statement which will return my results.

The issue i am running into is the embedded like statement and the apostrophes.

When I try and declare
sqlstring:=(select concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a from dual);

this gives me all sorts of syntax errors.

If i do (note extra ' 's)
sqlstring:='select concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a from dual';

then the like statement and ARC_ALD section fail.


Ideas?
Dec 19 '07 #1
0 3262

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: francisl | last post by:
We have to build some script were I work to make a dynamic server inventory. But, the project team, a windows crew, start it all in vbscript and on mssql. Note, due to political reason, we can...
3
by: William Buchanan | last post by:
Hi I have the following stored proc in interbase (which might contain errors - i'm doing it off the top of my head), which I would like to convert into oracle. Can you help? What I want back is...
1
by: Philip Mette | last post by:
I am in a crunch and need to covert this Oracle statement to MSSQL. Is there any Oracle/MSSQL experts out there that can help me? I do not understand the syntax enough to modify this. Thanks so...
9
by: Rodusa | last post by:
I am trying to assign @sql variable to @total, where @sql is a sql statement stored on the database, however what I am getting is its string value and not its calcuation. Could anybody help? ...
1
by: ian m via SQLMonster.com | last post by:
Hi, I currently have a ms access update query that runs perfectly well and quicly in access however I now need to add this query to convert this qeryu to oracles equivelant sql syntax and add it...
10
by: Adam Warner | last post by:
Hi all, Just before Christmas Chris Torek gave me some great advice about closures in C: <http://groups.google.co.nz/groups?selm=cqcl3k030vj%40news3.newsguy.com&output=gplain> It includes this...
4
by: kenneth.osenbroch | last post by:
Hi, I have a MSSQL Server communicating with an Oracle database through a MSSQL linked server using a MS ODBC connection. If I query the Oracle database through the Oracle ODBC 32Bit Test, the...
2
by: manindra | last post by:
Recently we migrated our product from MS-SQL 2000 to Oracle 9i. We see lot of performance degradation due to migration. Some times complex queries are hitting 10 fold slower than SQL Server. ...
7
by: krishna | last post by:
What is the need of this syntax for initializing values, isn't this ambiguous to function call? e.g., int func_name(20); this looks like function call (of course not totally as there is no...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.