Topic: SQL*Plus and PL/SQL >> error in the procedure
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: error in the procedure |
| muzu |
Posted: Aug 21, 2007 12:53:45 PM |
Total Post: 37
Joined: Aug, 2007
|
I get the following type of errors in the procedure. May i know the
proper reason for it.
create table testindex (a number,b number,c number);
create sequence testseq1
start with 1
increment by 1
nocache
nocycle;
---The following procedure is for populating the
column a with distinct numbers from 1 to 50,
column b with the same value, starting from 1 until
it encounters multiples of 5 in column "a" then it increments to the next value.
column c with random numbers and fills null values in the column whenever
multiples of 5 is encountered in column "a".
-----
create or replace procedure populate
as
var1 number default 1;
var2 number default 0;
begin
for i in 1..50
loop
select dbms_random.value(1,100) into var2 from dual;
if(mod(i,5) != 0) then
insert into testindex values (testseq1.nextval,var1,var2);
if( mod(testseq1.currval,5) =0 ) then
var1 := var1 +1;
end if;
else
insert into testindex values (testseq1.nextval,var1,null);
if( mod(testseq1.currval,5) =0 ) then
var1 := var1 +1;
end if;
end if;
end loop;
end;
/
Errors for PROCEDURE POPULATE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
23/12 PL/SQL: Statement ignored
23/29 PLS-00357: Table,View Or Sequence reference 'TESTSEQ1.CURRVAL'
not allowed in this context
34/12 PL/SQL: Statement ignored
34/29 PLS-00357: Table,View Or Sequence reference 'TESTSEQ1.CURRVAL'
not allowed in this context
|
|
|
 Ramesh Joyisa |
| Posted: Aug 22, 2007 12:09:54 AM |  |
|
Total Post: 246
Joined: Aug, 2007
|
first get the sequence value to a variable using select seq.nextval into V1 from dual and then use in the insert statement
|
|
|
|
|
| muzu |
| Posted: Aug 22, 2007 01:34:18 AM | |
|
Total Post: 37
Joined: Aug, 2007
|
Is it that sequence.currval will not work in as any of the input parameters in a function ?
Why is it so that i have to get its value in a variable ?
Any reason ? Please let me know.
|
|
|
|
|
| Mitesh |
| Posted: Aug 28, 2007 05:07:12 AM | |
|
Total Post: 6
Joined: Aug, 2007
|
Hi,
After executed above example and i got above complie-errors, which u sent.
Because here Sequence is an Object same as table or view.
And if we want to use Table or View object then we have to specify in a SQL Statement.
e.g. we can not use direcly in statment like v_value := table_name.field name;
for that we have to use table_name.field_name into v_value in SELECT statement.
So if you want to use Sequence (testseq1), value (either currval or nextval0 then you must have to use SELECT SQL statement.
e.g. select testseq1.nextval into v_nextval from dual;
Thanks
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 387 |
| Vinoth Kumar | 379 |
| Gopu Gopi | 350 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
