export filename

[Thiko]

I would like to pass in the users schema name and the date of the export and the sequenceid from a database sequence called ExportDumpID for the name given to the export dump file.

So far I have the date and sequence passed into the filename using sqlplus scripting.

Then I have a pl-sql function (within a package body) which returns the username of the user currently logged in.

Any ideas on how to now put/syntax the username returned by the function into the sqlplus script to be returned into the export filename like the date and sequence number?

To become for example

betty_08JUN2001_45.dmp
_______________________________________

column user new_val user
column today new_val dt
column sequence new_val seq

SELECT TO_CHAR(sysdate,'dd-mm-yyyy HH24:MI') today FROM dual;
SELECT ExportDumpID.NEXTVAL seq FROM dual;

host exp FILE=exp_&user_&dt_&seq.dmp OWNER=Y GRANTS=Y ROWS=Y COMPRESS=Y

exit
________________________________________

--
-- Function to return the username of the current user for use in the export file name.
--
FUNCTION get_username (current_username IN VARCHAR2) RETURN VARCHAR2
IS
result NUMBER;
result2 NUMBER;
result3 NUMBER;
result4 VARCHAR2(10);
BEGIN
SELECT COUNT(user_id) INTO result FROM dba_users
WHERE username = current_username;
IF result > 0 THEN
SELECT user_id INTO result FROM dba_users
WHERE username = current_username;
END IF;
SELECT count(userid) INTO result2 FROM permissions
WHERE userid = result;
IF result2 > 0 THEN
SELECT userid INTO result2 FROM permissions
WHERE userid = result;
END IF;
SELECT COUNT(userid) INTO result3 FROM permissions
WHERE userid=result;
IF result3 > 0 THEN
SELECT username INTO result4 FROM dba_users
WHERE username=current_username;
END IF;
RETURN result4;
END get_username; Many Thanks.

Thiko!

 

[sem]

set feedback off
set pages 0
set timing off

spool com.sql
select 'host ''exp FILE=exp_'
||user||'_'||TO_CHAR(sysdate,'dd-mm-yyyy HH24:MI')
||ExportDumpID.NEXTVAL
||'.dmp OWNER=Y GRANTS=Y ROWS=Y COMPRESS=Y'''
from dual
/
spool off
@com.sql

As for date format mask, are you sure your OS understands filenames with blanks as a parameter??? :-(
If this is not the case, add some quotation marks to the result filename or ommit time part of date format mask.