If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Need help in fixing oracle stored procedure..

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,595
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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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()
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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...
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,458
Well, what happened when you tried your statement above? Did it work? Did it fail? Did it try to run away with the spoon?
Reply With Quote
  #6 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,998
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;
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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..(:
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #9 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,998
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?
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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...
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On