Skip to Main Content
  • Questions
  • Creating dynamic stored procedure problem

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Boris.

Asked: July 01, 2002 - 5:04 pm UTC

Answered by: Tom Kyte - Last updated: February 16, 2017 - 3:37 am UTC

Category: SQL*Plus - Version: 8.0

Viewed 10K+ times! This question is

You Asked

Here is my first dynamic stored procedure and I have some errors when trying to compile it. Please help. I am new in ORACLE. Thanks a lot

Line 12 PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char binary national character ncharThe symbol "<an identifier>" was substituted for "=" to continue.

Line 14 PLS-00103: Encountered the symbol "." when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char binary national character ncharThe symbol "<an identifier>" was substituted for "." to continue.

Line 15 PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> cursor form currentThe symbol "begin" was substituted for "IF" to continue.

Line 47 PLS-00103: Encountered the symbol "SQLQUERY" when expecting one of the following: select

Line 55 PLS-00103: Encountered the symbol "/" when expecting one of the following: begin function package pragma procedure form external

CREATE OR REPLACE PROCEDURE SMKT.Account_Search
(p_searchname in varchar2,
p_sname in varchar2,
p_searchnumber in varchar2,
p_snumber in varchar2)
AS
BEGIN
DECLARE
SQLQuery varchar2 (4000) :='';
TYPE ACCOUNT_CUR IS REF CURSOR;
CUR ACCOUNT_CUR;
ACCOUNTREC ACCOUNT%ROWTYPE;
--Line 12
SQLQuery := 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT ';

--Line 14
DBMS_OUTPUT.PUT_LINE('HI IF' || ACCOUNT);
--Line 15
IF p_sname <>'' THEN
DBMS_OUTPUT.PUT_LINE('HI IF');
IF p_searchname='1' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE '''|| p_sname || '%''';
END IF;
IF p_searchname='2' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE ''%'|| p_sname || '''';
END IF;
IF p_searchname='3' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE ''%'|| p_sname || '%''';
END IF;
IF p_searchname='4' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME = '''|| p_sname || '''';
END IF;
END IF;
IF p_snumber <>'' THEN
DBMS_OUTPUT.PUT_LINE('HI IF');
IF p_searchnumber='1' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE '''|| p_snumber || '%''';
END IF;
IF p_searchnumber='2' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE ''%'|| p_snumber || '''';
END IF;
IF p_searchnumber='3' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE ''%'|| p_snumber || '%''';
END IF;
IF p_searchnumber='4' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE = '''|| p_snumber || '''';
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE('SQLQuery'||SQLQuery);


--Line 47
OPEN CUR FOR SQLQuery;
LOOP
FETCH CUR INTO ACCOUNTREC;
EXIT WHEN CUR%NOTFOUND;
END LOOP;
CLOSE CUR;

END;

--Line 55
/


and we said...

line 12 is an assignment (executable code) but you have it in a declare block.

You should lose the declare block and just put the variable declares after the AS, before the BEGIN:

...
p_snumber in varchar2)
AS

SQLQuery varchar2 (4000) :='';
TYPE ACCOUNT_CUR IS REF CURSOR;
CUR ACCOUNT_CUR;
ACCOUNTREC ACCOUNT%ROWTYPE;

BEGIN
--Line 12
SQLQuery := 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT ';
....

the rest of the code is equally affected by being in a declare block, not in a begin/end block.


And, since you are just learning -- I have the golden opportunity to make sure you do not make the MOST BASIC BUT TERRIBLE MISTAKE you could ever make. You are not using bind variables and they are SOOOO important -- you will not BELIEVE how important.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

shows you exactly how to accomplish your task from start to finish!



and you rated our response

  (29 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Creating dynamic stored procedure problem

July 02, 2002 - 8:57 am UTC

Reviewer: Boris Cherkalin from New York

It was very useful but I still have some minor problems and if you can may be you could help me because I can not find an answer to this errors.

57 PLS-00103: Encountered the symbol "SQLQUERY" when expecting one of the following: select

70 PLS-00103: Encountered the symbol "/" when expecting one of the following: begin function package pragma procedure form external

CREATE OR REPLACE PROCEDURE SMKT.Account_Search (p_searchname in varchar2 :='1',
p_sname in varchar2 :='',
p_searchnumber in varchar2 :='1',
p_snumber in varchar2 :='')
AS

TYPE rc IS REF CURSOR;
l_cursor rc;
SQLQuery varchar2(4000) default 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT ';
cursor l_template is SELECT ACCOUNT_CODE FROM INTL_ACCOUNT;
l_rec l_template%rowtype;

BEGIN
SQLQuery := 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT ';
--SQLQuery :='SELECT ACCOUNT_CODE,ACCOUNT_NAME,AFFILIATE_NAME,ACCOUNT_NUMBER,EFFECTIVE_START_DT,EFFECTIVE_END_DT,FORECAST_IND,ACTUAL_IND,REF_RLI_COUNTRY_CODE FROM INTL_ACCOUNT '
DBMS_OUTPUT.PUT_LINE('HI IF' || ACCOUNT);
IF p_sname <>'' THEN
DBMS_OUTPUT.PUT_LINE('HI IF');
IF p_searchname='1' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE '''|| p_sname || '%''';
END IF;
IF p_searchname='2' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE ''%'|| p_sname || '''';
END IF;
IF p_searchname='3' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE ''%'|| p_sname || '%''';
END IF;
IF p_searchname='4' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME = '''|| p_sname || '''';
END IF;
END IF;
IF p_snumber <>'' THEN
DBMS_OUTPUT.PUT_LINE('HI IF');
IF p_searchnumber='1' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE '''|| p_snumber || '%''';
END IF;
IF p_searchnumber='2' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE ''%'|| p_snumber || '''';
END IF;
IF p_searchnumber='3' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE ''%'|| p_snumber || '%''';
END IF;
IF p_searchnumber='4' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE = '''|| p_snumber || '''';
END IF;
END IF;
--DBMS_OUTPUT.PUT_LINE('SQLQuery'||SQLQuery);
p(SQLQuery);
--line 57
open l_cursor for SQLQuery;
LOOP
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(SQLQuery);
END LOOP;
close l_cursor;
END;
--line 70
/




Tom Kyte

Followup  

July 02, 2002 - 10:59 am UTC

My suggestion:

a) erase this code
b) follow the link to the code that does this and does it using binds so that it does it efficiently.

You have quite a few problems with this code:

1) DBMS_OUTPUT.PUT_LINE('HI IF' || ACCOUNT);

what is account?  it is not defined anywhere.  That bombs.

2)  IF p_sname <>'' THEN  is a bug.  You must use "if p_sname is not null then"

consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p( p_data in varchar2 := '' )
  2  is
  3  begin
  4          if ( p_data <> '' )
  5          then
  6                  dbms_output.put_line( 'not equal' );
  7          elsif ( p_data = '' )
  8          then
  9                  dbms_output.put_line( 'equal' );
 10          else
 11                  dbms_output.put_line( 'gee, I just don''t know' );
 12          end if;
 13  end;
 14  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p
gee, I just don't know

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p( null );
gee, I just don't know

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p( 'some stuff' );
gee, I just don't know

PL/SQL procedure successfully completed.


Using trivalued logic and the fact that '' is a NULL, x <> '' is never TRUE (or FALSE), it is UNKNOWN.

3) SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE '''|| p_sname || '%''';

does not use binds and that is SO important that I won't even look at fixing this code.  It needs to be FIXED to use binds more then anything else in the world.

 

Creating dynamic stored procedure problem

July 02, 2002 - 11:34 am UTC

Reviewer: Boris from New York

Sorry Tom.
I re-created stored procedure based on example you gave me with a bind variables but stil have same problems. I am running Oracle 8.0

Line 82 PLS-00103: Encountered the symbol "L_QUERY" when expecting one of the following: select

line 95 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function package pragma procedure form external

