Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to DBMS

Reply
Occasional Contributor
Posts: 9

Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to DBMS

[ Edited ]

Is it possible to execute explicit SQL against a Teradata database to perform a CREATE/REPLACE PROCEDURE on the database? I am running into errors when I attempt to do this. The Teradata Stored Procedure was developed and tested in SQL Assistant and works without issue. When I move it over into SAS the 'REPLACE PROCEDURE...' Teradata SQL code portion fails. If I create the procedure using Teradata SQL Assistant I can successfully execute a 'CALL PROCEDURE();' statement using explicit pass through SQL.  Below is a simplified version of what I am trying execute.  The log first has this message twice:

 

NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation

marks.

 

followed by this Error:

 

ERROR: Teradata execute: Syntax error: Invalid SQL Statement.

 

proc sql noerrorstop;

connect to teradata(server="myserver" user="&usr" password="&pw" connection=global mode=teradata);

execute(

REPLACE PROCEDURE MYDB.my_proc (IN DB_name VARCHAR(128), IN TBL_name VARCHAR(128))

SQL SECURITY INVOKER

BEGIN

IF EXISTS(SELECT 1 FROM DBC.TablesV WHERE DatabaseName =Smiley Very HappyB_name AND TABLENAME = :TBL_name) THEN

CALL DBC.SysExecSQL('

INSERT INTO '||Smiley Very HappyB_name||'.'||:TBL_name||'

SELECT

AU_NUM

, OUTLET_ID

, sale_wk

, COUNT(DISTINCT account) AS accounts

FROM

BMGU_TEMP.ddd_startright;' );

ELSE

CALL DBC.SysExecSQL('

CREATE MULTISET TABLE '||Smiley Very HappyB_name||'.'||:TBL_name||' AS (

SELECT

AU_NUM

, OUTLET_ID

, sale_wk

, COUNT(DISTINCT account) AS accounts

FROM

mydb1.mytable

) WITH DATA

PRIMARY INDEX(AU_NUM, sale_wk);');

END IF;

END;

) by teradata;

 

The quoted text within both of the DBC.SysExecSQL() parts is considerably longer than what I have shown above, but I had to change the query to remove company information. Even though the text is within a execute() by teradata; statement and being passed to the DBMS, is it possibly an issue with quotes? Again, the entire REPLACE PROCEDURE... statement executes without issue on Teradata.

 

NOTE: Couldn't figure out how to turn off the emoji's, but anywhere there is an emoji should be Smiley Very HappyB_Name

 

Thanks.

Frequent Contributor
Posts: 81

Re: Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to DBMS

[ Edited ]

Interesting. I wouldn't think creating/replacing a procedure should be a problem. I've run all sorts of stuff in Oracle using explicit passthrough, and I don't think Teradata would be different. My guess is that it is a quoting problem, I've run into similar problems and they're always a pain to track down. Have you tried creating a much simpler procedure, ideally one without all the internal quoting, to see if that works?  If so, then you'll know it's a quoting issue and can start adding parts back in piecemeal to try and track down what's going wrong.

Occasional Contributor
Posts: 9

Re: Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to DBMS

I tried a much smaller/simpler procedure. I no longer get the note about long string/unbalanced quotes but I still get the error "

ERROR: Teradata execute: Syntax error: Invalid SQL Statement." In looking in the log and what is being executed by Teradata it appears to all by syntactically correct. There is some white space added due to tabs in my formatting/indenting of the procedure but I wouldn't think that is impacting anything.  Here's the log excerpt:

 

78 proc sql noerrorstop;

79 connect to teradata(server="myserver" user="&usr" password="&pw" connection=global mode=teradata);

80 execute(

81 REPLACE PROCEDURE MYDB.my_proc (IN DB_name VARCHAR(128), IN TBL_name VARCHAR(128))

82 SQL SECURITY INVOKER

83 BEGIN

84 IF EXISTS(SELECT 1 FROM DBC.TablesV WHERE DatabaseName = Smiley Very HappyB_name AND TABLENAME = :TBL_name) THEN

85 CALL DBC.SysExecSQL('

86 INSERT INTO '||Smiley Very HappyB_name||'.'||:TBL_name||'

87 SELECT TOP 50 ACCT_NUM, OPEN_DT

88 FROM DB1.ACCOUNTS;' );

89 ELSE

90 CALL DBC.SysExecSQL('

91 CREATE MULTISET TABLE '||Smiley Very HappyB_name||'.'||:TBL_name||' AS (

92 SELECT TOP 50 ACCT_NUM, OPEN_DT

93 FROM DB1.ACCOUNTS

94 ) WITH DATA

95 PRIMARY INDEX(ACCT_NUM);');

96 END IF;

97 END;

98 ) by teradata;

 

TERADATA_0: Executed: on connection 0

REPLACE PROCEDURE MYDB.my_proc (IN DB_name VARCHAR(128), IN TBL_name VARCHAR(128)) SQL SECURITY INVOKER BEGIN IF

EXISTS(SELECT 1 FROM DBC.TablesV WHERE DatabaseName = Smiley Very HappyB_name AND TABLENAME = :TBL_name) THEN CALL DBC.SysExecSQL(' INSERT INTO

'||Smiley Very HappyB_name||'.'||:TBL_name||' SELECT TOP 50 ACCT_NUM, OPEN_DT FROM DB1.ACCOUNTS;' ); ELSE CALL

DBC.SysExecSQL(' CREATE MULTISET TABLE '||Smiley Very HappyB_name||'.'||:TBL_name||' AS ( SELECT TOP 50 ACCT_NUM, OPEN_DT FROM

DB1.ACCOUNTS ) WITH DATA PRIMARY INDEX(ACCT_NUM);'); END IF; END;

 

 

DBMS_TIMER: summary statistics

DBMS_TIMER: total SQL execution seconds were: 0

DBMS_TIMER: dbiopen/dbiclose timespan was 0.

ERROR: Teradata execute: Syntax error: Invalid SQL Statement.

 

Frequent Contributor
Posts: 81

Re: Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to DBMS

[ Edited ]

What do you have set for the SASTRACE options?  I usually run my code with the following so any Oracle error messages are passed back to the SAS Log, that may help determine why Teradata doesn't like the code if you haven't already.

OPTIONS  NOSTSUFFIX;
OPTIONS SASTRACELOC=SASLOG; OPTIONS SASTRACE=',,t,dbs';

 

Occasional Contributor
Posts: 9

Re: Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to DBMS

This is my normal settings for those options:

 

option debug=dbms_timers

sastrace=',,,d'

sastraceloc=saslog nostsuffix;

 

I took it a couple of steps farther to get more detailed info and went with this and re-ran and it only provided a little more information like the timing and some return codes. Not sure if there's a way to see what the return codes mean.

SASTRACE=',,dt,dsa';

 

FROM THE LOG:

ACCESS ENGINE: Entering dbiclose

TERADATA: trclose()

 

Summary Statistics for TERADATA are:

Total SQL execution seconds were: 0.002083

Total seconds used by the TERADATA ACCESS engine were 0.002773

 

ACCESS ENGINE: DBICLOSE open_id 0, connect_id 0

ACCESS ENGINE: Exiting dbiclos with rc=0X00000000

ACCESS ENGINE: Exiting execute with rc=0X8003180C

ERROR: Teradata execute: Syntax error: Invalid SQL Statement.

 

Frequent Contributor
Posts: 81

Re: Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to DBMS

[ Edited ]

That's unfortunate, Oracle is usually quite helpful with error codes on stuff like this, but I've only used Teradata very briefly so don't know what else I can offer.  The only other thing I'd suggest is to continue to pare down the code until you get a CREATE PROCEDURE that actually works, even if it's not really doing anything besides creating an empty procedure or something.  Once you get something that actually runs, you can start adding in bits and pieces until the errors come back, which should tell you what's going wrong.  Otherwise I guess you can open a SAS Support ticket, or maybe someone else will have better insight into Teradata specific troubleshooting.

 

Sorry I wasn't of more help.

Occasional Contributor
Posts: 9

Re: Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to DBMS

I appreciate your responses and assistance. I'll probably first proceed with a SAS Support Ticket just to confirm if there's some limitation with the SAS/ACCESS Teradata preventing this. As I cannot see anything that would be preventing it from running. I can take the code that is sent to Teradata verbatim from the SAS log, paste into Teradata SQL Assistant and execute it without issue.

Ask a Question
Discussion stats
  • 6 replies
  • 186 views
  • 1 like
  • 2 in conversation