SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


conersion of sql server code to slq plus


conersion of sql server code to slq plus

Author
Message
murarka.ankita2007
murarka.ankita2007
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
please convert below code of sql server to sql plusso that it doesnt generate syntax error.

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results END
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217977 Visits: 41995
I don't work with Oracle anymore but you don't need a loop for any of this and you probably don't need dynamic SQL for it either. Oracle has all of the information stored in system tables and you just need to hit the books and find out what they are. It will also help your career to learn of such things especially if you're going to be working with Oracle for a while.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14393 Visits: 4639
murarka.ankita2007 (3/25/2012)
please convert below code of sql server to sql plusso that it doesnt generate syntax error.


Please don't take this the wrong way but this is so wrong it is hard to find where to start.

SQLplus is not a language, is an interface to run SQL commands... which do not allow to create a stored procedure.

To create a stored procedure you should use PL/SQL syntax... which is not the same syntax you use when running SQL command on SQLplus.

As a rule of thumbs, to stay out of troubles and avoid hanging yourself from a bridge you may want to think that anything more complex than "select * from table" has to be re-written from scratch. This requires that the person doing the job understands both Transac-SQL and PL/SQL; then the person should understand what the code is doing in one language and re-write it on the other one.

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
jimbobmcgee
jimbobmcgee
SSC Veteran
SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)

Group: General Forum Members
Points: 266 Visits: 916
I agree with the others and add that you shouldn't need/want to do this is in a well-designed database, but that's not going to make your requirement go away (nor the fact that you've been tasked with doing it), is it?

I'm not going to write it for you and check it for syntax, as that would be doing your job for you! Besides, I don't have a working copy of Oracle installed at the moment. But I can hopefully give you some pointers:

1. Temp tables must be created outside of procedures, with the CREATE GLOBAL TEMPORARY TABLE syntax. This means that the object will always be available to everyone, regardless of whether it is populated by your procedure. The semantics are the same, however, in that data inserted during one user session will not be available in another user session and it will clean itself up when the owning session disconnects (the exact point where the data is deleted is determinable by a clause in the CREATE statement).

2. QUOTENAME and PARSENAME don't exist as is, but equivalent functions for their typical uses can be found in the DBMS_ASSERT package.

3. Most dynamic SQL can be run with EXECUTE IMMEDIATE, i.e. EXECUTE IMMEDIATE 'SELECT * FROM blah', or EXECUTE IMMEDIATE v_sql. If you are going to loop round, executing a similar statement each time, consider looping to build up a CLOB full of PL/SQL (in a BEGIN/END; block) and EXECUTE IMMEDIATE-ing that whole block once.

4. INFORMATION_SCHEMA is supposedly ANSI-compliant, so should be available as is, in later versions of Oracle. If not, consider the USER_TABLES/USER_COLUMNS tables for objects in the currently logged on schema, or ALL_TABLES/ALL_COLUMNS for all schemas (subject to granted permissions).

5. If you need to get this data back out (e.g. to a .NET IDataReader), alter your procedure definition to include an OUT SYS_REFCURSOR parameter, then open that cursor with your resultset, e.g. OPEN results FOR SELECT * FROM the_temp_table. Note that, depending on when you've configured the temp table to clean up, the data may no longer be available to a forward-only reader like IDataReader.

Hope this helps.

J.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search