Please help
Thanks a lot

CREATE OR REPLACE PROCEDURE SMKT.Account_Search_By
( p_sname in varchar2 default NULL,
p_snumber in varchar2 default NULL,
p_searchname in varchar2 default '1',
p_searchnumber in varchar2 default '1')

as
type rc is REF CURSOR;

l_cursor rc;
l_query varchar2(512)
default 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null ';

cursor l_template is SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null;
l_rec l_template%rowtype;
begin

if ( p_sname is NOT NULL ) then
IF p_searchname='1' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_NAME',
''||p_sname||'%');
l_query := l_query ||
' and ACCOUNT_NAME like
sys_context( ''MY_CTX'', ''ACCOUNT_NAME'' ) ';
end if;
IF p_searchname='2' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_NAME',
'%'||p_sname||'');
l_query := l_query ||
' and ACCOUNT_NAME like
sys_context( ''MY_CTX'', ''ACCOUNT_NAME'' ) ';
end if;
IF p_searchname='3' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_NAME',
'%'||p_sname||'%');
l_query := l_query ||
' and ACCOUNT_NAME like
sys_context( ''MY_CTX'', ''ACCOUNT_NAME'' ) ';
end if;
IF p_searchname='4' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_NAME',
''||p_sname||'');
l_query := l_query ||
' and ACCOUNT_NAME =
sys_context( ''MY_CTX'', ''ACCOUNT_NAME'' ) ';
end if;
end if;

if ( p_snumber is NOT NULL ) then
IF p_searchname='1' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_CODE',
''||p_snumber||'%');
l_query := l_query ||
' and ACCOUNT_CODE like
sys_context( ''MY_CTX'', ''ACCOUNT_CODE'' ) ';
end if;
IF p_searchname='2' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_CODE',
'%'||p_snumber||'');
l_query := l_query ||
' and ACCOUNT_CODE like
sys_context( ''MY_CTX'', ''ACCOUNT_CODE'' ) ';
end if;
IF p_searchname='3' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_CODE',
'%'||p_snumber||'%');
l_query := l_query ||
' and ACCOUNT_CODE like
sys_context( ''MY_CTX'', ''ACCOUNT_CODE'' ) ';
end if;
IF p_searchname='4' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_CODE',
''||p_snumber||'');
l_query := l_query ||
' and ACCOUNT_CODE =
sys_context( ''MY_CTX'', ''ACCOUNT_CODE'' ) ';
end if;
end if;

--Print( l_query );

open l_cursor for l_query;

loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;

dbms_output.put_line(l_rec.p_sname || ',' ||
l_rec.p_snumber || ',' ||
l_rec.p_searchname || ',' ||
l_rec.p_searchnumber );
end loop;

close l_cursor;
end Account_Search_By;

Tom Kyte

Followup  

July 02, 2002 - 2:41 pm UTC

Oh, now it is obvious. Sorry I did not see this before ( i saw the bind variables -- lack thereof -- and stopped reading, sorry about that)

You have 8.0.

OPEN L_CURSOR FOR <variable>

is a new 8i feature. You cannot dynamically open a ref cursor in 8.0 and before, you would use DBMS_SQL. You will use dbms_sql with dbms_sql.open, .parse, .define, .bind_variable and so on.




It is interesting to note that in 8i, when you do do this, you could replace:

if ( p_snumber is NOT NULL ) then
IF p_searchname='1' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_CODE',
''||p_snumber||'%');
l_query := l_query ||
' and ACCOUNT_CODE like
sys_context( ''MY_CTX'', ''ACCOUNT_CODE'' ) ';
end if;
IF p_searchname='2' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_CODE',
'%'||p_snumber||'');
l_query := l_query ||
' and ACCOUNT_CODE like
sys_context( ''MY_CTX'', ''ACCOUNT_CODE'' ) ';
end if;
IF p_searchname='3' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_CODE',
'%'||p_snumber||'%');
l_query := l_query ||
' and ACCOUNT_CODE like
sys_context( ''MY_CTX'', ''ACCOUNT_CODE'' ) ';
end if;
IF p_searchname='4' THEN
dbms_session.set_context( 'MY_CTX', 'ACCOUNT_CODE',
''||p_snumber||'');
l_query := l_query ||
' and ACCOUNT_CODE =
sys_context( ''MY_CTX'', ''ACCOUNT_CODE'' ) ';
end if;

With

if ( p_snumber is NOT NULL ) then
select decode( p_searchnumber,
'1', p_snumber || '%', '2', '%' || p_snumber,
'3', '%' || p_snumber || '%', '4', p_snumber )
into l_snumber
from dual;

dbms_session.set_context( 'my_ctx', 'account_code', l_snumber );
l_qiery := l_query ||
' and account_code = sys_context( ''my_ctx'',''account_code'' ) ';
end if;



Elementary Troubleshooting

July 02, 2002 - 11:57 am UTC

Reviewer: Cory D. Wittenborn from Chicago, IL USA

Boris--when you do dynamic SQL or PL/SQL in Oracle ALWAYS collect the entire string you are going to execute in a variable and either DBMS_OUTPUT it or insert it into a debug table BEFORE executing so you can see what you are asking Oracle to execute. 99% of the time it is something silly that causes the problem--not escaping single quotes, forgetting a space (so that keywords flowtogetherlikethis), etc. Take the resulting output and then try to run it in SQL*Plus and see if it executes and, if it doesn't, what the problems are. The same suggestion applies to any language--troubleshooting dynamic SQL strings in VB should be done the same way, etc. ...

Creating dynamic stored procedure problem

July 02, 2002 - 4:53 pm UTC

Reviewer: Boris from NY

Hi Tom
I am really sorry for bothering you so many times but I did not get your last answer. YOu said "You will use dbms_sql with dbms_sql.open, .parse, .define, .bind_variable and so on." Where? How? My questions might sound stupid but today is a first day when I work with ORACLE 8.0
Could you help a little bit more please. I change the "IF" part the way you said but still have same problems as before.

Line 46 PLS-00103: Encountered the symbol "L_QUERY" when expecting one of the
following: select

line 62 PLS-00103: Encountered the symbol "end-of-file" when expecting one of
the following: begin function package pragma procedure form external


CREATE OR REPLACE PROCEDURE SMKT.Account_Search_By1
( p_sname in varchar2 default NULL,
p_snumber in varchar2 default NULL,
p_searchname in varchar2 default '1',
p_searchnumber in varchar2 default '1')

as
type rc is REF CURSOR;

l_cursor rc;
l_query varchar2(4000) default 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null ';

cursor l_template is SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null;
l_rec l_template%rowtype;
begin


if ( p_sname is NOT NULL ) then
select decode( p_searchname,
'1', p_sname || '%', '2', '%' || p_sname,
'3', '%' || p_sname || '%', '4', p_sname )
into l_sname
from dual;

dbms_session.set_context( 'my_ctx', 'ACCOUNT_NAME', l_sname );
l_qiery := l_query ||
' and ACCOUNT_NAME = sys_context( ''my_ctx'',''ACCOUNT_NAME'' ) ';
end if;

if ( p_snumber is NOT NULL ) then
select decode( p_searchnumber,
'1', p_snumber || '%', '2', '%' || p_snumber,
'3', '%' || p_snumber || '%', '4', p_snumber )
into l_snumber
from dual;

dbms_session.set_context( 'my_ctx', 'ACCOUNT_CODE', l_snumber );
l_qiery := l_query ||
' and ACCOUNT_CODE = sys_context( ''my_ctx'',''ACCOUNT_CODE'' ) ';
end if;


--Print( l_query );
dbms_output.put_line( l_query);

open l_cursor for l_query;

loop
fetch l_cursor into l_rec;

exit when l_cursor%notfound;

dbms_output.put_line(l_rec.p_sname || ',' ||
l_rec.p_snumber || ',' ||
l_rec.p_searchname || ',' ||
l_rec.p_searchnumber );
end loop;

