Calling a Teradata Macro or Stored Procedure Using SQL Passthru
x

SQL Passthru is supported for Teradata macros and stored procedures.

Macros need to be developed within Teradata using the CREATE or REPLACE MACRO command. Procedures need to be developed within Teradata using the CREATE PROCEDURE command.

You must call a macro in the same transaction mode in which it was compiled. For information about setting the transaction mode, see Setting the Transaction Mode Within a Teradata Connection. To find out which transaction mode is currently in effect, issue the HELP session command:

SQL SQLDBC HELP SESSION;
TABLE FILE SQLOUT PRINT TRANSACTION_SEMANTICS
END

Before you can call a stored procedure or a macro, you must set the connection accordingly, either using the Web Console or by issuing the SET MACRO command

SQL SQLDBC SET MACRO ON|OFF

where:

ON

Enables one to call a macro. This is the default.

OFF

Enables one to call a stored procedure.


Top of page

Example: Calling a Macro

This is an example of the syntax for calling a macro:

ENGINE SQLDBC
EX SAMPLE PARM1,PARM2,PARM3...;
TABLE FILE SQLOUT
END

Top of page

Example: Calling a Stored Procedure

The supported syntax to call a stored procedure is shown below.

ENGINE SQLDBC
EX SAMPLE PARM1,PARM2,PARM3...;
TABLE FILE SQLOUT
END

When using the adapter with:


Top of page

Example: Teradata Stored Procedure
CREATE OR REPLACE PACKAGE pack1 AS
TYPE nfrectype IS RECORD (
employee NF29005.EMPLOYEE_ID5%TYPE,
ssn5     NF29005.SSN5%TYPE,
l_name   NF29005.LAST_NAME5%TYPE,
f_name   NF29005.FIRST_NAME5%TYPE,
birthday NF29005.BIRTHDATE5%TYPE,
salary   NF29005.SALARY5%TYPE,
joblevel NF29005.JOB_LEVEL5%TYPE);
TYPE nfcurtype IS REF CURSOR RETURN nfrectype ;
PROCEDURE proc1(c_saltable IN OUT nfcurtype);
END pack1 ;
/
CREATE OR REPLACE PACKAGE BODY pack1 AS
PROCEDURE proc1 (c_saltable IN OUT nfcurtype)
IS
BEGIN
OPEN c_saltable FOR SELECT 
EMPLOYEE_ID5,SSN5,LAST_NAME5,FIRST_NAME5,BIRTHDAT
E5,SALARY5,JOB_LEVEL5 FROM NF29005;
END proc1 ;  -- end of procedure
END pack1; -- end of package body
/


iWay Software