We help IT Professionals succeed at work.

Local Variables in Oracle

Sorax
Sorax asked
on
2,961 Views
Last Modified: 2013-12-07
I'm migrating an application from SQL Server to Oracle and am having trouble using local variables.  Simply, if someone could show me an equivalent statement that works in Oracle I would be grateful:
DECLARE @p_LocCode NCHAR(20)
DECLARE @p_PeriodType NCHAR(20)
DECLARE @p_EndDate DATETIME
SET @p_LocCode = 'CA'
SET @p_PeriodType = 'W'
SET @p_EndDate = '09/12/06'

SELECT VALUE
FROM xPPMSSummary
WHERE ProgId = 'BRIM' AND ReportType = 'BrimSold' AND FormulaNo = 989 AND SoldDataFlag = 1
AND xDate = @p_EndDate
AND FacilityId = @p_LocCode
AND PeriodType = @p_PeriodType


Thanks
Comment
Watch Question

DECLARE

p_LocCode NCHAR(20) := 'CA';
p_PeriodType NCHAR(20) = 'W';
p_EndDate DATE := TO_DATE('09/12/06','MM/DD/YY');
p_value VARCHAR2(1000);

BEGIN

SELECT value INTO p_value FROM xPPMSSummary
WHERE ProgId = 'BRIM' AND ReportType = 'BrimSold' AND FormulaNo = 989 AND SoldDataFlag = 1
AND TRUNC(xDate) = TRUNC(p_EndDate) AND FacilityId = p_LocCode AND PeriodType = p_PeriodType;

DBMS_OUTPUT.PUT_LINE ('Value : ' || p_value);

END;
You could use VARCHAR2 for p_LocCode and p_PeriodType variables as well.
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
In order to get output from the above out of SQL*PLus, be sure to specify "set serveroutput on" first, or he dbms_output.put_line output will not appear.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
You may be aware of this already, but the two databases use fundamentally different approaches for things like: record locks, read consistency, null values, date values, etc. For example, in SQL Server it is common to create "temp tables" in stored procedures.  By contrast in Oracle, you almost never need "temp tables" and if you do, they are created once (usually by a DBA) outside of PL\SQL, then used over and over by PL\SQL procedures, but they are usually not created in a procedure.

Basically, besides these facts:
1. both SQL Server and Oracle store data in tables made up of rows and columns;
2. they both support SQL statements
There are a lot more differences between them than similarities!  There have been numerous questions (and responses) on this topic on this site over the years.

Author

Commented:
Thanks for the responses. With the following query I'm receiving a "No Data Found" exception:
DECLARE
p_LocCode NCHAR(20) := 'CA';
p_PeriodType NCHAR(20) := 'W';
p_EndDate DATE := TO_DATE('09/12/06','MM/DD/YY');
p_value VARCHAR2(1000);
BEGIN
SELECT value INTO p_value FROM xPPMSSummary
WHERE ProgId = 'BRIM' AND ReportType = 'BrimSold' AND FormulaNo = 989 AND SoldDataFlag = 1
AND TRUNC(xDate) = TRUNC(p_EndDate) AND FacilityId = p_LocCode AND PeriodType = p_PeriodType
;    
DBMS_OUTPUT.PUT_LINE ('Value : ' || p_value);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
    p_value := 'No Data';
    DBMS_OUTPUT.PUT_LINE ('Value : ' || p_value);
END;

Output  = "Value: No Data"


But by replacing the SELECT statement with the following I get the correct output:
SELECT value INTO p_value FROM xPPMSSummary
WHERE ProgId = 'BRIM' AND ReportType = 'BrimSold' AND FormulaNo = 989 AND SoldDataFlag = 1
AND TRUNC(xDate) = TO_DATE('09/12/06','MM/DD/YY') AND FacilityId = 'CA' AND PeriodType = 'W';

Its my understanding that these should be equal statements. What is the cause of this discrepancy?

Author

Commented:
Markgeer:
Thanks for the comments. I have come across many differences during this project. NULLs, stored procedures, case sensitivity and brackets being used for regular expressions instead of delimiters is a favorite of mine. I only have cursory knowledge of the differences in record locking and read consistency so I think I have some homework. Its been an interesting project and I always enjoy improving my skill set.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the rewrite. I'll be sure to take advantage of Explicit Cursors in the future.
I'm still receiving "Value: NO DATA". The cursor defined as follows works:
cursor c1 is SELECT value FROM xPPMSSummary
    WHERE ProgId = 'BRIM' AND ReportType = 'BrimSold' AND FormulaNo = 989
        AND SoldDataFlag = 1;

Of course without the variables this has no practical purpose. Is there a session variable I have to set?
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
"Is there a session variable I have to set?"
No.  I can't say that I've ever heard the phrase "session variable" in an Oracle context.  
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I got it going. Thanks for all the help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.