Stored Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Stored Procedure

  1. #1
    Join Date
    Nov 2001
    Location
    Visalia, California
    Posts
    20

    Angry

    I'm new at using Stored Procedures, and here is my procedure. And I'm using SQL Plus, but I type this in and it just seems to give me a new line when I want the command to end. What do I need to do.

    Thanks.


    CREATE OR REPLACE PROCEDURE parcel_t1 (
    p_CityID ttt.CITY_ID,
    p_StreetName ttt.STREET_NAME),

    BEGIN
    SELECT CITY_ID,STREET_NAME FROM tttt
    WHERE CITY_ID = 'TC'
    -- Insert a new row in the parcel_t1 table
    INSERT INTO parcel_t1 (CityID, Street_Name)
    VALUES (CITY_ID, STREET_NAME));

    COMMIT;
    END parcel_t1;

  2. #2
    Join Date
    Sep 2001
    Posts
    15
    Probably you should enter '/' at first positon of the new line.
    CREATE OR REPLACE PROCEDURE parcel_t1 (
    p_CityID ttt.CITY_ID,
    p_StreetName ttt.STREET_NAME),

    BEGIN
    ...
    END parcel_t1;
    /

    Victor
    www.dynamicpsp.com

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by bduke
    I'm new at using Stored Procedures, and here is my procedure. And I'm using SQL Plus, but I type this in and it just seems to give me a new line when I want the command to end. What do I need to do.

    Thanks.


    CREATE OR REPLACE PROCEDURE parcel_t1 (
    p_CityID ttt.CITY_ID,
    p_StreetName ttt.STREET_NAME),

    BEGIN
    SELECT CITY_ID,STREET_NAME FROM tttt
    WHERE CITY_ID = 'TC'
    -- Insert a new row in the parcel_t1 table
    INSERT INTO parcel_t1 (CityID, Street_Name)
    VALUES (CITY_ID, STREET_NAME));

    COMMIT;
    END parcel_t1;
    Type / on the new line.
    But the procedure above will compile with errors.


  4. #4
    Join Date
    Oct 2001
    Posts
    13
    CREATE OR REPLACE PROCEDURE parcel_t1
    (
    p_CityID ttt.CITY_ID%TYPE,
    p_StreetName ttt.STREET_NAME%TYPE
    ) AS
    declare
    cursor co is
    SELECT CITY_ID,STREET_NAME
    FROM tttt
    WHERE CITY_ID = 'TC' ;
    -- Why did you hardcode the city_id. Logically, it should
    -- have been p_cityid... Am I right ?
    -- for instance it could have been
    -- cursor co is
    -- select city_id,street_name
    -- from ttt
    -- where city_id = p_city_id
    -- and street_name = p_streetname;

    Begin
    for i in co
    loop
    -- Insert a new row in the parcel_t1 table
    INSERT INTO parcel_t1 (CityID, Street_Name)
    VALUES (i.CITY_ID, i.STREET_NAME));
    end loop;
    COMMIT;
    END parcel_t1;
    /


    -- Note
    -- Do not forget to put '/' since it will tell SQL*PLUS to execute the script and hence compile the procedure. In case you have errors, use the following command



    To view one line, say line 4 use the command
    < line 1,10> , it will display first 10 lines. It is easier to find the errors this way since error at line 4 may be due to the fact the line 3 was incomplete

    Hope this will help

    Ally


  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    You can create a spool file to allow you to edit your
    procedure using your text editor.

    SQL>set heading off
    SQL>set underline off
    SQL>set pagesize 0
    SQL>spool c:\yourdir\yourproc.txt
    SQL>select text
    2 from user_source
    3 where name = 'PARCEL_T1';
    SQL>spool off


    edit c:\yourdir\yourproc.txt and remove the select statement above.

    make changes to your code and you can run that yourproc.txt by:
    SQL>@ c:\yourdir\yourproc.txt
    or
    SQL>start c:\yourdir\yourproc.txt

Posting Permissions

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


Click Here to Expand Forum to Full Width