Stored Procedure

[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;

[victorp]

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;
/

[julian]

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.

[musaphura]

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

[reydp]

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