Using a parameter value as a column name

[Former Member]

Hi,

I have created an command like this:

update table1 set [Param.1] = '[Param.2]' where id = [Param.3]

where:

Param.1 = COL1

Param.2 = Hello

Param.3 = 1 (the primary key I want to change).

When I run this SQL Action I get the following error message:

java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

I have double checked the values of my parameters and they are all correct. There is a column named COL1, and its type is varchar2, and there is PK = 1.

So, my question is: Is it a limitation of xMII (we can not take column names from parameters) or there is a mistake here?

Thank you in advance,

Nuno Cunha

[sidnooradarsh]

Hi Nuno,

I see you are trying to perform Dynamic SQL execution in Oracle.

In order to execute a Dynamic SQL you need to use the following syntax

EXECUTE IMMEDIATE 'Sql Statement with any runtime params like cloumns, even table names too';

And I guess you can use an Dynamic Sql only within a PL/SQL block.

Please refer to the below link for more details

[Dynamic SQL|http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm]

Hope this helps!!

Regards,

Adarsh

[Former Member]

Hi,

Not a dynamic query in Oracle, but in MII. I was thinking it was only a simple replacement in MII... so I can not see why it don't work.

Thank you,

Nuno

[sidnooradarsh]

Hi Nuno,

Don't use quotes to the column names when you are passing them as variable in MII

update emp_list 
set
[Param.1] = '[Param.2]'
where
emp_code = 1

This should work.

Regards,

Adarsh

[Former Member]

Hi,

I did not.

Nuno

[Former Member]

Nuno,

did you checked the NW Logs as I describe above?

BR

Pedro Iglesias

Edited by: Pedro Iglesias on Dec 24, 2009 3:30 PM

[Former Member]

Hi Pedro,

I was out (Christmas vacation) since 18 dec. Today I will take a look on it.

Thank you,

Nuno Cunha

[Former Member]

Hi all,

Last week I rewrote it from scratch and now it works!

Thank you for you time,

Nuno Cunha

[Former Member]

Does the query work if you hard code it? I wonder if COL1 is an illegal name somehow. Also with oracle you may need to specify the schema ( e.g. <schema_name>.<table_name> )

Edited by: Christian Libich on Dec 15, 2009 7:36 PM

Edited by: Christian Libich on Dec 15, 2009 7:38 PM

[Former Member]

Hi,

If I change the parameter by the name of the column everything works fine.

As I need 9 sql queries that are very similar with each but the name of the column, so I was trying making the column a parameter itself...

Nuno

[jcgood25]

Did you try putting quotes or square brackets around COL1 - this might be a test to see if it is some sort of reserved keyword.

Have you tried the statement inside a native Oracle query editor to see if it give you any more insight than the typical Oracle ORA-xxxx error?

[Former Member]

Nuno,

if you still have problems with your column name, you may also build a string variable that conatins your complete query and link it to the Query parameter of the query.

Michael

[Former Member]

Hi Jeremy,

The name of the column in the example given here is COL1 but at my real work is another one (DATA_SAIDA_LOCAL_ATUAL), in portuguese (some like the "Departure date from the current location").

I have ran this command (without using parameters) in a oracle query editor and it is okay. All others parameters in this MII query that are values to put in others columns works with no problem. Just the one that I would like to use as a column name do not work.

Nuno

[Former Member]

Hi Michel,

I am curious about what is wrong here because I thought that the MII would simply replace the value of the parameter in the sql update string... then there should be no difference between I have written directly the column name or it has been a replacement...

Thank you

[Former Member]

Nuno,

did you check the Netweaver Logs (Last24Hours)? Normally you will find the error with the complete Query that

failed. Copy this Query and paste in the MII SQL Query Template.

Cheers

Pedro

[Former Member]

Hi Pedro,

No, I did not. Where I can find this log?

Thank you,

Nuno

[Former Member]

Nuno,

go to the Netweaver Administration

http://<server>:<port>/nwa

then Monitoring -> Log & Traces -> and select the Predefined View Last 24 Hours.

BR

Pedro

Edited by: Pedro Iglesias on Dec 16, 2009 4:04 PM