March 25, 2012 at 11:19 am
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'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
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)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(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
March 25, 2012 at 12:30 pm
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
Change is inevitable... Change for the better is not.
March 25, 2012 at 12:38 pm
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.March 26, 2012 at 4:03 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply