Unanswered: Convert Microsoft SQL PROCEDURE to Oracle PROCEDURE
I am a MCDBA. We are in the process of migrating our databases from a SQL Server to an Oracle server. The following SQL Server procedure is one of the examples of the problems I've had with the migration. Would anyone advise how to convert the following to a working Oracle procedure? ANY aid would be greatly appreciated.
The following SQL Server PROCEDURE calls for a user to enter a number. After the user enters a number, a query will execute that will perform a count on each of the tables within the database of the records that contain the entered number, then output the results.
First of all Oracle PL/SQL procedures are not really meant to handle user-interaction (ie supplying a value).
But the format would look something like this..
create or replace procedure test_procedure
(p_param1 in varchar2)
v_holding_variable number; -- hold count
into v_holding_variable from
where bchSAMP.SAMPLE_ID = bchTEST.SAMPLE_ID and
bchSAMP.QCID = p_param1;
then to call this you would do something like this
Where 31 would be the id supplied by the end user. Make sure before you run this that you set output on so you can see the dbms_output on the screen.
This is the "general" format of a procedure. By no means the only way to do it - read up on Oracle cursors and procedures, but it should be a good start.
Last edited by ss659; 09-09-04 at 12:03.
Reason: Add comment