All Forums Database
Deepakji 5 posts Joined 06/14
23 Aug 2016
Can we pass object as parameter in a macro ?

For eg : 

replace macro database1.macro1(databasename char(50),tablename char(50))



Select count(*) from :databasename.:tablename;

Can someone let me know how to fix this issue ? 
REPLACE MACRO Failed.  [3707] Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'from' keyword and ':'.

bhavesh_09 3 posts Joined 06/12
23 Aug 2016

Try execute immediate.

tomnolan 594 posts Joined 01/08
23 Aug 2016

No, you cannot pass an object name as a macro parameter. Macro parameters cannot be used as database object names.

ToddAWalter 316 posts Joined 10/11
23 Aug 2016

Macro parameters are designed for constant substitution only. They can appear only in the places in a statment where a constant value can appear (and not every one of those either - like options in DDL.)
To fill in object names, IN lists, WHERE clauses, ORDER BY lists,... one needs to be able to do text substitution and string manipulation. To do this, one needs to use a stored procedure where one can do any kind of desired string manipulation to create the text of the SQL statement, then execute the resulting string.

Deepakji 5 posts Joined 06/14
29 Aug 2016

thanks Todd .. after your suggestion i tried with stored procedure .. but still i m a bit confused how to implement it with stored procedure.. can u please guide me ?
I m trying it .. but i m getting errors .. 

REPLACE PROCEDURE sysdba.new_date2 (tablename1 varchar(200),databasename1 varchar(200))




declare create_user_sql char(500);


sel  tablename from dbc.tables where tablename=tablename1 and databasename=databasename1;

open date1;


set create_user_sql='sel count(*) from '||databasename1||'.'||tablename1||';';

call dbc.sysexecsql(create_user_sql);




Here i guess when i use sel statement in sysexecsql it is not accepting .. Could you please provide a solution for this ?


Fred 1096 posts Joined 08/04
29 Aug 2016

You can't use SysExecSQL for a data-returning statement. You need to use a second (dynamic) cursor:
DECLARE sel_csr CURSOR FOR sel_stmt;
PREPARE sel_stmt FROM create_user_sql;
OPEN sel_csr;

Deepakji 5 posts Joined 06/14
08 Sep 2016

Thanks Fred.. I was able to achieve through stored procedure.. thanks for your support

You must sign in to leave a comment.