| working with Sequence in stored procedures [message #9140] |
Tue, 21 October 2003 15:24  |
Lance Pris
Messages: 40
Registered: January 2002
|
Member |
|
|
am new to working with Oracle and have a question. I understand that orcale does not have a "autonumber" like other DataBases. So the best way to do it is using sequences.
I undestand how to create use and drop a sequence but when ever I try to put into a stored procedure I get an error.. I am fairly confident that it is the syntax of the stored procedure that I doing wrong I just can not find anything to help me..
CODE:
CREATE OR REPLACE procedure Incident_solution
AS
cursor_id integer;
execute_return_value integer;
last_function_code integer;
Begin
-- Truncate the PRIMUS_TEMP_DUMP table
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse (cursor_id, 'TRUNCATE TABLE TEMP_INCIDENT_SOLUTION', dbms_sql.native) ;
execute_return_value := dbms_sql.execute(cursor_id);
last_function_code := dbms_sql.last_sql_function_code;
dbms_sql.close_cursor(cursor_id);
--populate the temp table
INSERT INTO TEMP_INCIDENT_SOLUTION (ROW_ID, SOLUTION_ID, SECURETRAK_ID, OWNER, CREATED_DATE, TITLE)
(SELECT
row_id_seq.nextval,
a.pc_solution_id,
b.pc_incident_id,
d.pc_session_user_name,
d.pc_date,
substr(a.pc_title, 1, 60)
FROM
pt_solution a,
pt_incidentlink b,
pt_solution_eventdetails c,
pt_solution_event d,
pt_object_info e
WHERE
c.pc_name = upper('INCIDENT') and
c.pc_value1 = 'Siebel' and
a.pc_solution_uuid = b.pc_solution_uuid and
a.pc_secure_id = d.pc_sol_secure_id and
d.pc_event_id = c.pc_event_id and
d.pc_user_object_id = e.pc_object_id and
b.pc_incident_id = c.pc_value2);
--drop the sequence
drop sequence row_id_seq;
END;
/
IF I put either "drop sequence row_id_seq;" or "Create sequence row_id_seq;" in the Stored procedure It get a similer error:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:
begin declare end exception exit for goto if loop mod null
pragma raise return select update while <an identifier>
Any ideas would help.
Thanks you in advanced
|
|
|
|
|
|
| Re: working with Sequence in stored procedures [message #9142 is a reply to message #9140] |
Tue, 21 October 2003 16:34  |
Lance Pris
Messages: 40
Registered: January 2002
|
Member |
|
|
My understanding of Sequences if I do not drop and recreate then they will continue to grow in count values. Is that the wrong frame of thought?
I am using this sequence only to assign a unique row id to the table I am creating in this procedure.
If there was something similar to "truncate" or restart that is what I would want to use but could not find anything like that for sequences that is why I assumed I had to drop it after every time the procedure runs
Thank you
|
|
|
|
| Re: working with Sequence in stored procedures [message #9143 is a reply to message #9142] |
Tue, 21 October 2003 16:56 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Are you just looking to give each row inserted into the temp table a unique value starting at 1? If so, I wouldn't even suggest using a sequence. Just replace the sequence.nextval reference in your insert/select with ROWNUM.
Are you using DBMS_SQL because you are using a version earlier than 8i? If not, NDS (native dynamic SQL) would be easier to use here (execute immediate).
|
|
|
|
| Re: working with Sequence in stored procedures [message #9144 is a reply to message #9141] |
Tue, 21 October 2003 17:03 |
Lance Pris
Messages: 40
Registered: January 2002
|
Member |
|
|
I am useing version 8i
My understanding of Sequences if I do not drop and recreate then they will continue to grow in count values. Is that the wrong frame of thought?
I am using this sequence only to assign a unique row id to the table I am creating in this procedure.
If there was something similar to "truncate" or restart that is what I would want to use but could not find anything like that for sequences that is why I assumed I had to drop it after every time the procedure runs
Thank you
|
|
|
|
| Re: working with Sequence in stored procedures [message #9145 is a reply to message #9140] |
Tue, 21 October 2003 18:12 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> Is that the wrong frame of thought?
I'm afraid so. If you drop & create a sequence (or just about anything else for that matter) not only will you have to use dynamic SQL (which should be EXECUTE IMMEDIATE unless you have a good reason to do it the long way with DBMS_SQL), but all references to it in that or any other procedure will ALSO have to use dynamic code. Otherwise there is a dependency, and PL/SQL has to recompile when something it depends on changes (like getting dropped), so the procedure would invalidate itself at runtime.
There are ways to make a sequence cycle around back to 1, but it seems like Todd's ROWNUM suggestion would be a much simpler solution.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
