Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2013
    Posts
    21

    Need help in fixing oracle stored procedure..

    we are migrating from SQL SERVER TO ORACLE. so im writing STORED PROCEDURE for oracle. i have the following code in SQL SERVER.
    select @ErrorCode= 3
    where @pManagedStatusCode = 'FALSE'
    and @OrganizationId != 0

    can i replace the above code in oracle stored procudure with

    IF pManagedStatusCode='FALSE' AND pOrganizationId !=0 THEN
    pErrorCode:=3;
    END IF;
    Last edited by u0_java; 02-13-13 at 15:16.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,621
    When I was learning SQL, I was taught that every SELECT clause required a FROM clause.

    is SELECT on SQL Server different?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Jan 2013
    Posts
    21
    Quote Originally Posted by anacedent View Post
    When I was learning SQL, I was taught that every SELECT clause required a FROM clause.

    is SELECT on SQL Server different?
    yup. this is the case in pl/sql statemnts.. and when it comes to sql for example SELECT GETDATE()

  4. #4
    Join Date
    Jan 2013
    Posts
    21
    Quote Originally Posted by anacedent View Post
    When I was learning SQL, I was taught that every SELECT clause required a FROM clause.

    is SELECT on SQL Server different?
    Assignment in Microsoft SQL Server is done using the SELECT statement
    PL/SQL assigns values to a variable as follows:

    It uses the assignment statement to assign the value of a variable or an expression to a local variable. It assigns a value from a database using the SELECT..INTO clause. But i want to avoid select ..into clause so im looking for alternative.. i think i made it clear now.

    thanks...

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,500
    Well, what happened when you tried your statement above? Did it work? Did it fail? Did it try to run away with the spoon?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    Oracle stored procedure code you posted in the first message suggests that IF checks two variables (or functions?); the first one (pmanagedstatuscode) being Boolean, another one (porganizationid) number. If the condition is TRUE, you are setting the third variable (perrorcode to 3). With Boolean, you don't need to specify its value so your code might evaluate to
    Code:
    if not pmanagedstatuscode and porganizationid <> 0 then
       perrorcode := 3;
    end if;
    Obviously, you don't need a SELECT statement to set PERRORCODE's value. However, if you insist, no problem either:
    Code:
    select 3 into perrorcode from dual;

  7. #7
    Join Date
    Jan 2013
    Posts
    21
    Quote Originally Posted by MCrowley View Post
    Well, what happened when you tried your statement above? Did it work? Did it fail? Did it try to run away with the spoon?
    yes. its compiled with no errors..(:

  8. #8
    Join Date
    Jan 2013
    Posts
    21
    Quote Originally Posted by Littlefoot View Post
    Oracle stored procedure code you posted in the first message suggests that IF checks two variables (or functions?); the first one (pmanagedstatuscode) being Boolean, another one (porganizationid) number. If the condition is TRUE, you are setting the third variable (perrorcode to 3). With Boolean, you don't need to specify its value so your code might evaluate to
    Code:
    if not pmanagedstatuscode and porganizationid <> 0 then
       perrorcode := 3;
    end if;
    Obviously, you don't need a SELECT statement to set PERRORCODE's value. However, if you insist, no problem either:
    Code:
    select 3 into perrorcode from dual;
    i appreciate your help. but PmanagedStatusCode is not boolean .it is varchar2 . so in that case we cant use the code you mentioned above.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    You know how it goes (if not, you'll learn): answers depend on quality of information you provide.

    In a message #7 you said that "it compiled with no errors ..(:". What does "..(:" mean? Is it a sad smiley? If so, what does it have to do with a (positive!) compilation outcome?

  10. #10
    Join Date
    Jan 2013
    Posts
    21
    Quote Originally Posted by Littlefoot View Post
    You know how it goes (if not, you'll learn): answers depend on quality of information you provide.

    In a message #7 you said that "it compiled with no errors ..(:". What does "..(:" mean? Is it a sad smiley? If so, what does it have to do with a (positive!) compilation outcome?

    yes .. its my fault not providing enough information. its not sad sad smiley . All i want is a better working code..sorry about that. Actually it is due to the naming conventions . from next time onwards i will provide complete information..

    Thank you very much...

Posting Permissions

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