pl-sql variables

[Thiko]

Hi

I'm getting a bit confused (surprise surprise).

I have this pl-sql block where if function dba_services.permissions = 0 then it will set status to failed in the jobrequest table, but if it is not = to 0 i would like it to go onto the next step in the application - Which is to do an export of the users schema.

But how can I do this?

I don't think you can 'run/execute' a pl-sql variable can you?

Any ideas would be much appreciated.

set serveroutput on

DECLARE
job_instance_id NUMBER; --variable to be automatically passed in later.
runexport VARCHAR2(200);
BEGIN
IF dba_services.permissions = 0 THEN
UPDATE jobrequest SET status = (SELECT status FROM status WHERE description = 'FAILED')
WHERE id = job_instance_id;
UPDATE jobrequest SET description = ('You do not have permissions to use this service. Contact the DBA.')
WHERE id = job_instance_id;
ELSE
SELECT 'host ''exp FILE=exp_'
||user||'_'||TO_CHAR(sysdate,'dd-mm-yyyy_HH24:MI:SS')||'_seq'
||ExportDumpID.NEXTVAL
||'.dmp OWNER=Y GRANTS=Y ROWS=Y COMPRESS=Y'''
INTO runexport
FROM dual;
END IF;

IF runexport > 0 THEN 'host runexport';
END IF;

COMMIT;
END;

Many Thanks.

Thiko!

 

[sem]

You can spool your command into some file according to your conditions and run it, something like that:

spool somefile

.....
if (some condition) then
dbms_output.put_line( runexport );
else
dbms_output.put_line( 'exit' );


......
spool off
@somefile