Native SQL in ABAP

[Former Member]

I need a query or stored procedure to get data from a table name specified by a variable (here 'TableName').

The stored procedure would be called everytime with a parameter that specifies the tablename to be queried.

The following piece of code works in Oracle, but when I use it in ABAP it gives a 'Table Access Error'.

DECLARE @Query varchar(300), @TableName varchar(30)
SET @TableName = 'Tab1'
SET @Query = 'SELECT * FROM ' + @TableName
EXEC (@Query)

Please help me out.

[Former Member]

Hello Nitya,

Try to do it like this.

<B>EXEC SQL.<B>

YOUR CODE HERE

<B>ENDEXEC. <B>

Try to check the documentation for native sql. You may get an idea!!

Hope this helps, dont forget to reward.

Gabriel

[Former Member]

Yes, I know that the procedure is to be written within EXEC and ENDEXEC.

My question was how do I pass a new value everytime to that procedure i.e. a new tablename is passed to it everytime it is executed.

<b>SET @TableName = 'Tab1'</b>

Instead of specifying a tablename directly , I want to pass a dynamic name to the variable.

[former_member186741]

You can use:

SET @TableName = table_name

where table_name is a vaiable or parameter in the abap.

[seshatalpasai_madala]

Hi,

I am not sure what is your requirment to use Native SQL. This can be done in the open SQL itself that is passing a table name.

DATA: VAR1 type string value 'DEFAULT_TAB'.

SELECT * FROM VAR1 into ITAB.

Regards,

Sesh

[Former Member]

Hello again,

Sorry for the misunderstanding!!

Have you tried to concatenate the table??

I mean to concatenate the values as a string??

I have never done it before, but it could work. Try to write it like a dynamic select:

column_syntax = `car name plate`.

dbtab_syntax = `zcars_names`.

SELECT (column_syntax)

FROM (dbtab_syntax)

INTO CORRESPONDING FIELDS OF TABLE itab.

try to concatenate or assign a value on a string variable.

myvar = 'tab1'

...

SET @TableName = myvar

...

Will it work, i dont know?? Try it!!...

Be careful with the quotation marks...you know how SQL make us crazy with it!!

Gabriel

Message was edited by: Gabriel Fernando Pulido V.

[Former Member]

Ok. My actual requirement is like this. I have a certain number of tables (say 40) in oracle.

I need to write a select query on each table.

Now, I don't want to write 40 select queries.

Instead, I thought of writing a SQL procedure and pass the tablename each time. So that the select query is executed everytime with a new tablename which changes dynamically.

You can assume that the tablenames to be queried are present in an internal table or an oracle table.

I hope this makes my problem statement more clear!

[Former Member]

hI AGAIN!

Try something like this:

data myvar type string.

" ittab-> cant be tehe it with the table names!!

loop at ittab.

myvar = iittab-tabname.

EXEC.

DECLARE @Query varchar(300), @TableName varchar(30)

SET @TableName = myvar

SET @Query = 'SELECT * FROM ' + @TableName

EXEC (@Query)

ENDEXEC.

ENDLOOP.

gabriel

Message was edited by: Gabriel Fernando Pulido V.