i meant it this way:
SET SERVEROUTPUT ON
DECLARE
v_table VARCHAR2(30 BYTE) := 'table name';
v_date_col VARCHAR2(30 BYTE) := 'date column';
v_result NUMBER(12) := 0;
v_sql VARCHAR2(4000 BYTE);
BEGIN
v_sql := 'SELECT COUNT(*) cc
FROM ' || v_table || '
WHERE ' || v_date_col || ' >= SYSDATE-20 ';
EXECUTE IMMEDIATE v_sql INTO v_result;
DBMS_OUTPUT.PUT_LINE('number of rows is : ' || v_result);
END;
/
SET SERVEROUTPUT OFF
if there is still something what I don't understand, just write back
hoppo
On 20. 12. 2011 11:19, Mohammed Iyad wrote:
Hi,
ok I know that but the trick is how to retrieve the count(*) results
into variable without using create table or insert into table then
read it from that table,
On 20 December 2011 12:09, Andrej Hopko <[email protected]
<mailto:[email protected]>> wrote:
Hi,
look up uses of execute immediate
create your own dynamic query with table name taken from
parameter v_table and column name
essentially you create string with query v_sql := 'SELECT
* FROM ' || v_table || ' WHERE ' || v_col || ' ..... ';
then its something like EXECUTE IMMEDIATE v_sql INTO
result; (I've improvised syntax - google up correct one)
regards
hoppo
On 20. 12. 2011 10:33, Mohammed Iyad wrote:
Hi friends,
I collect a large amount of data every day, from various tables,
I like to create a function has two in parameter (table name &
date field) and it's out parameter should be row count
as the follow:
create or replace function TABLE_ROW_COUNT (v_table IN VARCHAR2,
v_date_col IN varchar2) return number is
Result number;
begin
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO Result
FROM v_table
WHERE v_date_col >= trunc(SYSDATE-1);
return(Result);
end ;
the problem it doesn't accept table name as variable,
is their any other way around it,
many thanks in advance,
Best Regards,
Iyad
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to
[email protected] <mailto:[email protected]>
To unsubscribe from this group, send email to
[email protected]
<mailto:[email protected]>
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
<mailto:[email protected]>
To unsubscribe from this group, send email to
[email protected]
<mailto:[email protected]>
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
