Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

conersion of sql server code to slq plus Expand / Collapse
Author
Message
Posted Sunday, March 25, 2012 11:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 25, 2012 11:16 AM
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
Post #1272364
Posted Sunday, March 25, 2012 12:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1272375
Posted Sunday, March 25, 2012 12:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1272377
Posted Monday, March 26, 2012 4:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:26 PM
Points: 65, Visits: 813
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.
Post #1272514
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse