Results 1 to 3 of 3

Thread: new to pl/sql

  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: new to pl/sql

    I have to build an app using Oracle for the first time. I do have Sybase and SQL2000 experience. So I am playing around trying to test how to build tables and stored procedures. I was able to create the table successfully...

    Code:
    CREATE TABLE delemelatertable 
    (
        ZIPCODE   VARCHAR2(64) NOT NULL,
        LONGITUDE FLOAT        NOT NULL,
        LATITUDE  FLOAT        NOT NULL
    )
    But I can't get the SP below to work. It looks to me like I have the SQL exactly right. The message I get is: "PL/SQL Statement Ignored" and the SP is created but invalid....

    Code:
    CREATE OR REPLACE PROCEDURE DELETEMELATER3
    AS
    BEGIN
    SELECT ZIPCODE, LONGITUDE, LATITUDE FROM TMOSER.DELEMELATERTABLE;
    END;
    Please let me know what you think.

    Thanks in advance for your help.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    Every 'SELECT' within a SP will return values into variables, therefore you would have to code something llike this:

    Code:
    
    CREATE OR REPLACE PROCEDURE DELETEMELATER3
    (P_ZIPCODE VARCHAR2
    ,P_LONG OUT FLOAT
    ,P_LAT OUT FLOAT)
    AS
    BEGIN
    SELECT LONGITUDE, LATITUDE 
        INTO P_LONG, P_LAT  
       FROM TMOSER.DELEMELATERTABLE
     WHERE ZIPCODE = P_ZIPCODE;
    END;
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2003
    Posts
    67

    Thanks

    Originally posted by LKBrwn_DBA
    Every 'SELECT' within a SP will return values into variables, therefore you would have to code something llike this:

    Code:
    
    CREATE OR REPLACE PROCEDURE DELETEMELATER3
    (P_ZIPCODE VARCHAR2
    ,P_LONG OUT FLOAT
    ,P_LAT OUT FLOAT)
    AS
    BEGIN
    SELECT LONGITUDE, LATITUDE 
        INTO P_LONG, P_LAT  
       FROM TMOSER.DELEMELATERTABLE
     WHERE ZIPCODE = P_ZIPCODE;
    END;
    

    Thanks this works (compiles) now I just need to figure out the methods of creating recordset using an SP.

Posting Permissions

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