great it's working
I missed the into option
many thanks

On 20 December 2011 13:43, Andrej Hopko <ado.ho...@gmail.com> wrote:

>  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 <ado.ho...@gmail.com> 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 Oracle-PLSQL@googlegroups.com
>> To unsubscribe from this group, send email to
>> oracle-plsql-unsubscr...@googlegroups.com
>> 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 Oracle-PLSQL@googlegroups.com
>> To unsubscribe from this group, send email to
>> oracle-plsql-unsubscr...@googlegroups.com
>> 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 Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> 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 Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> 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 Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

Reply via email to