close l_cursor;
end Account_Search_By1;




Tom Kyte

Followup  

July 02, 2002 - 7:38 pm UTC

This:

Line 46 PLS-00103: Encountered the symbol "L_QUERY" when expecting one of the
following: select

is because the syntax you are using:

open l_cursor for l_query;

which is "open cursor for <variable>" is NEW with 8i, you have 8.0 and you need 8.1 and up for that syntax. You can ONLY open a static query in 8.0 and before. In looking at your procedure, your best bet is this:




CREATE OR REPLACE PROCEDURE SMKT.Account_Search_By1
( p_sname in varchar2 default NULL,
p_snumber in varchar2 default NULL,
p_searchname in varchar2 default '1',
p_searchnumber in varchar2 default '1')

as
type rc is REF CURSOR;

l_cursor rc;
l_query varchar2(4000) default 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT
WHERE ACCOUNT_NUMBER is not null ';

cursor l_template is SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE
ACCOUNT_NUMBER is not null;
l_rec l_template%rowtype;
begin


if ( p_sname is NOT NULL ) then
select decode( p_searchname,
'1', p_sname || '%', '2', '%' || p_sname,
'3', '%' || p_sname || '%', '4', p_sname )
into l_sname
from dual;
end if;

if ( p_snumber is NOT NULL ) then
select decode( p_searchnumber,
'1', p_snumber || '%', '2', '%' || p_snumber,
'3', '%' || p_snumber || '%', '4', p_snumber )
into l_snumber
from dual;
end if;

if ( l_sname is not null and l_snumber is not null )
then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT
where account_name LIKE l_sname and account_code LIKE l_snumber;
elsif ( l_sname is not null )
then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT
where account_name LIKE l_sname;
elsif ( l_snumber is not null )
then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT
where account_code LIKE l_snumber;
else
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT;
end if;

loop
fetch l_cursor into l_rec;

exit when l_cursor%notfound;

dbms_output.put_line(l_rec.p_sname || ',' ||
l_rec.p_snumber || ',' ||
l_rec.p_searchname || ',' ||
l_rec.p_searchnumber );
end loop;

close l_cursor;
end Account_Search_By1;





Create dynamic stored procedure

July 08, 2002 - 11:27 am UTC

Reviewer: Boris Cherkalin from NY

Hi Tom,
Thanks a lot for your help. Thanks, but I still have one small problem. Could you help me a little bit more. Here is an error:

Line 43 PL/SQL: Statement ignored
Line 43 PLS-00302: component 'P_SNAME' must be declared
Error happened on line “dbms_output.put_line(l_rec.p_sname || ',' || l_rec.p_snumber || ',' || l_rec.p_searchname || ',' || l_rec.p_searchnumber );”

Thanks a lot

CREATE OR REPLACE PROCEDURE SMKT.Account_Search_By2
( p_sname in varchar2 default NULL,
p_snumber in varchar2 default NULL,
p_searchname in varchar2 default '1',
p_searchnumber in varchar2 default '1')

as
l_sname VARCHAR2(300);
l_snumber VARCHAR2(300);
type rc is REF CURSOR;
l_cursor rc;
l_query varchar2(4000) default 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null ';
cursor l_template is SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null;
l_rec l_template%rowtype;
begin


if ( p_sname is NOT NULL ) then
select decode( p_searchname, '1', p_sname || '%', '2', '%' || p_sname, '3', '%' || p_sname || '%', '4', p_sname )
into l_sname
from dual;
end if;

if ( p_snumber is NOT NULL ) then
select decode( p_searchnumber, '1', p_snumber || '%', '2', '%' || p_snumber, '3', '%' || p_snumber || '%', '4', p_snumber )
into l_snumber
from dual;
end if;

if ( l_sname is not null and l_snumber is not null ) then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT where ACCOUNT_NUMBER is not null and account_name LIKE l_sname and account_code LIKE l_snumber;
elsif ( l_sname is not null ) then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT where ACCOUNT_NUMBER is not null and account_name LIKE l_sname;
elsif ( l_snumber is not null ) then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT where ACCOUNT_NUMBER is not null and account_code LIKE l_snumber;
else
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null;
end if;

loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
dbms_output.put_line(l_rec.p_sname || ',' || l_rec.p_snumber || ',' || l_rec.p_searchname || ',' || l_rec.p_searchnumber );
end loop;
close l_cursor;
end Account_Search_By2;


Tom Kyte

Followup  

July 08, 2002 - 4:29 pm UTC

well, p_sname and p_snumber and so on are not in the cursor -- the l_rec record only has ACCOUNT_CODE in it.

p_sname and so on are simple "variables". maybe:

dbms_output.put_line(p_sname || ',' || p_snumber || ',' ||
p_searchname || ',' || p_searchnumber );

is what you meant.

Create dynamic stored procedure

July 08, 2002 - 4:34 pm UTC

Reviewer: Boris Cherkalin from NY

Thank a lot Tom
I've posted my question to 3 or 4 places and only you are the one who really is helping. Thanks you very much.

Creating dynamic stored procedure problem

July 11, 2002 - 3:56 pm UTC

Reviewer: Boris Cherkalin from NY

Hi Tom,
Sorry but I have to bother you a little bit more. I am frustrated with ORACLE (or mostly with myself). I need your help. I compiled stored procedure and there was no mistakes but I can not retrieve anything.
Here is my stored procedure:
PROCEDURE ACCOUNT_SEARCH
( p_sname in varchar2 default NULL,
p_snumber in varchar2 default NULL,
p_searchname in varchar2 default '1',
p_searchnumber in varchar2 default '1')
as
l_sname VARCHAR2(300);
l_snumber VARCHAR2(300);
type rc is REF CURSOR;
l_cursor rc;
l_query varchar2(4000) default 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null ';
cursor l_template is SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null;
l_rec l_template%rowtype;
begin
if ( p_sname is NOT NULL ) then
select decode( p_searchname, '1', p_sname || '%', '2', '%' || p_sname, '3', '%' || p_sname || '%', '4', p_sname )
into l_sname
from dual;
end if;
if ( p_snumber is NOT NULL ) then
select decode( p_searchnumber, '1', p_snumber || '%', '2', '%' || p_snumber, '3', '%' || p_snumber || '%', '4', p_snumber )
into l_snumber
from dual;
end if;
if ( l_sname is not null and l_snumber is not null ) then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT where ACCOUNT_NUMBER is not null and account_name LIKE l_sname and account_code LIKE l_snumber;
elsif ( l_sname is not null ) then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT where ACCOUNT_NUMBER is not null and account_name LIKE l_sname;
elsif ( l_snumber is not null ) then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT where ACCOUNT_NUMBER is not null and account_code LIKE l_snumber;
else
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null;
end if;
loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
begin
dbms_output.put_line(l_sname || ',' || l_snumber );
end;
end loop;
close l_cursor;
end ACCOUNT_SEARCH;
Here is table structure I am using for my select:
CREATE TABLE INTLFCST.INTL_ACCOUNT
(
ACCOUNT_CODE VARCHAR2(3) NOT NULL,
ACCOUNT_NAME VARCHAR2(32) NOT NULL,
AFFILIATE_NAME VARCHAR2(32) NOT NULL,
ACCOUNT_NUMBER VARCHAR2(5) NOT NULL,
EFFECTIVE_START_DT DATE NOT NULL,
EFFECTIVE_END_DT DATE NULL,
FORECAST_IND CHAR(1) DEFAULT 'Y' NOT NULL,
ACTUAL_IND CHAR(1) DEFAULT 'Y' NOT NULL,
REF_RLI_COUNTRY_CODE VARCHAR2(2) NOT NULL
)
and table data:
INSERT INTO INTL_ACCOUNT
(ACCOUNT_CODE,ACCOUNT_NAME,AFFILIATE_NAME,ACCOUNT_NUMBER,EFFECTIVE_START_DT,EFFECTIVE_END_DT,FORECAST_IND,ACTUAL_IND,REF_RLI_COUNTRY_CODE)
VALUES
('USA','United States','Big Company','11111','WEDNESDAY, JULY 10, 2002',NULL,'1','1','11')

