CREATE or REPLACE PROCEDURE [dbo].[SP_FindValueInDB]
(
@value VARCHAR(1024)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
CREATE TABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64)
)
DECLARE TABLES CURSOR
FOR
SELECT o.name, c.name
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
ORDER BY o.name, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
SET @sql = @sql + @column + ''')'
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @table, @column
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT *
FROM #t
DROP TABLE #t
End
*********************************************************************************************
我需要查询整个数据库中某个特定值所在的表和字段,在网上搜到上面这个存储过程代码。在oracle sql developer中Run Statement通不过,报以下错误,求指导啊!Error report:
ORA-04050: invalid or missing procedure, function, or package name
04050. 00000 - "invalid or missing procedure, function, or package name"
*Cause: The required procedure, function, or package name is invalid
or missing.
*Action: Specify a valid name.
(
@value VARCHAR(1024)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
CREATE TABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64)
)
DECLARE TABLES CURSOR
FOR
SELECT o.name, c.name
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
ORDER BY o.name, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
SET @sql = @sql + @column + ''')'
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @table, @column
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT *
FROM #t
DROP TABLE #t
End
*********************************************************************************************
我需要查询整个数据库中某个特定值所在的表和字段,在网上搜到上面这个存储过程代码。在oracle sql developer中Run Statement通不过,报以下错误,求指导啊!Error report:
ORA-04050: invalid or missing procedure, function, or package name
04050. 00000 - "invalid or missing procedure, function, or package name"
*Cause: The required procedure, function, or package name is invalid
or missing.
*Action: Specify a valid name.
解决方案 »
- 关于一些命令,大家进来帮我解释一下....
- 如何大批量的往oracle中倒入数据?
- Linux 安装 oracle 求救....分值100,等着...
- oracle数据导入导出问题
- oracle和sqlserver在连接上的差别
- 我是Oracle 的初学者,只用过sql server,想学Oracle 8i请大家指教。不给分是小狗!
- 急求救:改动init.ora文件,8i就无法启动
- 我刚从sql server转到oracle来的,一个update就快疯了。
- 哪里可以较快速的下载JDeveloper 9.0.3、BI Bean 9.0.3?
- 字符集的问题
- oracle 统计数据
- 物化视图问题一个,大家多多帮忙
你确定这个是ORACLE的存储过程,而不是SQL SERVER的?
======================================================================
先解决此错误。
方括号,dbo,都是典型的sql server 语法。如果要用,首先去掉方括号,然后确定 oracle 中有 dbo 用户。