| dynamic sql-loc_bang [message #37755] |
Tue, 26 February 2002 20:49  |
krishna
Messages: 141
Registered: October 1998
|
Senior Member |
|
|
I have two tables loc_bang and loc_tvm. The description of both tables are as follows.
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER(7,2),
deptno NUMBER(2)
I want to retrieve the number of people of a particular job type in a particular location dynamically.
This is the function i have written.
create or replace function get_no_of_emp(loc varchar2, job varchar2) return number is
no_of_emp number;
query varchar2(1000);
begin
query:='select count(*) from loc_'||loc|| ' where job=:job_title';
execute immediate query into no_of_emp using job;
return no_of_emp;
end;
I have written an anonymous block to achieve my goal which is as follows
begin
declare
num number;
num:=get_no_of_emp(tvm,prez);
dbms_output.put_line(num);
end;
When i run this block i get the following error.
ERROR at line 4:
ORA-06550: line 4, column 4:
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 time timestamp interval date binary national character
nchar
The symbol "<an identifier>" was substituted for "=" to continue.
ORA-06550: line 5, column 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 time timestamp interval date binary national charac
ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form current
Please help.
|
|
|
|
| Re: dynamic sql-loc_bang [message #37764 is a reply to message #37755] |
Wed, 27 February 2002 01:46  |
Manoj
Messages: 101
Registered: August 2000
|
Senior Member |
|
|
HI dear,U have do a little mistake.
Try this one
declare
num number:=0;
begin
num:=get_no_of_emp(tvm,prez);
dbms_output.put_line(num);
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
