Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: variable assignment

    I am converting parts of a Sybase DB to Oracle. I am trying to figure out how to assign a value from a select query (returns only one record) to a variable like so.
    Code:
    
    DECLARE
    lng NUMBER(10,5);
    BEGIN
    lng := ((SELECT DISTINCT lng FROM lkup_zipcodes WHERE zip_code=@zipcode)*180)/PI();
    END;
    
    Can this be done in Oracle?
    What I found on google I can use ":=" to assign a variable.
    As you can probably tell by all of my post in this forum I really don't know anything about Oracle.

    Thanks in advance for you help.

  2. #2
    Join Date
    Jan 2003
    Posts
    67
    Well this *seems* to be working. Only thing is I don't know how to display the variable. Sybase it's just a: "Select varname" and done.

    Code:
    DECLARE
    alng FLOAT;
    azipcode VARCHAR2(64);
    BEGIN
    azipcode:='19464';
    SELECT DISTINCT LONGITUDE INTO alng FROM W6ADMIN.W6ZIPDATA WHERE zipcode=azipcode;
    END;
    thanks

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    To display values from PL/SQL, call the procedure put_line from the package dbms_output. This procedure needs a string as input and will display the string when the your code is run on the SQL*Plus prompt AND serveroutput is on.

    You can also assign values in the declaration section, when it also will be the same value. Otherwise (when the value changes) woul will want to work with variables.

    Your code can be like this :

    DECLARE
    alng FLOAT;
    azipcode VARCHAR2(64) := '19464';
    BEGIN
    SELECT DISTINCT LONGITUDE INTO alng FROM W6ADMIN.W6ZIPDATA WHERE zipcode=azipcode;
    dbms_output.put_line('Value of alng: '||alng);
    END;

    Remember when you do this at the SQL*Plus prompt, be sure to type :
    set serveroutput on size 1000000
    before running the script.

    You can also use a cursor. Cod will be like this

    declare
    cursor c_long (bv_zipcode varchar2) is
    select distinct longitude
    from w6admin.w6zipdata
    where zipcode= bv_zipcode;
    azipcode VARCHAR2(64) := '19464';
    alng FLOAT;
    BEGIN
    open c_long ( azipcode);
    fetch c_long into alng;
    close c_long;
    dbms_output.put_line('Value of longitude: '||alng);
    END;

    You can also put this in a (stored) procedure with a parameter to use it for every zipcode you need.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Jan 2003
    Posts
    67

    Thumbs up

    Wow that's great information to have. Thanks for thaking the time to put this together!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •