Run sql query from a worksheet

  • Hello.


    I am connecting to an Oracle database from excel. On one of the sheets I store a large and complex query (with comments lines etc, from PL/SQL) that takes about 100 rows (A1:A100)

    Question. How possible assemble this query from a lot of rows?


    This function works, but for a small query of 3-4 rows

    Code
    1. Function GetSqlStr(rBeg As String) As String
    2. Dim x, i&, s$
    3. x = Range(rBeg).CurrentRegion.Value
    4. For i = 1 To UBound(x)
    5. s = s & x(i, 1)
    6. Next i
    7. GetSqlStr = s
    8. End Function
  • I can see anything there that would have an issue with 100 rows. What is happening when you try to use it?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • OraOLEDB -214 - ORA-00928 missing SELECT keyword.

    My query works well in PL/SQL developer. But not works, when placed in excel sheet. (A1:A100)
    In query i use comments and nested queries. Also "union all", regexp etc.. etc...( 90% power of PL/SQL syntax

  • That error suggest a SQL error, not a code error per se, so I'd check that the string returned by the function is actually what you think it should be.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why