Skip to Content

Table Function: Select statement with tablename in variable - Error: scalar type is not allowed

I have two SQL statements which are almost identical. The only difference is the table to be queried.

So I thought about using a Table Function (Table User Defined Function), (Table UDF) and passing the table name as a paramter:

FUNCTION "SCHEMA"."package::Function" ( in tbl varchar(200) )
Select col1, length(col1), left(col1, 6) ...
from :tbl

Unfortunately I get an error during activation:

Could not create catalog object: scalar type is not allowed; line 18 col 7

The same activation error (scalar type not allowed) occurs in a stored procedure.

So what exactly is the problem - and how to fix it??

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Nov 23, 2017 at 06:20 AM

    This is a common requirement - you want to write the transformation description of the data once and apply it to different data sets with the same structure.

    One way to do this is via dynamic SQL which comes with the well-known disadvantages.

    Another is to use table functions and SQLScript to hand over the data you like to process.


    create column table even_users 
        as (select user_name, user_id from users
            where mod(user_id,2) =0);
    create column table odd_users 
        as (select user_name, user_id from users
            where mod(user_id,2) =1);        
    -- now we have two distinct data sets with the same structure
    create function getUsersUpperCase (
                    in_users table (user_name nvarchar(256)
                                  , user_id bigint)) 
    returns table (user_name nvarchar(256)
                  , user_id bigint)
         return select upper(user_name) as user_name,
                 from :in_users;   
    -- the function performs a transformation (simple for this example) and returns a result of the same structure 
    -- to use the function, we need a SQLScript context in order to bind the table type input parameter
    do begin
      u =  select * from odd_users;
             getUSersUpperCase (in_users => :u);        

    Here I've separated the transformation declaration completely from the source tables without using any dynamic SQL.

    Add a comment
    10|10000 characters needed characters exceeded

    • From my point of view this is just consistent with the general concepts of SQL programming. It’s a declarative, static language. Tables are not just data structures that have certain characteristics, but they identify the value set of tuples stored in them.

      So, in my example odd_users and even_users might be seen as “user” tables from the view point of a generalisation modelling, that is not a concept available in SQL. Here, both tables contain different value sets.

      If you describe a result set (which is what you do when you write a SELECT) then this is always in the context of a specific table.

      Dynamic SQL is the DBMS industries’ response to requirements like yours: to perform a kind of half-meta programming in SQL. With dynamic SQL one doesn’t describe a result set anymore, but how to construct such a description.
      While it is a very powerful mechanism it does come with drawback comparable with the lack of static type checking in JavaScript compared to TypeScript.

      My personal view here is that the answer could simply be to have the application, that runs those similar but different statements, construct the statements as required instead of trying to force the dynamism into the DB layer.
      I like code that is easy to understand - generating SQL in the application layer is not hard to understand and easy to debug as it produces static SQL.
      On the other hand, dynamic SQL is not as easy to debug and understand and typically the resulting code obscures what the itention of the program are.
      When you look at the example I gave again, it loosely follows the idea of the strategy pattern by isolating the intended computation. It’s very obvious what happens here. That’s why I like it.

      I’m not even sure that it requires really that much more code in total, but it definitively requires more artefacts and smaller units of “computation-logic”, which again, is a benefit in my books.

      To finish this comment, I have the feeling that frameworks like “Hibernate” are aiming to bridge exactly this conceptual gap, where the procedural/oo-minded developer tries to super-impose his/her models onto a SQL database. Having this “interpreter”-layer (just “translating” is not sufficient here - it’s not a dictionary lookup exercise, but a mapping of concepts) is a necessary component in the application stack and probably too often overlooked.

      Having said that, I also think that it doesn’t hurt to have a few modelling/programming pattern in SQL up your sleeve that allow for flexibility and abstraction without resorting to dynamic SQL.

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2017 at 03:21 PM

    Hi Thorsten,

    Maybe you need dynamic SQL for this scenario.

    Here an example:

                                          IN SCHEMA_NAME VARCHAR(100) ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
      DECLARE T VARCHAR(5000); 
      SELECT   'SELECT * FROM  "'||:SCHEMA_NAME ||'"."'||:TABLE_NAME||'"' 
      INTO     T 
      FROM    DUMMY;
    call PROCEDURE_TEST('yourTable','yourSchema')

    Best regards


    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.