Home » SQL & PL/SQL » SQL & PL/SQL » can procedures created in sql server be converted into oracle (merged)
can procedures created in sql server be converted into oracle (merged) [message #302281] Mon, 25 February 2008 03:07 Go to next message
amitraidit
Messages: 10
Registered: January 2008
Location: gwalior
Junior Member

Hi,
i am in need of how to create procedure, as the previous procedures were created in sql server.
Re: creating stored procedure [message #302282 is a reply to message #302281] Mon, 25 February 2008 03:14 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
what exactly do you want?
.how to create a procedure in oracle? or
.convert an sql server procedure into oracle procedure.

regards,
Re: creating stored procedure [message #302283 is a reply to message #302281] Mon, 25 February 2008 03:19 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
create a procedure
http://www.techonthenet.com/oracle/procedures.php
how to store procedures in the database
http://www.orafaq.com/forum/m/293777/0/?srch=creating+stored+procedure#msg_293777

regards,
Re: creating stored procedure [message #302289 is a reply to message #302281] Mon, 25 February 2008 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You first have to read:
Application Developer's Guide - Fundamentals
PL/SQL User's Guide and Reference

Regards
Michel
can procedures created in sql server be converted into oracle [message #302301 is a reply to message #302281] Mon, 25 February 2008 04:04 Go to previous messageGo to next message
amitraidit
Messages: 10
Registered: January 2008
Location: gwalior
Junior Member

Hi,
i want to know can procedures in sql server be converted into oracle, if yes than how if no, than i have a big problem, as i in in erp field, and we use similar forms in different companies, i have no knowledge of oracle procedure, i aatached one procedure, alongwith the tables , please help me to solve this problem
regards.
Amit
  • Attachment: amit.sql
    (Size: 10.04KB, Downloaded 136 times)
Re: can procedures created in sql server be converted into oracle [message #302304 is a reply to message #302301] Mon, 25 February 2008 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't start a new topic, continue in the previous one.
You have to know:
- Oracle PL/SQL language
- the differences between Oracle and SQL Server mechanism
Or you can do bad work.

Regards
Michel
Re: can procedures created in sql server be converted into oracle [message #302308 is a reply to message #302301] Mon, 25 February 2008 04:09 Go to previous messageGo to next message
amitraidit
Messages: 10
Registered: January 2008
Location: gwalior
Junior Member

i want to know can sql server procedure can be tranferred into oracle
Re: can procedures created in sql server be converted into oracle [message #302311 is a reply to message #302308] Mon, 25 February 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, if you know how to do it.

Regards
Michel
Re: can procedures created in sql server be converted into oracle [message #302312 is a reply to message #302308] Mon, 25 February 2008 04:11 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
amitraidit wrote on Mon, 25 February 2008 10:09
i want to know can sql server procedure can be tranferred into oracle

1. Understand what the SQL server procedure does.
2. learn PL/SQL and transfer the logic from T-SQL to PL/SQL (or possibly into straight SQL)
Re: can procedures created in sql server be converted into oracle [message #302313 is a reply to message #302308] Mon, 25 February 2008 04:11 Go to previous messageGo to next message
amitraidit
Messages: 10
Registered: January 2008
Location: gwalior
Junior Member

i will give u the procedure which is in sql server.
CREATE PROCEDURE PROC_PSNoForIssue
@Type Varchar(100)
AS

BEGIN
if @Type = 'Inward Job Card'
SELECT distinct PS.PSCode, PS.BOMCode, BOM.WorkOrder
FROM ProductionSchedule PS INNER JOIN ProductionScheduleDetail PSD ON PS.PSCode = PSD.PSCode INNER JOIN JobCard JC ON PSD.PSCode = JC.PSCode AND PSD.JOB_RQN_NO = JC.JobCardNo LEFT OUTER JOIN BOMMaster BOM ON PS.BOMCode = BOM.BOMCode
WHERE JC.JobCardStatus = 'Inward Job Card' AND PSD.MaterialIssuedForJobCard = 0
ORDER BY PS.PSCode
else
SELECT DISTINCT PS.PSCode, PS.BOMCode, BOM.WorkOrder
FROM ProductionSchedule PS INNER JOIN
ProductionScheduleDetail PSD ON PS.PSCode = PSD.PSCode INNER JOIN
JobCard JC ON PSD.PSCode = JC.PSCode AND PSD.JOB_RQN_NO = JC.JobCardNo LEFT OUTER JOIN
BOMMaster BOM ON PS.BOMCode = BOM.BOMCode
WHERE (JC.JobCardStatus = 'Outward Job Card') AND (PSD.MaterialIssuedForJobCard = 0)
ORDER BY PS.PSCode
END

GO
Re: can procedures created in sql server be converted into oracle [message #302318 is a reply to message #302313] Mon, 25 February 2008 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks but we will not do your work for you.
The steps have been explained, the links have been posted, follow them.

In addition, just translated T-SQL to PL/SQL is NOT the good way. You have to know how both RDBMS work.

Regards
Michel

[Updated on: Mon, 25 February 2008 04:19]

Report message to a moderator

How to execute stored procedure of oracle through vb.net [message #312089 is a reply to message #302318] Mon, 07 April 2008 23:38 Go to previous messageGo to next message
amitraidit
Messages: 10
Registered: January 2008
Location: gwalior
Junior Member

I have problem while executing stored procedure of oracle 10g by using vb.net and i m also using recordset to return parameters as below:

strQuery = "EXECUTE PROC_PSNoForIssue('Inward Job Card')" ' " & Trim(gl_LoginBranchCode) & "')"

da.SelectCommand = New OracleCommand()
da.SelectCommand.Connection = db.getConnection()
da.SelectCommand.CommandText = strQuery
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.Fill(ds, "PSNoForIssue")

I will be very thankful to you, if you give me proper code for this.
Thanks..
Re: can procedures created in sql server be converted into oracle (merged) [message #312092 is a reply to message #302281] Mon, 07 April 2008 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>if you give me proper code for this.
"THIS"? I don't understand the question or the desired answer.
Re: How to execute stored procedure of oracle through vb.net [message #312122 is a reply to message #312089] Tue, 08 April 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is SQL forum not Java one.
Post your question in correct forum and don't hijack another topic.

Regards
Michel
Re: How to execute stored procedure of oracle through vb.net [message #312484 is a reply to message #312122] Wed, 09 April 2008 00:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Before posting in the java forum: that code is no java either!
Re: How to execute stored procedure of oracle through vb.net [message #312498 is a reply to message #312484] Wed, 09 April 2008 01:11 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ooops, sorry, read too fast, it seems to be VB one.
Anyway post in appropriate forum...

Regards
Michel
Previous Topic: Execute immediate
Next Topic: Problem with VARRAYs
Goto Forum:
  


Current Time: Sat Dec 10 11:13:53 CST 2016

Total time taken to generate the page: 0.10114 seconds