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