| pl/sql stored procedure errors [message #21699] |
Fri, 23 August 2002 07:37  |
Jyothi
Messages: 12
Registered: May 2002
|
Junior Member |
|
|
Iam new to oracle (pl/sql).when Iam creating a stored procedure for select statement,I have selected into clause in the select statement.Is it correct.I looked through many problems faced with this in the forums too.Even i tried with those I am not able to slove it.
Iam getting the following errors as mentioned below.
1 create or replace procedure test1 as
2 declare
3 idno catalog%rowtype;
4 begin
5 select *
6 into idno
7 from catalog;
8* end;
L> /
errors occuring:
1.PLS-00103: Encountered the symbol "DECLARE" when expecting one of
the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form current external language
The symbol "begin" was substituted for "DECLARE" to continue.
2. PLS-00103: Encountered the symbol "end-of-file" 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>
3. <a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
I will appreciate if anyone could help me out with this.
thank you,
jyothi
|
|
|
|
|
|
| Re: pl/sql stored procedure errors [message #21705 is a reply to message #21702] |
Fri, 23 August 2002 10:09  |
Jyothi
Messages: 12
Registered: May 2002
|
Junior Member |
|
|
What this error actually mean.From oracle it showed that PL/SQL procedure successfully completed.But it didnt shown any results.From the front end when iam trying to retrieve it ,its giving the following error.
the error is:
ORA-06550: line 1, column 25: :PLS-00103: Encountered the symbol "END" when expecting one of the following: : : := . ( @ % ; :The symbol ";" was substituted for "END" to continue. :-6550
sorry to trouble u.Iam learning oracle.
Thank You,
jyothi
|
|
|
|
| Re: pl/sql stored procedure errors [message #21718 is a reply to message #21702] |
Sun, 25 August 2002 08:17 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
To get a result returned by a pl/sql block, you should define it as a function, rather than just a procedure. As you should be aware of, a procedure does not return any value (you can try with OUT parameter to get a value back). To return a value calling a pl/sql block, better you define it is a function with assigning a result into the returning variable (using := ) and return it.
In fact, that is what your error message is saying. It is expecting := operator to return a value, where it did not find in your procedure. Try the following one.
Assuming your SELECT statement returns ONLY ONE row, I am giving following example. But if you need to give a SELECT that returns more than one row, the you SHOULD write a pl/sql block with CURSOR, but not simple SELECT return. Check the following.
SQL> ed
Wrote file afiedt.buf
1 create or replace function myp_test_func
2 return varchar
3 is
4 idno varchar2(2000);
5 begin
6 select eno||','||enam||','||eg||','||dept||','||salary into idno from myp_employee
7 where rownum=1;
8 return (idno);
9* end;
10 /
Function created.
SQL> select myp_test_func from dual;
MYP_TEST_PROC
--------------------------------------------------------------------------------
101,Steven,A,TECH,3240
SQL>
If you still need help, come back with exactly what is your criteria and sample data.
Good luck :)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