INSERT INTO INTL_ACCOUNT
(ACCOUNT_CODE,ACCOUNT_NAME,AFFILIATE_NAME,ACCOUNT_NUMBER,EFFECTIVE_START_DT,EFFECTIVE_END_DT,FORECAST_IND,ACTUAL_IND,REF_RLI_COUNTRY_CODE)
VALUES
('RUS','Russia','RusCompany','22222','WEDNESDAY, JULY 10, 2002',NULL,'1','1','22')

INSERT INTO INTL_ACCOUNT (ACCOUNT_CODE,ACCOUNT_NAME,AFFILIATE_NAME,ACCOUNT_NUMBER,EFFECTIVE_START_DT,EFFECTIVE_END_DT,FORECAST_IND,ACTUAL_IND,REF_RLI_COUNTRY_CODE)
VALUES
('CHI','China','China Cosmetic','33333','WEDNESDAY, JULY 10, 2002',NULL,'1','1','33')

INSERT INTO INTL_ACCOUNT (ACCOUNT_CODE,ACCOUNT_NAME,AFFILIATE_NAME,ACCOUNT_NUMBER,EFFECTIVE_START_DT,EFFECTIVE_END_DT,FORECAST_IND,ACTUAL_IND,REF_RLI_COUNTRY_CODE)
VALUES
('FRA','France','France Cosmetic','44444','WEDNESDAY, JULY 10, 2002',NULL,'1','1','44')

INSERT INTO INTL_ACCOUNT
(ACCOUNT_CODE,ACCOUNT_NAME,AFFILIATE_NAME,ACCOUNT_NUMBER,EFFECTIVE_START_DT,EFFECTIVE_END_DT,FORECAST_IND,ACTUAL_IND,REF_RLI_COUNTRY_CODE)
VALUES
('ITA','Italy','Italy Cosmetic','55555','WEDNESDAY, JULY 10, 2002',NULL,'1','1','55')

If I pass following parameters (‘U’,’’,’1’,’1’) I should get one record (“USA”) but it doesn’t return anything.
Please help.


Tom Kyte

Followup  

July 12, 2002 - 8:02 am UTC

OH, so close

SQL> set serveroutput on size 1000000

you need to tell sqlplus to dump the dbms_output buffer.

I use a login.sql file to have this always be set in my sessions.....  But you can just put it in the script.

It worked -- you just didn't see it! 

Creating dynamic stored procedure problem

July 12, 2002 - 9:50 am UTC

Reviewer: Boris Cherkalin from NY

Hi Tom,
Thanks for your help. As you said I added “set serveroutput on size 1000000” line and it gives me an error: PLS-00103: Encountered the symbol "SERVEROUTPUT" when expecting one of the following: transaction
One more thing I did not get this part, You said: “you need to tell sqlplus to dump the dbms_output buffer.I use a login.sql file to have this always be set in my sessions..... But you can just put it in the script.” Do you have an example of the code how should I dump the dbms_output buffer.

CREATE OR REPLACE PROCEDURE INTLFCST.ACCOUNT_SEARCH
( p_sname in varchar2 default NULL,
p_snumber in varchar2 default NULL,
p_searchname in varchar2 default '1',
p_searchnumber in varchar2 default '1')
as
l_sname VARCHAR2(300);
l_snumber VARCHAR2(300);
type rc is REF CURSOR;
l_cursor rc;
l_query varchar2(4000) default 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null ';
cursor l_template is SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null;
l_rec l_template%rowtype;
begin

if ( p_sname is NOT NULL ) then
select decode( p_searchname, '1', p_sname || '%', '2', '%' || p_sname, '3', '%' || p_sname || '%', '4', p_sname )
into l_sname
from dual;
end if;
if ( p_snumber is NOT NULL ) then
select decode( p_searchnumber, '1', p_snumber || '%', '2', '%' || p_snumber, '3', '%' || p_snumber || '%', '4', p_snumber )
into l_snumber
from dual;
end if;
if ( l_sname is not null and l_snumber is not null ) then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT where ACCOUNT_NUMBER is not null and account_name LIKE l_sname and account_code LIKE l_snumber;
elsif ( l_sname is not null ) then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT where ACCOUNT_NUMBER is not null and account_name LIKE l_sname;
elsif ( l_snumber is not null ) then
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT where ACCOUNT_NUMBER is not null and account_code LIKE l_snumber;
else
open l_cursor for SELECT ACCOUNT_CODE FROM INTL_ACCOUNT WHERE ACCOUNT_NUMBER is not null;
end if;

set serveroutput on size 1000000

loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
begin
dbms_output.put_line(l_sname || ',' || l_snumber );
end;
end loop;
close l_cursor;
end ACCOUNT_SEARCH;


Tom Kyte

Followup  

July 12, 2002 - 10:01 am UTC

<b>SQL></b> set serveroutput on size 1000000


you do that at the SQL prompt.


scott@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( 'hello world' );

PL/SQL procedure successfully completed.

scott@ORA817DEV.US.ORACLE.COM> set serveroutput on
scott@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( 'hello world' );
hello world

PL/SQL procedure successfully completed.


<b>not in the plsql -- it is a sqlplus thing</b>
 

January 22, 2003 - 8:40 pm UTC

Reviewer: A reader

Is there anyway at all to set serveroutput on inside a procedure code so that irrespective of the client setting, the output will be displayed. Is there a sql command equivalent to this (like an alter session?). Thanks.

Tom Kyte

Followup  

January 23, 2003 - 7:48 am UTC

Nope - if you understand how it works, it would be clear why this is so. (if you have my book "Expert one on one Oracle" -- i go into details)

dbms_output just fills up a buffer.

The CLIENT is responsible for pulling this buffer and actually printing it.

Hence, if the client isn't told (sqlplus via set serverouput on for example, your own code via some flag) that they need to dump the output -- they don't know to pull it and dump it.

Therefore, UNLESS you use sqlplus commands to tell SQPLUS "hey, after you do something, call dbms_output.get_lines and actually print out anything you find" -- it just won't be happening.

Need some quick help....

May 27, 2003 - 1:58 pm UTC

Reviewer: venkat

Hi Tom,

Sorry to use this thread for my problem...I need help getting this working.... I am creating a procedure like this:

TEST:SQL> create or replace procedure p1 as
  2  begin
  3  insert into t1 (id, name) (select 1,(select 'venkat' from dual) from dual);
  4  end;
  5  /

Warning: Procedure created with compilation errors.

