Greetings

I'm trying to include search criteria in below script's query ('select * from ' || p_tname ;) to fetch only those rows/employees which were hired during last 24 hours.

I have tried below --

SQL> create or replace procedure dump_table_to_csv( p_tname in varchar2, p_filename in varchar2)
authid current_user /* <<<== if you want... runs as "invoker" and runs with ROLES */
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
2 3 4 5 6 7 l_status integer;
l_sysmodtime date;
l_query varchar2(1000)
default 'select * from ' || p_tname ||' where hiredate >= '||to_char(sysdate-1,'dd-mm-yyyy hh24:mi:ss');
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( 'XXD_UTL', p_filename, 'w' );
execute immediate
8 9 10 11 12 13 14 15 16 17 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
utl_file.put( l_output,
l_separator || '"' || l_descTbl(i).col_name || '"' );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
18 19 20 21 22 23 24 l_separator := ',';
end loop;
utl_file.new_line( l_output );
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
25 end loop;
dbms_sql.close_cursor(l_theC 26 ursor);
27 28 29 30 31 32 33 34 35 36 37 38 utl_file.fclose( l_output );
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
39 40 41 42 43 raise;
44 end;
45 /

Procedure created.

SQL> exec dump_table_to_csv('scott.emp','scott.csv');
BEGIN dump_table_to_csv('scott.emp','scott.csv'); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "SCOTT.DUMP_TABLE_TO_CSV", line 43
ORA-06512: at line 1


SQL>

Thanking you in anticipation

Regards,
Ahmad

SQL> set serverout on
SQL> declare
  2   p_tname varchar2(10) := 'EMP';
  3   l_query varchar2(1000) default 'select * from ' || p_tname ||' where hiredate >= '||to_char(sysdate-1,'dd-mm-yyyy hh24:mi:ss');
  4  begin
  5    dbms_output.put_line(l_query);
  6  end;
  7  /
select * from EMP where hiredate >= 12-03-2018 10:48:35

PL/SQL procedure successfully completed.

SQL> select * from EMP where hiredate >= 12-03-2018 10:48:35;
SP2-0552: Bind variable "48" not declared.

SQL> set serverout on
SQL> declare
  2   p_tname varchar2(10) := 'EMP';
  3   l_query varchar2(1000)
  4     default 'select * from ' || p_tname ||' where hiredate >= to_date('''||to_char(sysdate-1,'dd-mm-yyyy hh24:mi:ss')||''',''dd-mm-yyyy hh24:mi:ss'')';
  5  begin
  6    dbms_output.put_line(l_query);
  7  end;
  8  /
select * from EMP where hiredate >= to_date('12-03-2018 10:50:01','dd-mm-yyyy hh24:mi:ss')