Solved

Need help converting some sql server sql to Oracle sql

Posted on 2016-10-09
9
70 Views
Last Modified: 2016-10-11
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
0
Comment
Question by:brgdotnet
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41836283
to search for table or view columns try one of these:

select * from dba_tab_columns where column_name = :searchstring
or
select * from dba_tab_cols where column_name = :searchstring

to find stored procedure parameters try

select * from dba_arguments where argument_name = :searchstring
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41836315
May be this you are looking for..

--

DECLARE
    pawan integer; -- declare
BEGIN
    pawan := 30;  --assign <<Value you want to search for >>
    
    dbms_output.Put_line(pawan); --display
    
    IF NOT EXISTS (SELECT * FROM YourTable WHERE Colname = :pawan)
    BEGIN
           .................
    END
    
END; 

--

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41836334
if used inside a pl/sql block, don't put the colon (:) in front of the bind variable.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41837098
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:brgdotnet
ID: 41837562
Thank you.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41837563
Pawan,

Still not Oracle syntax.  Please test against an actual database before posting.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41837564
brgdotnet,

What accept a post that isn't even valid syntax?
0
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 41839453
THank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PL/SQL Multiple PAYER_IDs with PAYER_TYPES are these types 3 38
Oracle regular expression 6 45
Oracle Subquery bad Join 11 59
Processing of multiple cursor 6 35
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now