TEST:SQL> show err
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/38     PLS-00103: Encountered the symbol "SELECT" when expecting one of
         the following:
         ( - + mod not null others <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> avg
         count current exists max min prior sql stddev sum variance
         execute forall time timestamp interval date
         <a string literal with character set specification>
         <a number> <a single-quoted SQL string>

3/65     PLS-00103: Encountered the symbol "FROM" when expecting one of
         the following:
         ; return returning and or

TEST:SQL> select 1,(select 'venkat' from dual) from dual;

         1 (SELEC
---------- ------
         1 venkat

Can you tell me if this is possible to do or i need to think of a workaround? Your help much appreciated!!!

Thanks in advance
Venkat.
 

Tom Kyte

Followup  

May 27, 2003 - 5:26 pm UTC

in 8i and before certain SQL constructs were unknown to the plsql parser such as

o select (select )....
o order by in subquery
o CASE
o analytics
o group by extensions


the workarounds in 8i, short of getting on 9i, would be

o native dynamic sql
o dbms_sql dynamic sql
o views

so, in your case,

execute immediate 'insert into .....';


Thanks Tom, for your suggestion!!!

May 27, 2003 - 7:28 pm UTC

Reviewer: A reader


June 14, 2004 - 3:16 pm UTC

Reviewer: A reader


BULK COLLECT

December 11, 2004 - 8:29 am UTC

Reviewer: A reader


December 11, 2004 - 8:55 am UTC

Reviewer: A reader


PLS-00103: Encountered the symbol "LOOP" when expecting one of the following

December 27, 2004 - 8:12 am UTC

Reviewer: Shriram Mohite from India

create or replace function cust(CUST_ID IN VARCHAR2) RETURN VARCHAR2
IS
CUSTGRADE VARCHAR2(100);
BEGIN
FOR P1 IN (SELECT PG.PRODUCT_GROUP_NAME PRODNAME
, SUM(DPM.CONSUMPTION) AS CONSUM FROM
DGC_PLAN_MASTER DPM, MACRO_PLAN_MASTER MPM, PRODUCT_GROUP PG
WHERE DPM.MACRO_PLAN_ID=MPM.MACRO_PLAN_ID AND
MPM.PRODUCT_ID=PG.PRODUCT_GROUP_ID AND DPM.CUSTOMER_ID=CUST_ID
GROUP BY PG.PRODUCT_GROUP_NAME) LOOP
--- FOR PP GROUP
IF P1.PRODNAME='PP' AND P1.CONSUM > 500 THEN
CUSTGRADE :=CUSTGRADE || 'PP-A';
ELSE IF P1.PRODNAME='PP' AND (P1.CONSUM > 100 AND P1.CONSUM < 500) THEN
CUSTGRADE :=CUSTGRADE || 'PP-B';
ELSE IF P1.PRODNAME='PP' AND P1.CONSUM < 100 THEN
CUSTGRADE :=CUSTGRADE || 'PP-C';
--- FOR 'PF' GROUP
ELSE IF P1.PRODNAME='PF' AND P1.CONSUM > 100 THEN
CUSTGRADE :=CUSTGRADE || 'PF-A';
ELSE IF P1.PRODNAME='PF' AND (P1.CONSUM > 30 AND P1.CONSUM < 100) THEN
CUSTGRADE :=CUSTGRADE || 'PF-B';
ELSE IF P1.PRODNAME='PF' AND P1.CONSUM < 30 THEN
CUSTGRADE :=CUSTGRADE || 'PF-C';
--- FOR 'PF' GROUP
ELSE IF P1.PRODNAME='PV' AND P1.CONSUM > 250 THEN
CUSTGRADE :=CUSTGRADE || 'PV-A';
ELSE IF P1.PRODNAME='PV' AND (P1.CONSUM > 100 AND P1.CONSUM < 250) THEN
CUSTGRADE :=CUSTGRADE || 'PV-B';
ELSE IF P1.PRODNAME='PV' AND P1.CONSUM < 100 THEN
CUSTGRADE :=CUSTGRADE || 'PV-C';
END IF;
END LOOP;
RETURN CUSTGRADE;
END;


Tom Kyte

Followup  

December 27, 2004 - 10:21 am UTC

one word for you:

elsif



errors while executing

February 16, 2005 - 10:29 am UTC

Reviewer: nimisha from USA

This my first simple function and I have some errors when trying to compile it. please help me.i am new in oracle just started learning


create or replace function addition(a integer,b integer)
return number
as
x integer;
begin

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/2 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
( - + ; mod not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
cast <a string literal with character set specification>
<a number> <a single-quoted SQL string>
The symbol ";" was substituted for "END" to continue.
a:= 2;
b:=3;
x:=a+b;
dbms_output.put_line('the variable x:= ');
return
end;
/

Tom Kyte

Followup  

February 16, 2005 - 10:55 am UTC

do you see your missing semi colon after return?



Iam getting an error

January 03, 2006 - 4:20 am UTC

Reviewer: murali from india

SQL> ed
Wrote file afiedt.buf

  1   create or replace procedure sp_adminlogin(username_in varchar2(100)) is
  2   --declare
  3   cursor cur_admin is
  4   SELECT ADMIN_USERS.ADM_ID,ADMIN_USERS.ADM_USERNAME,LEVEL_MASTER.LVM_STATUS,ADMIN_USERS.ADM_PASSWORD,
  5     LEVEL_MASTER.LVM_ID,LEVEL_MASTER.LVM_REDIRECT FROM ADMIN_USERS,LEVEL_MASTER WHERE
  6     ADMIN_USERS.ADM_LVM_ID = LEVEL_MASTER.LVM_ID and admin_users.adm_username = username_in;
  7   begin
  8   for i in cur_admin loop
  9   dbms_output.put_line(i.adm_username);
 10   end loop;
 11*  END;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE SP_ADMINLOGIN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/45     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

 

Tom Kyte

Followup  

January 03, 2006 - 7:34 am UTC

bummer?

I suppose what you want to do is ask "why am I getting this on line 1, column 45"?

because all plsql parameters are unconstrained types - you do not use varchar2(100), you just use varchar2, or table.column%type


Creating Stored procedures in 9i

January 25, 2006 - 12:00 pm UTC

Reviewer: lakshmi from Denver, CO

Hi Tom,

I am new to Oracle and this is the first stored procedure I created today in version 9i. The code looks a lot like a VB code, since that is what I have worked with for a long time... Can you please tell me what these errors mean?? Would really appreciate the help!!!

CREATE OR REPLACE PROCEDURE LSEWARE.GETLEASEPMTSTREAM
AS

vSeq number(4); ---- Sequence
vDueDate date; ------ Due date in the main table
vLoop number(2); ------ Loops for each type of lease
vRent number(10,2); ------ Rent(Payment)
vRunRent number(12,2); ------ Running Total of Rent
vPmtL number(14,2); ------- Payments
vAdvance boolean; ------- Whether there is an advance
vFirst boolean; ------ ??
vLastLse varchar2(9); ------ To store the last lease number

vCtr number(5); --- counter for FOR loop 1
vCtr1 number(5); --- counter for FOR loop 2


cursor C1 IS Select * from Lseware.Vw_LeaseList ; --- Where LSe_s = <lessee number>
cursor C2 IS Select * from Lseware.Vw_LeasePaymentSchedule; -- Where Lse_s =<Lessee number>

-- Variables for Vw_leaseList cursor this saves????
c1_lse_s varchar2(10);
c1_acc_l_s varchar2(12);
c1_d_com_s date;
c1_pmt_l number(10);
c1_active_lease char(3);

--- Variables for Vw_Leasepaymentschedule
c2_lse_s varchar2(10);
c2_unique_l number(10);
c2_pmt_frq_l number(10);
c2_pmt_amt_d number(20);
c2_pmt_cd_s varchar2(12);


BEGIN
-- First truncate the Lease Payment Stream table

-- TRUNCATE TABLE lseware.LeasePmtStream;
-- Commit;


vLastLse:=null; -- Set Last lease ctr to null
vSeq:=0; -- Set the sequence to 0

-- Open the first cursor Vw_LeaseList in a loop
Open c1;

Loop
fetch c1 into c1_lse_s, c1_acc_l_s, c1_d_com_s, c1_pmt_l, c1_active_lease;
-- Open the second cursor Vw_LeasePayment Schedules
open c2;

Loop
fetch c2 into c2_lse_s, c2_unique_l, c2_pmt_frq_l, c2_pmt_amt_d, c2_pmt_cd_s;

-- Reset the Last lease number & running amount variables
if vLastLse is null or vLastLse <> c2_lse_s then
vLastLse:=c2_lse_s;
vRunRent:=0;
endif;

--- Case statement to evaluate the pmt_cd_s
CASE c2_pmt_cd_s
WHEN 'ADVA' THEN vLoop:=12;
WHEN 'ANNL' THEN vLoop:=12;
WHEN 'ADVS' THEN vLoop:=6;
WHEN 'SEMI' THEN vLoop:=6;
WHEN 'ADVQ' THEN vLoop:=3;
WHEN 'QTR' THEN vLoop:=3;
WHEN 'ADVM' THEN vLoop:=1;
WHEN 'MON' THEN vLoop:=1;
WHEN 'SKIP' THEN vLoop:=1;
--ELSE -- Raise error here;
END CASE;


vPmtL:=c1_pmt_l*vLoop;

-- determine an advance

if substr(c1_acc_l_s,1,1)='A' then
vAdvance:=true;
else
vAdvance:=false;
endif;

-- populate the Vduedate and vLstdate

For vCtr IN 1..c2_pmt_frq_l ----------- FOR LOOP 1
if c2_unique_l=1 and vCtr=1 then
if substr(c1_acc_l_s,1,1)='A' then
vDueDate:=c1_d_com_s;
else
vDueDate:=add_months(c1_d_com_s,1);
endif;
else
vDueDate:=add_months(vDueDate, 1);
endif;


For vCtr1 IN 1..vLoop ------------ FOR Loop 2

if vCtr=1 and vCtr1>1 then
vDueDate:=add_months(vDueDate, 1);
endif;


if vAdvance then
if vCtr1=1 then
vRent:=c2_pmt_amt_d;
else
vRent:=0;
endif;
else
if vCtr1=vLoop then
vRent:=c2_pmt_amt_d;
else
vRent:=0;
endif;
endif;

vRunRent:=vRunRent+VRent;
vSeq:=vSeq+1;

Insert into lseware.LeasePmtStream values (c2_lse_s,vSeq,vPmtL,vRent,vDueDate,vRunRent);

vLastDate:=vDueDate;
vLastLse:=c2_lse_s;

End for;

END for;

EXIT WHEN C2%NOTFOUND; -- end of cursor 2

End Loop; -- end of loop for cursor 2

Close c2;

EXIT WHEN C1%NOTFOUND; -- exit from cursor 1

End Loop; --- end of loop for cursor 1

Close c1;

Commit;

END


ERRORS:

GETLEASEPMTSTREAM PROCEDURE 1 90 5 PLS-00103: Encountered the symbol "IF" when expecting one of the following:

. ( * @ % & - + / at loop mod rem <an exponent (**)> ||
The symbol "loop" was substituted for "IF" to continue.

GETLEASEPMTSTREAM PROCEDURE 2 96 5 PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:

begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe

GETLEASEPMTSTREAM PROCEDURE 3 103 18 PLS-00103: Encountered the symbol "IF" when expecting one of the following:

. ( * @ % & - + / at loop mod rem <an exponent (**)> ||
The symbol "loop" was substituted for "IF" to continue.

GETLEASEPMTSTREAM PROCEDURE 4 114 6 PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:

begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe

GETLEASEPMTSTREAM PROCEDURE 5 130 9 PLS-00103: Encountered the symbol "FOR" when expecting one of the following:

if



stored procedures

February 01, 2006 - 12:30 pm UTC

Reviewer: L from USA

Never mind!! Got it

Mutating table....

March 14, 2006 - 2:16 pm UTC

Reviewer: Gerardo Pimentel from Mexico City

Hello Tom,

I have an aplication that through some filters I need to go to an screen which one of its fields has a value.

When this value is changed I need the trigger on this table fires and makes an update on the same table and the first value I had at the begining return.

This process needs two tables, Tab_One and Tab_Two. Second table has a trigger After Insert or Update for each row.

Inside the code I query the first table to get some values which I use them to make another query on table_two and finally with all these values I need to update table_two.

I perfectly know that because I'm querying and updating the same table where trigger is, error ORA-04091 rises.

However the same code works whithout any problem in SQL/SERVER. I have done all you says in your article Avoiding Mutating Tables but I'm still getting the same error.

Please help me!!! This problem it's make crazy.

I don't believe SQL/SERVER let do this operation and Oracle doesn't.

Next is the original code:

CREATE TRIGGER SSUPDREFP
ON table_two
FOR INSERT, UPDATE
AS
IF UPDATE ( SUN_DATA )
SET NOCOUNT ON
DECLARE @CUENTA varchar(10),@TRANSDATE VARCHAR(8), @REFEREN varchar(10),
@JRNN INT , @JRNL INT,@T1 varchar(15),@message varchar(120),@SUNDATA VARCHAR(80),
@message2 varchar(100),@SUNDAT2 VARCHAR(80),@POLIZA VARCHAR(7),@LINEA VARCHAR(7),@CON INT
DECLARE poliza_cursor CURSOR FOR
SELECT ACCNT_CODE,TRANS_DATE,TREFERENCE,JRNAL_NO,JRNAL_LINE,ANAL_T1
FROM table_one
WHERE ALLOCATION='F'
ORDER BY ACCNT_CODE

OPEN poliza_cursor
FETCH NEXT FROM poliza_cursor INTO @CUENTA, @TRANSDATE, @REFEREN,@JRNN,@JRNL,@T1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @POLIZA=''+@JRNN
SET @LINEA=''+@JRNL
SET @SUNDATA=(SELECT SUN_DATA FROM table_two WHERE SUN_DB='FMM' AND SUN_TB='SDE' AND KEY_FIELDS=@T1)
SELECT @message =@CUENTA + ' '+ @TRANSDATE + ' ' +@REFEREN + ' '+ @POLIZA + ' ' + @LINEA + ' ' + @T1+ @SUNDATA
UPDATE table_two SET SUN_DATA=@SUNDATA
WHERE
SUN_DB='FMM' AND SUN_TB='ESR'
AND SUBSTRING(KEY_FIELDS, 1,10)=@CUENTA
AND SUBSTRING(KEY_FIELDS, 16,6)=@REFEREN
AND SUBSTRING(KEY_FIELDS, 31,7)=@JRNN
AND SUBSTRING(KEY_FIELDS, 38,7)=@JRNL

FETCH NEXT FROM poliza_cursor INTO @CUENTA, @TRANSDATE, @REFEREN,@JRNN,@JRNL,@T1
END

CLOSE poliza_cursor
DEALLOCATE poliza_cursor

GO

I'm looking forward to hearing from you!!

Best Regards,

Gerardo Pimentel

Tom Kyte

Followup  

March 15, 2006 - 9:30 am UTC

why are you giving me a sql server trigger????!?!??!

sql server has one trigger type - AFTER.

You can do this in an after trigger in Oracle as well. Using triggers this way is a horrible coding practice (in any database). Hate this stuf.


how about in english you say what you are trying to do. I'm not going to reverse engineer a sql server trigger.

and guess what: there will be hundreds of things you did in sqlserver that don't fly in Oracle. There will be thousands of things you do in Oracle that you won't be able to do in sqlserver. We are different, that is the point of having more than one database on the market.

PLS

May 25, 2006 - 4:55 am UTC

Reviewer: Benexxavier from India

PLS-00103: Encountered the symbol "@" when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier>
current

Tom Kyte

Followup  

May 25, 2006 - 1:44 pm UTC

ok? so?

statspack error after upgrading

July 07, 2006 - 3:21 am UTC

Reviewer: ravs from india

Hi Tom,
We have recently upgraded our db from 8.1.7.2 to 9.2.0.4 using oracle scripts (metalink ID159657.1).
Now it is in 9.2.0.4. I was trying to install statspack on this by running (pointing to oracle9i home) spcreate.
But the package "STATSPACK" is failing at some of the statements.. like (part of statspack package)

insert into stats$sqltext
( hash_value
, text_subset
, piece
, sql_text
, address
, command_type
, last_snap_id
)
select st1.hash_value
, ss.text_subset
, st1.piece
, st1.sql_text
, st1.address
, st1.command_type
, ss.snap_id
from v$sqltext st1
, stats$sql_summary ss
where ss.snap_id = l_snap_id
and ss.dbid = p_dbid
and ss.instance_number = p_instance_number
and st1.hash_value = ss.hash_value
and st1.address = ss.address
and not exists (select 1
from stats$sqltext st2
where st2.hash_value = ss.hash_value
and st2.text_subset = ss.text_subset
)
order by st1.hash_value,ss.text_subset; -- deadlock avoidance

i was going thru this poster (posted by venkat) and found that prior to 8i select (select won't work). But i have upgraded it to 9i now. Do i need to do any specific settings to make it work.. my compatible parameter in init.ora is "9.2.0.4"

please help
- Thanks
ravi

Tom Kyte

Followup  

July 08, 2006 - 10:42 am UTC

well, you don't say "what" error you are actually getting, so it is not really possible to comment.

you don't have any select (select) in there.

How to spell out the date in oracle

September 19, 2006 - 8:55 am UTC

Reviewer: manikandan from India

Hi Tom,

How to spell out the date in oracle.for example,If i want spell out the number , i can use JSP then, it can spell my number whatever it is..Please help me out from this problem.

Thanks,
Manikandan.k

Tom Kyte

Followup  

September 19, 2006 - 2:42 pm UTC

define "spell out the date"


ops$tkyte%ORA10GR2> select to_char( sysdate, 'Month DDth, YYYY' ) from dual;

TO_CHAR(SYSDATE,'MON
--------------------
September 19TH, 2006


to me, that is spelled out.. 

Regarding the doubt in triggers

September 20, 2006 - 9:18 am UTC

Reviewer: manikandan.k from india

Hi Tom,

How are you?..I am doing good..

I am creating two trigger on same table..for ex..

create trigger tri_1
after insert on emp
for each row
begin
some logic was here..
end;

create trigger tri_2
after insert on emp
for each row
begin
some logic was here..
end;

whenever,I am inserting the data into this table(emp),the second trigger will be fired..why it was happening me..

why the first triger will not be fired on this table..

then i drop the second triger then first triger will be fired..


Thanks,
Manikandan.k


Tom Kyte

Followup  

September 20, 2006 - 3:19 pm UTC

eh?  they both are fired, unless you accidently named them the same and they overwrote eachother.

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> create or replace trigger t1 after insert on t for each row
  2  begin
  3          dbms_output.put_line( 'Greetings from T1!!' );
  4  end;
  5  /

Trigger created.

ops$tkyte%ORA10GR2> create or replace trigger t2 after insert on t for each row
  2  begin
  3          dbms_output.put_line( 'Greetings from T2!!' );
  4  end;
  5  /

Trigger created.

ops$tkyte%ORA10GR2> insert into t values ( 1 );
Greetings from T2!!
Greetings from T1!!

1 row created.
 

compile time errors

May 16, 2008 - 12:25 am UTC

Reviewer: KRISHNA from India

CREATE PROCEDURE [dbo].[Usp_CheckUser]
(
@pUserID varchar(40),
@pPassword varchar(50)
)
AS
Begin
if exists(select U_ID from AWC_TAB_User where [USER_ID]=@pUserID)
Begin
if exists(select U_ID from AWC_TAB_User where [USER_ID]=@pUserID and PSWD=@pPassword)
select DISTINCT 1,U_ID,[USER_ID], USER_NM, First_Login,g.GRP_ID, GRP_DESCR, OUTLET_CD, g.IS_ADMIN, Last_Login_Date
FROM AWC_TAB_User u, AWC_TAB_GRP g
WHERE u.GRP_ID=g.GRP_ID AND u.ACTIVE_IND='Y' AND [USER_ID]=@pUserID AND PSWD=@pPassword;
else
select 2
else
Begin
select 3
End
End

above procedure is in sql server 2005
I want to convert it into oracle 8i
I write a code like this n I compiled it

CREATE OR REPLACE PROCEDURE AWC_SP_CHKUSER
(
v_UserID varchar2,
v_Pw varchar2
)
AS
Begin
if exists(select U_ID from AWC_TAB_User where USER_ID=v_UserID)
Begin
if exists(select U_ID from AWC_TAB_User where USER_ID=v_UserID and PW=v_Pw)
select DISTINCT 1,U_ID,[USER_ID], USER_NM, First_Login,g.GRP_ID, GRP_DESCR, OUTLET_CD, g.IS_ADMIN, Last_Login_Date
FROM AWC_TAB_User u, AWC_TAB_GRP g
WHERE u.GRP_ID=g.GRP_ID AND u.ACTIVE_IND='Y' AND USER_ID=v_UserID AND PW=v_Pw;
else
select 2 FROM DUAL;
else
Begin
select 3 FROM DUAL;
End
End;


Warning: Procedure created with compilation errors.

IM GETTING THIS AS OUTPUT
WHT IS THE PROBLEM IN MY STORED PROCEDURE


Tom Kyte

Followup  

May 19, 2008 - 2:30 pm UTC

THE PROBLEM IS YOU HAVE NOT BOTHERED TO LEARN PLSQL AND EXPECT ORACLE TO BE AS PRIMITIVE AS SQL SERVER IS WITH IT"S TSQL LANGUAGE.


oh, and you are using ancient software too - brilliant :(


why you ever had procedural code to do this is beyond me - in sql server or oracle

that is a single query....


Ok, as a specification sort of:

if the user does not exist in the awc_tab, then return 3
if the user does exist in the awc_tab, but the password is wrong, return 2
else return 1, the user_id, the name and other data from the join.



ops$tkyte@ORA817DEV> create table awc_tab ( u_uid number, user_id varchar2(30), pswd varchar2(30),
  2  user_nm varchar2(30), first_login date, last_login_date date, grp_id number, active_ind varchar2(1) );

Table created.

ops$tkyte@ORA817DEV> create table awc_tab_grp ( grp_id number, grp_descr varchar2(10), outlet_cd number, is_admin varchar2(10) );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> variable pUserId varchar2(30)
ops$tkyte@ORA817DEV> variable pPassWord varchar2(30)
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec :pUserId := 'tkyte'

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> exec :pPassword := 'hello';

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  2              when awc_tab.user_id = d.pUserId then 2
  3              else 3
  4          end silly_flag
  5    from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
  6   where awc_tab.user_id(+) = d.pUserId
  7  /

SILLY_FLAG
----------
         3

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into awc_tab( user_id, active_ind ) values ('tkyte', 'Y');

1 row created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  2              when awc_tab.user_id = d.pUserId then 2
  3              else 3
  4          end silly_flag
  5    from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
  6   where awc_tab.user_id(+) = d.pUserId
  7  /

SILLY_FLAG
----------
         2

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> update awc_tab set pswd = 'hello';

1 row updated.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  2              when awc_tab.user_id = d.pUserId then 2
  3              else 3
  4          end silly_flag
  5    from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
  6   where awc_tab.user_id(+) = d.pUserId
  7  /

SILLY_FLAG
----------
         1

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select *
  2    from ( select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  3                       when awc_tab.user_id = d.pUserId then 2
  4                       else 3
  5                   end silly_flag, awc_tab.*
  6             from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
  7            where awc_tab.user_id(+) = d.pUserId
  8          ) awc_tab, awc_tab_grp
  9   where awc_tab.grp_id = awc_tab_grp.grp_id(+)
 10     and (awc_tab.active_ind = 'Y' or awc_tab.silly_flag in (2,3))
 11  /

SILLY_FLAG      U_UID USER_ID
---------- ---------- ------------------------------
PSWD                           USER_NM                        FIRST_LOGIN
------------------------------ ------------------------------ -----------
LAST_LOGIN_     GRP_ID A     GRP_ID GRP_DESCR   OUTLET_CD IS_ADMIN
----------- ---------- - ---------- ---------- ---------- ----------
         1            tkyte
hello
                       Y


ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace package types
  2  as
  3      type refcur is ref cursor;
  4  end;
  5  /

Package created.

ops$tkyte@ORA817DEV> create or replace procedure we_do_not_use_sp_as_a_prefix( p_cur in out types.refcur, p_userId in varchar2, p_password in varchar2 )
  2  as
  3  begin
  4  open p_cur for '
  5  select *
  6    from ( select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  7                       when awc_tab.user_id = d.pUserId then 2
  8                       else 3
  9                   end silly_flag, awc_tab.*
 10             from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
 11            where awc_tab.user_id(+) = d.pUserId
 12          ) awc_tab, awc_tab_grp
 13   where awc_tab.grp_id = awc_tab_grp.grp_id(+)
 14     and (awc_tab.active_ind = ''Y'' or awc_tab.silly_flag in (2,3))' using p_userid, p_password;
 15  end;
 16  /

Procedure created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> variable x refcursor
ops$tkyte@ORA817DEV> exec we_do_not_use_sp_as_a_prefix( :x, 'tkyte', 'hello' )

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> print x

SILLY_FLAG      U_UID USER_ID
---------- ---------- ------------------------------
PSWD                           USER_NM                        FIRST_LOGIN
------------------------------ ------------------------------ -----------
LAST_LOGIN_     GRP_ID A     GRP_ID GRP_DESCR   OUTLET_CD IS_ADMIN
----------- ---------- - ---------- ---------- ---------- ----------
         1            tkyte
hello
                       Y


Creating dynamic stored procedure problem

September 23, 2008 - 11:47 am UTC

Reviewer: Arvind from NY, USA

Hi Tom,

We are using Oracle 9.2.0.8, while creating dynamic procedure I am getting ORA-06512 error, If I reformat the code it works fine. Please can you advice if I am doing anything wrong here.

-- Below is the snapshot of the block which gives ORA-06512 error.

DECLARE
l_cnt NUMBER ;
begin

EXECUTE IMMEDIATE ' DECLARE CNT NUMBER;
BEGIN SELECT Count(*) INTO :CNT FROM DUAL; end;'
USING OUT l_cnt ;
dbms_output . put_line ( ' - ' || l_cnt );

end ;

-- If i reformat the above by taking the declare begin to one line, It code works fine.

DECLARE
l_cnt NUMBER ;
begin

EXECUTE IMMEDIATE ' DECLARE CNT NUMBER; BEGIN SELECT Count(*) INTO :CNT FROM DUAL; end;'
USING OUT l_cnt ;
dbms_output . put_line ( ' - ' || l_cnt );

end ;

-- Error Message.
ORA-06550: line 1, column 22:
PLS-00103: Encountered the symbol "" when expecting one of the following:

begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
Tom Kyte

Followup  

September 24, 2008 - 6:25 pm UTC

is this perhaps a windows file executed on unix?

Creating dynamic stored procedure problem

September 23, 2008 - 11:47 am UTC

Reviewer: Arvind from NY, USA

Hi Tom,

We are using Oracle 9.2.0.8, while creating dynamic procedure I am getting ORA-06512 error, If I reformat the code it works fine. Please can you advice if I am doing anything wrong here.

-- Below is the snapshot of the block which gives ORA-06512 error.

DECLARE
l_cnt NUMBER ;
begin

EXECUTE IMMEDIATE ' DECLARE CNT NUMBER;
BEGIN SELECT Count(*) INTO :CNT FROM DUAL; end;'
USING OUT l_cnt ;
dbms_output . put_line ( ' - ' || l_cnt );

end ;

-- If i reformat the above by taking the declare begin to one line, It code works fine.

DECLARE
l_cnt NUMBER ;
begin

EXECUTE IMMEDIATE ' DECLARE CNT NUMBER; BEGIN SELECT Count(*) INTO :CNT FROM DUAL; end;'
USING OUT l_cnt ;
dbms_output . put_line ( ' - ' || l_cnt );

end ;

-- Error Message.
ORA-06550: line 1, column 22:
PLS-00103: Encountered the symbol "" when expecting one of the following:

begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

September 25, 2008 - 7:49 am UTC

Reviewer: Oleksandr Alesinskyy from Hofheim, Germany

Hi Arvind,

that's easy (and not related to Oracle version). In tge PL/SQL (as in most other programm languages) string literals can not cross line boundaries, i.e. they should start and stop on the same line.


Tom Kyte

Followup  

September 25, 2008 - 3:28 pm UTC

that is not true at all.

ops$tkyte%ORA10GR2> create table t ( x varchar2(30) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          insert into t values ( 'hello
  3  world' );
  4          insert into t values ( 'hello world' );
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from t;

X
------------------------------
hello
world

hello world



I would suspect there is some "bad character" that is not visible when they type it on screen but is in the file.
ops$tkyte%ORA10GR2> declare
  2          l_cnt number;
  3  begin
  4          execute immediate 'select count(*) from dual' into l_cnt;
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2          l_cnt number;
  3  begin
  4          execute immediate 'select count(*) from dual' into l_cnt;
  5  /
        execute immediate 'select count(*) from dual' into l_cnt;
                                                                 *
ERROR at line 4:
ORA-06550: line 4, column 59:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe

<b>Looks similar, both things "look" the same, but a dump of the file shows:
</b>
ops$tkyte%ORA10GR2> !od -c test.sql | tail
0000360   (   *   )       f   r   o   m       d   u   a   l   '       i
0000400   n   t   o       l   _   c   n   t   ;  \r  \n   e   n   d   ;
0000420  \r  \n   /  \r  \n   d   e   c   l   a   r   e  \r  \n  \t   l
0000440   _   c   n   t       n   u   m   b   e   r   ;  \r  \n   b   e
0000460   g   i   n  \r  \n  \t   e   x   e   c   u   t   e       i   m
0000500   m   e   d   i   a   t   e       '   s   e   l   e   c   t
0000520   <b>c   o   u   n   t   (   *   ) 001       f   r   o   m</b>       d
0000540   u   a   l   '       i   n   t   o       l   _   c   n   t   ;
0000560  \r  \n   /  \r  \n
0000565



there is a control character in there, so I think they have a bad character in there, cannot see it on screen and when they edit the file, the editor "fixed" it.

I am also encountering a same problem

February 15, 2017 - 10:08 am UTC

Reviewer: prabhakar from india

create or replace procedure plp_myapp_selectnew(my_flag in varchar2)
is
p_userid varchar2(40),

p_username varchar2(40),
p_password varchar2(100),
p_empid varchar2(40),
p_Dob date,
p_question varchar2(100),
p_answer varchar2(100),
p_panno varchar2(40),
p_companyname varchar2(100)

begin

if my_flag = 'username' then
select user_id from user_master where user_id=p_userid;
elsif my_flag = 'validatepan' then
select pannumber from OTC_REGISTRAION where pannumber =p_panno;
elsif my_flag = 'companyname' then
SELECT companyname
FROM OTC_INS_COMPANY;
end if;
end;

i don't know what excatly wrong with this procedure



it will throw the error message like this




Error: PLS-00103: Encountered the symbol "," when expecting one of the following:

:= ; not null default character
Line: 3
Text: p_userid varchar2(40),

Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

<an identifier> <a double-quoted delimited-identifier>
current delete exists prior
Line: 13
Text: begin

Error: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

end not pragma final instantiable order overriding static
member constructor map
Line: 23
Text: end;

Connor McDonald

Followup  

February 16, 2017 - 3:37 am UTC

1) semi colons not commas after variables

2) you must select data into something

eg

create or replace procedure plp_myapp_selectnew(my_flag in varchar2, p_userid in varchar2, p_panno number) 
is 
l_username varchar2(40);
l_panno varchar2(40);
l_companyname varchar2(100) ;

begin 

if my_flag = 'username' then 
select user_id into l_username from user_master where user_id=p_userid; 
elsif my_flag = 'validatepan' then 
select pannumber into l_panno from OTC_REGISTRAION where pannumber =p_panno; 
elsif my_flag = 'companyname' then 
SELECT companyname into l_companyname
FROM OTC_INS_COMPANY; 
end if; 
end;