Externals
What is the difference between the DIRECT and CALL DIRECT statements?
DIRECT is invoked immediately at statement compile time. CALL DIRECT is invoked at run-time. Both commands allow execution of SQL statements. Common uses include table creation, truncation, the adding of indices after a load, and execution of stored procedures.
Tip: you can use DIRECT within a script to set up your environment – to create and populate tables, for example – before the run-time logic is executed.
Can I call an Oracle stored procedure from within my Warehouse scripts? And I need to pass it a few values and have it modify a few values.
You can invoke stored procedures using standard Oracle syntax through the DIRECT or CALL DIRECT statements, since these statements simply send a string as-is to the Oracle RDBMS. The string is not required to be static and can be built on the fly.
Let’s assume an Oracle instance that contains a table named foo composed of a single column named val that is defined as NUMBER type.
The procedure addrec that inserts a row into foo is defined as follows :
as BEGIN
insert into scott.foo values (numx);
commit work;
END;
Next is the execution of a script that invokes this stored procedure, inserting a new row into foo :
1>open db oracle scott/tiger home=/b/u01/oradata/ora sid=ora8
2>
3>print “Before stored procedure”
4>read adrec_before = db.foo
[Using serial read]
5> print adrec_before
6>endread
7>print
8>print “—-“
9>
10>call direct(db,”begin scott.addrec(99); end;;”)
11>
12>print
13>print “After stored procedure”
14>read adrec_after = db.foo
[Using serial read]
15> print adrec_after
16>endread
Before stored procedure
1.000000
2.000000
3.000000
—-
After stored procedure
1.000000
2.000000
3.000000
99.000000
One restriction is that you can’t see the stored procedure’s return parameters unless, of course, the processing has generated an ORA error, in which case Warehouse will return that error. A solution to this restriction is to have the stored procedure write the result(s) or the result set(s) to a temporary table or tables that can be subsequently examined by the calling script when the CALL DIRECT returns.
Tip: if implementing a temporary table in this manner, consider a unique naming scheme for the table to avoid name collisions with other scripts that are doing the same thing. And don’t forget to drop the table when you are done with it.
Can you give me an example of how to capture a non-tabular return parameter from a SQL query?
In the following example, the intermediate table temp_count is created as a means of storing and retrieving the row count of table prod_table. The RDBMS used in this example is Microsoft SQL Server.
open db odbc source_ss7
define count : image i2
direct db, “drop table temp_count”; ignore errors
direct db, “create table temp_count(val integer)”
call direct (db,”insert into temp_count select count(*) from prod_table “)
read x = db.temp_count
setvar count = x.val
endread
direct db, “drop table temp_count”
Can you tell me if it’s possible to call an external COBOL or C subroutine from Warehouse?
No, there are no programmatic exits that can be invoked from the Warehouse scripting environment.
Can I pass commands to the underlying system?
Warehouse provides two mechanisms for passing commands to a spawned shell.
The exclamation mark and equivalent colon commands (‘!’ and ‘:’) are executed immediately during the script compilation phase, along with invocations of DIRECT and OPEN statements.
The SYSTEM function is invoked during the execution phase of a script. For example, from within Windows, to list all files of format to*.txt in the current working directory, first during compilation, then during execution:
1> !dir to*.txt
Volume in drive D has no label.
Volume Serial Number is 1039-7340
Directory of D:\support\eps
08/02/2000 12:34p 1,322 todo.txt
1 File(s) 1,322 bytes
0 Dir(s) 1,940,754,432 bytes free
2> call system(“dir to*.txt”)
Volume in drive D has no label.
Volume Serial Number is 1039-7340
Directory of D:\support\eps
08/02/2000 12:34p 1,322 todo.txt
1 File(s) 1,322 bytes
0 Dir(s) 1,940,754,432 bytes free
This function is operating system dependent. >
Tip: the Warehouse client is blocked until the spawned child process returns.
How can I send email from a Warehouse script?
The solution is platform-dependent: you can invoke an external mailer application by means of either the SYSTEM function or the exclamation mark or colon commands.
For example, to invoke mailx on a Solaris system, where the first command is run immediately at script compilation time and the second is run at execution time:
1> !mailx -s “Load started” user@mail.com < filename
2> call system (“mailx -s \”Load completed\” user@mail.com < filename”)
For simplicity, the mailer syntax can be wrapped in a shell script for invocation from the Warehouse script.
How can I send a script listing and compile errors to the same file?
In these examples, wh is the WH client, script the WH script, and outfile the listfile.
Windows 2000 and NT4 dos shell
wh script > outfile 2>&1
csh and bash
wh script >& outfile
ksh and bourne
wh script > outfile 2>&1
MPE/iX ci
run wh.whii.taurus;info=”script” > outfile
There is no support for redirecting output from standard error on Windows 95 or Windows 98