473,320 Members | 2,080 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Convert Oracle PLSQL to SQL Server TSQL !

1
Hello all,

I am new to convert PLSQL to TSQL. Any expertise in SQL, please help me to do convert the belowed PLSQL procedure to SP in SQL server. I really appreciate your time. Thanks in advance.

==============================================

PROCEDURE SaveTestAs(P_ProgramID NUMBER,
P_ProgramName VARCHAR2)

IS

L_TestId INTEGER := 0;

CURSOR L_Program IS
SELECT *
FROM PROGRAMS
WHERE PROGRAM_ID = P_ProgramID;

CURSOR L_ProgramTest IS
SELECT *
FROM PROGRAM_TEST
WHERE PROGRAM_ID = P_ProgramID;


CURSOR L_ProgramNums(C_TestID IN NUMBER) IS
SELECT *
FROM PROGRAM_NUMERICS
WHERE TEST_ID = C_TestId;

CURSOR L_ProgramStr(C_TestID IN NUMBER) IS
SELECT *
FROM PROGRAM_STRINGS
WHERE TEST_ID = C_TestID;

BEGIN


FOR R_Programs IN L_Program LOOP

INSERT INTO PROGRAMS
VALUES(PROGRAMID.NEXTVAL,
P_ProgramName,
R_Programs.Program_Description,
SYSDATE,
R_Programs.Created_By,
'',
R_Programs.Station_NO);

END LOOP;

FOR R_PT IN L_ProgramTest LOOP

INSERT INTO PROGRAM_TEST
VALUES(TESTID.NEXTVAL,
PROGRAMID.CURRVAL,
R_PT.TEST_NUMBER,
R_PT.TESTDEF_ID,
R_PT.FAIL_MESSAGE,
R_PT.FAIL_HELPFILE,
R_PT.ACTIVE,
R_PT.CONTINUE_ON_FAIL);

FOR R_PN IN L_ProgramNums(R_PT.TEST_ID) LOOP

INSERT INTO PROGRAM_NUMERICS
VALUES(NUMERIC_ID.NEXTVAL,
TESTID.CURRVAL,
R_PN.NUM_PARAM_NUM,
R_PN.NUMERIC_PARAMETER,
R_PN.HELP_DESCRIPTION);

END LOOP;

FOR R_PS IN L_ProgramStr(R_PT.TEST_ID) LOOP

INSERT INTO PROGRAM_STRINGS
VALUES(STRING_ID.NEXTVAL,
TESTID.CURRVAL,
R_PS.STRING_PARAM_NUM,
R_PS.STRING_PARAMETER,
R_PS.HELP_DESCRIPTION);

END LOOP;

END LOOP;


END SaveTestAs;
Nov 17 '08 #1
1 7023
debasisdas
8,127 Expert 4TB
you can use any third party control or convert the code by yourself.
Nov 17 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

35
by: English Teacher | last post by:
Which would be a more useful relational database server to learn nowadays: MS SQL SERVER or ORACLE? Thanks!
14
by: tristant | last post by:
Hi All, Our client has just decided to migrate from SQLServer2k to Oracle 9i on AIX. We are considering what development tool / language as the application/ front end platform. We almost...
5
by: Michael Rudolph | last post by:
Hi newsgroup, I have an issue with the configuration of a DB2 federated database (WebSphere Information Integrator) in conjunction with the relational wrapper for Oracle on AIX. DB2 seems to not...
5
by: dreadnought8 | last post by:
I've worked with mdbs, and with SQL Server to a lesser extent, with Access as a front end, on commercial-strength systems for quite a while, starting with A97. The last 8 months or so, I've been...
1
by: jobs | last post by:
I am able to access 9i and 10g Oracle DBs from other clients on my pc - sqlplus, PLSQL Developer, etc. As a simple test I just wanted to connect and access the same oracle DBs through ASP.NET on...
0
by: anuptosh | last post by:
Hi, I have been trying to run the below example to get a Oracle Array as an output from a Java code. This is an example I have found on the web. But, the expected result is that the code should...
0
by: tickle | last post by:
Need to convert this PL/SQL script to Dynamic SQL Method 2 * copybook - celg02u3.sql SIR 24265 * * updates dt_deny for all rows in * * ...
2
by: singhjih | last post by:
The table looks like the following: Code ------------------- INSTRUCTOR_ID(PK) NUMBER(8,0) NOT NULL SALUTATION VARCHAR2(5) NULL FIRST_NAME VARCHAR2(25) NULL LAST_NAME ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.