-
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;
-
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|