11 Replies Latest reply on Aug 4, 2015 2:21 PM by shawkins

    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?

        • 1. Re: Execute String without knowing columns
          shawkins

          Only if returned the columns as an array.

          • 2. Re: Execute String without knowing columns
            virtualdatabase

            Do you have an example ?

            • 3. Re: Execute String without knowing columns
              shawkins

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

              • 4. Re: Execute String without knowing columns
                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?

                • 5. Re: Execute String without knowing columns
                  rareddy

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

                  • 6. Re: Execute String without knowing columns
                    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.

                    • 7. Re: Execute String without knowing columns
                      virtualdatabase

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

                      • 8. Re: Execute String without knowing columns
                        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?

                        • 9. Re: Execute String without knowing columns
                          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.

                          • 10. Re: Execute String without knowing columns
                            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.

                            • 11. Re: Execute String without knowing columns
                              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.