Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: Convert Microsoft SQL PROCEDURE to Oracle PROCEDURE

    Hello all,

    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.

    The SQL Server PROCEDURE is as follows:

    set quoted_identifier on
    GO

    /****** Object: Stored Procedure dbo.spc_QCID_count Script Date: 8/24/04 3:56:19 PM ******/
    CREATE PROC spc_QCID_count
    @QC_ID float = NULL
    AS

    /* Force user to enter a QC_ID */
    IF @QC_ID = NULL
    BEGIN
    PRINT 'You must enter a QC_ID'
    RETURN
    END

    SET NOCOUNT ON

    select 'tblQC_IDS',count(*) from tblQC_IDS where QCID = @QC_ID

    select 'bchCALC',count(*) from bchCALC where QCID = @QC_ID

    select 'bchGWD',count(*) from bchGWD where QCID = @QC_ID

    select 'bchLDI',count(*) from bchLDI where QCID = @QC_ID

    select 'bchLTD',count(*) from bchLTD where QCID = @QC_ID

    select 'bchWCI',count(*) from bchWCI,bchLDI
    where bchWCI.LINK = bchLDI.MSLINK and bchLDI.QCID = @QC_ID

    select 'bchSAMP',count(*) from bchSAMP where QCID = @QC_ID

    select 'bchTEST',count(*) from bchSAMP,bchTEST
    where bchSAMP.SAMPLE_ID = bchTEST.SAMPLE_ID and
    bchSAMP.QCID = @QC_ID

    select 'bchRES',count(*) from bchSAMP,bchTEST,bchRES
    where bchSAMP.SAMPLE_ID = bchTEST.SAMPLE_ID and
    bchTEST.TEST_ID = bchRES.TEST_ID and
    bchSAMP.QCID = @QC_ID
    GO

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Ok..

    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..

    Code:
    create or replace procedure test_procedure
    (p_param1 in varchar2)
    is
    
    v_holding_variable number;  -- hold count
    
    begin
    
    select count(*) 
    into v_holding_variable from
    bchSAMP,bchTEST 
    where bchSAMP.SAMPLE_ID = bchTEST.SAMPLE_ID and
    bchSAMP.QCID = p_param1;
    
    dbms_output.put_line(v_holding_variable);
    
    end;
    then to call this you would do something like this
    test_procedure(31);

    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

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Haven't purchased the tool, but you might want to look into:

    http://www.swissql.com./sql-server-to-oracle.html

    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •