Creating dynamic stored procedure problem
Boris Cherkalin, July 02, 2002 - 8:57 am UTC
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
/
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
Boris, July 02, 2002 - 11:34 am UTC
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;
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
Cory D. Wittenborn, July 02, 2002 - 11:57 am UTC
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
Boris, July 02, 2002 - 4:53 pm UTC
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;
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
Boris Cherkalin, July 08, 2002 - 11:27 am UTC
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;
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
Boris Cherkalin, July 08, 2002 - 4:34 pm UTC
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
Boris Cherkalin, July 11, 2002 - 3:56 pm UTC
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 doesnt return anything.
Please help.
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
Boris Cherkalin, July 12, 2002 - 9:50 am UTC
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;
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>
A reader, January 22, 2003 - 8:40 pm UTC
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.
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....
venkat, May 27, 2003 - 1:58 pm UTC
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.
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!!!
A reader, May 27, 2003 - 7:28 pm UTC
A reader, June 14, 2004 - 3:16 pm UTC
BULK COLLECT
A reader, December 11, 2004 - 8:29 am UTC
A reader, December 11, 2004 - 8:55 am UTC
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following
Shriram Mohite, December 27, 2004 - 8:12 am UTC
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;
December 27, 2004 - 10:21 am UTC
one word for you:
elsif
errors while executing
nimisha, February 16, 2005 - 10:29 am UTC
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;
/
February 16, 2005 - 10:55 am UTC
do you see your missing semi colon after return?
Iam getting an error
murali, January 03, 2006 - 4:20 am UTC
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.
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
lakshmi, January 25, 2006 - 12:00 pm UTC
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
L, February 01, 2006 - 12:30 pm UTC
Never mind!! Got it
Mutating table....
Gerardo Pimentel, March 14, 2006 - 2:16 pm UTC
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
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
Benexxavier, May 25, 2006 - 4:55 am UTC
PLS-00103: Encountered the symbol "@" when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier>
current
May 25, 2006 - 1:44 pm UTC
ok? so?
statspack error after upgrading
ravs, July 07, 2006 - 3:21 am UTC
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
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
manikandan, September 19, 2006 - 8:55 am UTC
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
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
manikandan.k, September 20, 2006 - 9:18 am UTC
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
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
KRISHNA, May 16, 2008 - 12:25 am UTC
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
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
Arvind, September 23, 2008 - 11:47 am UTC
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 24, 2008 - 6:25 pm UTC
is this perhaps a windows file executed on unix?
Creating dynamic stored procedure problem
Arvind, September 23, 2008 - 11:47 am UTC
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
Oleksandr Alesinskyy, September 25, 2008 - 7:49 am UTC
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.
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
prabhakar, February 15, 2017 - 10:08 am UTC
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;
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;