1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

convert oracle to sql server

Discussion in 'SQL PL/SQL' started by karthikeyanc2003, Sep 18, 2009.

  1. karthikeyanc2003

    karthikeyanc2003 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    100
    hi folks,

    i have a follwing sp which i create in oracle but my client ask me to develop the same for ms sql server, since i have no idea about the sql server i need you peoples guidence to fullfill my client needs
    the code is as follows...

    CREATE OR REPLACE PROCEDURE validation
    AS
    TYPE t_t1 is table of NUMBER(2);
    t_a t_t1;
    i NUMBER ;
    BEGIN
    select Count(table1.user_name) BULK COLLECT INTO t_a
    from table1 ,table2
    where table1.user_name = table2.user_name;
    IF t_a(1)>0 THEN
    dbms_output.put_line('EXECUTION FAILS');
    ELSE
    dbms_output.put_line('SUCCESSFUL EXECUTION');
    END IF;
    END ;

    thanks in advance
    Karthikeyanc2003
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Maybe I'm missing something here, but you're returning a count(*), a single value, in a single row, to a COLLECTION? Why? A scalar variable works just as well:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE validation
      2  AS
      3          t_a NUMBER;
      4  BEGIN
      5          SELECT COUNT(table1.user_name) INTO t_a
      6          FROM table1 ,table2
      7          WHERE table1.user_name = table2.user_name;
      8          IF t_a>0 THEN
      9                  dbms_output.put_line('EXECUTION FAILS');
     10          ELSE
     11                  dbms_output.put_line('SUCCESSFUL EXECUTION');
     12          END IF;
     13  END ;
     14  /

    PROCEDURE created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> EXEC validation
    EXECUTION FAILS

    PL/SQL PROCEDURE successfully completed.

    SQL>
    Additionally you have a variable declared (i, as a NUMBER) which you fail to use. Along with that your assumption is your count will never be greater than 99; you'll find your procedure throws an error if that count is a 3 or more digit number:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE validation
      2  AS
      3  TYPE t_t1 IS TABLE OF NUMBER(2);
      4  t_a t_t1;
      5  i NUMBER ;
      6  BEGIN
      7          SELECT COUNT(table1.user_name) BULK COLLECT INTO t_a
      8          FROM table1 ,table2
      9          WHERE table1.user_name = table2.user_name;
     10          IF t_a(1)>0 THEN
     11                  dbms_output.put_line('EXECUTION FAILS');
     12          ELSE
     13                  dbms_output.put_line('SUCCESSFUL EXECUTION');
     14          END IF;
     15  END ;
     16  /

    PROCEDURE created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> EXEC validation
    BEGIN validation; END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: NUMERIC OR VALUE error: Bulk bind: Error IN define
    ORA-06512: at "BING.VALIDATION", line 7
    ORA-06512: at line 1


    SQL>
    It would probably better serve you to find a DBA who's familiar with both SQL Server and Oracle to assist with this.