brgdotnet
asked on
Need help converting some sql server sql to Oracle sql
I need to convert the sql server sql below to Oracle sql. I am mostly stuck because I can't find an equivalent conversion for "syscolumns"
Does anyone know of an equivalent query for SELECT * FROM syscolumns ?
IF NOT EXISTS (SELECT * FROM syscolumns WHERE name = @searchString)
BEGIN
END
Does anyone know of an equivalent query for SELECT * FROM syscolumns ?
IF NOT EXISTS (SELECT * FROM syscolumns WHERE name = @searchString)
BEGIN
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if used inside a pl/sql block, don't put the colon (:) in front of the bind variable.
Since I don't know SQL Server, I have to guess at what "syscolumns" means in a SQL Server system. From sdstuber's comments, it appears to contain data similar to what these three views contain in an Oracle system:
dba_tab_columns
all_tab_columns
user_tab_columns
Depending on which Oracle schema your Oracle procedure will be in, and on what privileges that schema has, you may want to use one or another of these "data dictionary views". The dfference between these various views in Oracle is:
1. the "dba_..." objects include all objects in the database.
2. the "all_..." objects include the objects your schema is allowed to see
3. the "user_..." objects include only those objects in your schema
Oracle databases includes a number of these "data dictionary views" including:
dba/all/user_tables
dba/all/user_tab_columns
dba/all/user_indexes
dba/all/user_ind_columns
dba/all/user_objects
dba/all/user_constraints
etc.
(Your schema may or may not have permission to use these objects that start with "dba_...".)
Something to watch out for in converting SQL Server code to Oracle code is "temp" tables that are commonly created dynamically in SQL Server stored procedures. This concept is usually not needed in Oracle stored procedures. Oracle supports "global temporary tables". These can be helpful for some data processing tasks. But in Oracle these are not created dynamically at runtime. In Oracle these are created once by a DBA, then stored procedures can be written that use them.
dba_tab_columns
all_tab_columns
user_tab_columns
Depending on which Oracle schema your Oracle procedure will be in, and on what privileges that schema has, you may want to use one or another of these "data dictionary views". The dfference between these various views in Oracle is:
1. the "dba_..." objects include all objects in the database.
2. the "all_..." objects include the objects your schema is allowed to see
3. the "user_..." objects include only those objects in your schema
Oracle databases includes a number of these "data dictionary views" including:
dba/all/user_tables
dba/all/user_tab_columns
dba/all/user_indexes
dba/all/user_ind_columns
dba/all/user_objects
dba/all/user_constraints
etc.
(Your schema may or may not have permission to use these objects that start with "dba_...".)
Something to watch out for in converting SQL Server code to Oracle code is "temp" tables that are commonly created dynamically in SQL Server stored procedures. This concept is usually not needed in Oracle stored procedures. Oracle supports "global temporary tables". These can be helpful for some data processing tasks. But in Oracle these are not created dynamically at runtime. In Oracle these are created once by a DBA, then stored procedures can be written that use them.
ASKER
Thank you.
Pawan,
Still not Oracle syntax. Please test against an actual database before posting.
Still not Oracle syntax. Please test against an actual database before posting.
brgdotnet,
What accept a post that isn't even valid syntax?
What accept a post that isn't even valid syntax?
ASKER
THank you
Open in new window