Local Variables in Oracle

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
SoraxAsked:
Who is Participating?
 
Jinesh KamdarConnect With a Mentor Commented:
I havent tried it myself, but could you declare the 2 NCHAR variables as VARCHARs and then try again?

DECLARE

p_LocCode VARCHAR2(20) := 'CA';
p_PeriodType VARCHAR2(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;
0
 
Jinesh KamdarCommented:
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;
0
 
Jinesh KamdarCommented:
You could use VARCHAR2 for p_LocCode and p_PeriodType variables as well.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Steve WalesSenior Database AdministratorCommented:
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.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.

0
 
SoraxAuthor 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?
0
 
SoraxAuthor 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.
0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Yes, those statements should be equal.  And, Oracle procedures should work fine with the data values in variables, instead of as "hard-coded" literal values.  I rarely use procedures that have "hard-coded" values assigned to local variables.  Rather, I usually use Oracle PL\SQL procedures that have input parameters defined, then the SQL statement(s) in the procedure use these parameters as bind variables.

One other suggestions: I rarely use "implicit cursors" in PL\SQL like this example, which has: "select ... into... from ....".  I almost always use explicit cursors, since they do not raise either the "NO DATA FOUND" nor the "TOO MANY ROWS" exceptions, but they do require more lines of code.  Here is your procedure re-written with an explicit cursor instead:

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);
  cursor c1 is SELECT value FROM xPPMSSummary
    WHERE ProgId = 'BRIM' AND ReportType = 'BrimSold' AND FormulaNo = 989
        AND SoldDataFlag = 1AND TRUNC(xDate) = TRUNC(p_EndDate)
        AND FacilityId = p_LocCode AND PeriodType = p_PeriodType;
BEGIN
  open c1;
  fetch c1 INTO p_value;
  close c1;    
  DBMS_OUTPUT.PUT_LINE ('Value : ' || nvl(p_value,'NO DATA'));
END;
0
 
SoraxAuthor 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?
0
 
Mark GeerlingsDatabase AdministratorCommented:
"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.  
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
As a different approach, PL/SQL is not actually necessary to do this.  It can be accomplished like this:


variable p_loccode varchar2(20)
variable p_periodtype varchar2(20)
variable p_enddate varchar2(20)
begin
:p_loccode := 'CA';
:p_periodtype := 'W';
:p_enddate := '09/12/06';
end;
/
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 = to_date(:p_PeriodType, 'MM/DD/YY');

This also removed the DBMS_OUTPUT and other things you have to check in PL/SQL.
0
 
SoraxAuthor Commented:
I got it going. Thanks for all the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.