Getting variable back in SQL script when reading from table

[christoph_glania]

Hello,

in an SQL script procedure I want to do a select count(*) to get the number of table records.

The table name (with schema name) shall be a variable.

I tried to options, both failed:

Option 1: Within SQL script:

select count(*) into lv_number from table_name.

Here the problem is the table name. The system responds with "scalar types not allowed":

Option 2: Embedded SQL:

exec 'select count(*) as :lv_number" from " '|| :cachetable_name || ' "' ;

Here the problem is that the SQL-Script variable lv_number' is not filled from the embedded SQL.

Any ideas?

Thanks in advance, Christoph

[former_member184768 · Accepted solution]

Hi Christoph,

If you want the number of records in the table, then why not get it from M_CS_TABLES itself. You have a column RECORD_COUNT which can tell you number of records and you can pass schema name and table name as input the the query. Something like:

SELECT RECORD_COUNT into lv_number from M_CS_TABLES

where schema_name = :cacheschema_name

and table_name = :cachetable_name;

Regards,

Ravi

[Former Member]

Hi Christoph,

I'm not sure what you're trying to do here. Could you explain it? I think we could help better.

But you need to be very careful here - there is no good way to do this without dynamic SQL like YS wrote, and that is both slow, because it is dynamic SQL and also the COUNT(*), which is slow, and open to security problems like SQL injection.

Regards,

John

[yeushengteo]

Hi,

Not sure will this helps?

DROP PROCEDURE Z_GETTABLECOUNT;

CREATE PROCEDURE Z_GETTABLECOUNT (IN I_NAME VARCHAR(100))
LANGUAGE SQLSCRIPT AS
  L_STATEMENT VARCHAR(100);
BEGIN

  L_STATEMENT := 'SELECT COUNT(*) FROM ' ||:I_NAME;
--SELECT L_STATEMENT FROM DUMMY;
  EXECUTE IMMEDIATE L_STATEMENT;

END ;

CALL Z_GETTABLECOUNT('"XYZ"."ABC"');

Regards.

YS