Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Local Variables in Oracle

Posted on 2007-09-28
12
Medium Priority
?
2,922 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
0
Comment
Question by:Sorax
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 19979873
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 19979883
You could use VARCHAR2 for p_LocCode and p_PeriodType variables as well.
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 19979921
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19980163
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
 

Author Comment

by:Sorax
ID: 19980650
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
 

Author Comment

by:Sorax
ID: 19980726
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 800 total points
ID: 19980730
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
 

Author Comment

by:Sorax
ID: 19980985
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19981012
"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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 200 total points
ID: 19981310
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
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 1000 total points
ID: 19983503
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
 

Author Comment

by:Sorax
ID: 19992177
I got it going. Thanks for all the help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question