Need help in fixing oracle stored procedure..

u0_java · #1 (**permalink**) 02-13-13, 15:08

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;

anacedent · #2 (**permalink**) 02-13-13, 15:19

When I was learning SQL, I was taught that every SELECT clause required a FROM clause.

is SELECT on SQL Server different?

u0_java · #3 (**permalink**) 02-13-13, 15:37

Quote:

Originally Posted by anacedent

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()

u0_java · #4 (**permalink**) 02-13-13, 15:45

Quote:

Originally Posted by anacedent

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

MCrowley · #5 (**permalink**) 02-13-13, 15:46

Well, what happened when you tried your statement above? Did it work? Did it fail? Did it try to run away with the spoon?

Littlefoot · #6 (**permalink**) 02-13-13, 16:00

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;

u0_java · #7 (**permalink**) 02-13-13, 16:14

Quote:

Originally Posted by MCrowley

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

u0_java · #8 (**permalink**) 02-13-13, 16:22

Quote:

Originally Posted by Littlefoot

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.

Littlefoot · #9 (**permalink**) 02-14-13, 00:58

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?

u0_java · #10 (**permalink**) 02-14-13, 08:55

Quote:

Originally Posted by Littlefoot

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

Internet Services

Web Development

Digital Marketing

Consumer Tech