Execute String without knowing columns

[virtualdatabase]

Is it possible to build a procedure that executes a dynamically built string WITHOUT knowing the columns in advance?

[shawkins]

Only if returned the columns as an array.

[virtualdatabase]

Do you have an example ?

[shawkins]

EXEC IMMEDIATE 'SELECT (1,2)' AS VAL INTEGER[];

[virtualdatabase]

In this case I assume it'd be a string[] but I don't see that option when defining the "column" of the result-set...

What am I missing.. should it be a clob?

[rareddy]

I think it would be help if you can explain in little bit more detail as to what you are trying to do?

[shawkins]

Designer UI support for array types may be lacking.  If you manually update the transformation it should validate.  If not, then that's an issue.

[virtualdatabase]

the procedure validates but I cant create a resultset that uses the array type

[virtualdatabase]

So I have come up with a proc that **should** be what I want but running into a new problem..

BEGIN

  DECLARE integer VARIABLES.NBR;

  DECLARE integer VARIABLES.counter = 1;

  DECLARE string VARIABLES.execString = '';

  DECLARE string VARIABLES.LoopString = '';

  DECLARE string VARIABLES.ColumnsString = '';

  DECLARE string VARIABLES.BaseexecString = '';

  VARIABLES.BaseexecString = ('Select  *  FROM ' || Util.CLIENT_RNC_HISTORY_DYNAMIC.INPUT_TableName);

  VARIABLES.NBR = (SELECT Util.TableCounts.NbrOfTables FROM Util.TableCounts WHERE Util.TableCounts.ConcernedTables = Util.CLIENT_RNC_HISTORY_DYNAMIC.INPUT_TableName);

  VARIABLES.ColumnsString = (SELECT Util.POC_Columns.ColumnsString FROM Util.POC_Columns WHERE Util.POC_Columns.ConcernedTables = Util.CLIENT_RNC_HISTORY_DYNAMIC.INPUT_TableName);

  WHILE(VARIABLES.counter <= VARIABLES.NBR)

  BEGIN

  VARIABLES.LoopString = (((VARIABLES.BaseexecString || '_') || CASE WHEN VARIABLES.counter < 10 THEN ('0' || VARIABLES.counter) ELSE VARIABLES.counter END) || ' UNION ALL ');

  VARIABLES.execString = ((VARIABLES.execString || ' ') || VARIABLES.LoopString);

  VARIABLES.counter = (VARIABLES.counter + 1);

  END

  VARIABLES.execString = substring(VARIABLES.execString, 1, (length(VARIABLES.execString) - 6));

  EXECUTE IMMEDIATE VARIABLES.execString AS VARIABLES.COlumnsString;

END

The Variables.Columns String is for example this:  "CKEY string,

CDOCTYPE string,

CFLDCODE string,

CFLDVALUE string,

IROWNUM biginteger"

But I get the following error when validating:

ERROR: Error in parsing of sql - Invalid simple identifier format: [VARIABLES.COlumnsString]

Is this invocation of execute immediate with 2 variables NOT possible?

[shawkins]

> EXECUTE IMMEDIATE VARIABLES.execString AS VARIABLES.COlumnsString;

No you cannot use an expression in the AS clause.  It is for declaring the columns/types.

[virtualdatabase]

I know what comes after the " AS " and that is the contents of the variable VARIABLES.ColumnsString.

I assume you are stating that I cannot use an expression at all for the Execute Immediate except for the first part, the exec string.

[shawkins]

> I know what comes after the " AS " and that is the contents of the variable VARIABLES.ColumnsString.

> I assume you are stating that I cannot use an expression at all for the Execute Immediate except for the first part, the exec string.

Only the sql in "execute immediate" is interpreted the rest needs to follow the expected syntax.