| Dynamic table selection [message #18672] |
Sun, 10 February 2002 19:07  |
diaz
Messages: 58
Registered: October 2001
|
Member |
|
|
i have a code like this,
i wanted to make the selection is dynamic.. from the user specified table,
the parameter for the code is
@ code pepper 01
declare
name varchar(6);
passvar varchar(2);
sal number(9);
atable varchar(10);
begin
name := &1;
passvar := &2;
atable := 'tagihan' || passvar
select salary into sal from atable where name=name;
end;
i have 2 question.
1. the error,
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
. ( * @ % & = - + ; < / > at in mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling
The symbol ";" was substituted for "SELECT" to continue.
i guess .. the compiler didn't recognize the atable .. and i can't figure it out..
2. without adding the select statement..
it already turns out well (tested on dbms_output.put_line) but only that the 01 is outputted as 1 ... how to make that as 01 ?
please help me..
|
|
|
|
| Re: Dynamic table selection [message #18676 is a reply to message #18672] |
Sun, 10 February 2002 22:48  |
Satish Shrikhande
Messages: 167
Registered: October 2001
|
Senior Member |
|
|
do it with execute immediate
Create Or Replace procedure Fetchrows as
row integer;
cursor c1 is
select tname from tab;
begin
for z in c1 loop
execute immediate
'select count(*) from '|| z.tname into row;
dbms_output.put_line(z.tname||' - '||row);
end loop;
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
