Dynamic SQL [message #371440] |
Thu, 19 October 2000 09:24 |
Philippe Koulmann
Messages: 1 Registered: October 2000
|
Junior Member |
|
|
In a function, I need build a select statement
which depends on an argument of the function.
eg:
function(v_table)
nbrow number;
begin
select count(*) into nbrow
from v_table;
return nbrow;
end function;
This syntax doesnt work...
Any idea? Thanks in advance
|
|
|
Re: Dynamic SQL [message #371453 is a reply to message #371440] |
Fri, 20 October 2000 20:01 |
LUNA
Messages: 4 Registered: October 2000
|
Junior Member |
|
|
TRY THIS
function(v_table) nbrow number;
declare
sql_select VARCHAR2(200);
begin
sql_select='select count(*) into ' ||nbrow || 'from :tabla';
EXECUTE IMMEDIATE sql_select USING vtable;
return nbrow;
end function;
Please, tell me if it's correct
|
|
|
Re: Dynamic SQL [message #371454 is a reply to message #371440] |
Fri, 20 October 2000 20:18 |
LUNA
Messages: 4 Registered: October 2000
|
Junior Member |
|
|
I Think this is the correct to oracle8i
function(v_table varchar2) nbrow number;
declare
sql_select VARCHAR2(200);
begin
sql_select='select count(*) into :brow from ' ||v_table;
EXECUTE IMMEDIATE sql_select USING nbrow;
return nbrow;
end function;
Please, tell me if it's correct
|
|
|
Re: Dynamic SQL [message #371455 is a reply to message #371440] |
Fri, 20 October 2000 20:25 |
LUNA CORRECT
Messages: 1 Registered: October 2000
|
Junior Member |
|
|
I'm sorry, I think the correct, if you use oracle8i, it's the next
function(v_table varchar2) nbrow number;
declare
sql_select VARCHAR2(200);
begin
sql_select='select count(*) into :brow from ' ||v_table;
EXECUTE IMMEDIATE sql_select USING :nbrow;
return nbrow;
end function;
Please, tell me if it's correct
|
|
|