great it's working
I missed the into option
many thanks
On 20 December 2011 13:43, Andrej Hopko <[email protected]> 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 <[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]
>> 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
>>
>
> --
> 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
>
--
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
