| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Calling myscript.sql from within plsql block?
Brian,
You cannot mix PL/SQL commands (like IF) and SQL*Plus commands (like @, which is a synonym for START) in this manner. You may want to experiment with the SPOOL and START commands. Here are some scripts that will do something similar to what you are trying to accomplish. Of course, I'm going to strip away all your specific schema info since you did not post any DDL. Using Scott's sample data, running dynamic_script.sql will run another script (one that prints 'Hello, Department 20') only if the given user is in department 20. Not very creative, I know, but the technique should work for you. In the SAMPLE SESSION output, note that the first employee (7844) is not a member of Department 20, but the second employee (7902) is. Hope this helps!
[BEGIN SAMPLE SESSION:]
SQL> @c:\temp\dynamic_script.sql
Enter value for employee_number: 7844
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> @c:\temp\dynamic_script.sql
Enter value for employee_number: 7902
PL/SQL procedure successfully completed.
Hello, Department 20
PL/SQL procedure successfully completed. [END SAMPLE SESSION] [BEGIN FILE c:\temp\dynamic_script.sql:] variable command varchar2(200)
declare
v_deptno number;
begin
select deptno
into v_deptno
from scott.emp
where empno = &employee_number;
if v_deptno = 20 then
:command := '@c:\temp\myscript.sql';
else
:command := 'exec null';
end if;
end;
/
set head off
set termout off
spool c:\temp\runscript.sql
print command
spool off
set head on
set termout on
@c:\temp\runscript.sql
[END FILE]
[BEGIN FILE c:\temp\myscript.sql:]
set serveroutput on
begin
dbms_output.put_line('Hello, Department 20');
end;
/
[END FILE]
"Brian Peasey" <bpeasey_at_doncarsys.com> wrote in message
news:a%hA8.8776$uE2.547913_at_news2.calgary.shaw.ca...
> Hi,
>
> I need to retrieve a value called 'dbversion' from on of our user tables
and
> then run
> one or more .sql files based on the value retrieved. I was going to create
a
> little
> procedure to do this but can't get the it to work correctly.
>
> variable dbversion number
> begin
>
> select to_number(control_value) into :dbversion
> from nat.spot_control
> where upper(control_name) = 'DBVERSION';
>
> if dbversion < 337 then
> @c:\avanue\mainscriptORAFEB0502.sql
> end if
>
> end;
>
> Dbversion is 337 but the script.sql runs anyways. Any suggestions,
> or alternative approaches to this problem? .
>
> Best Regards,
> Brian
>
>
>
Received on Tue May 14 2002 - 11:22:44 CDT
 |
 |