ODBC stage - Column name with pound sign (#) not accepted

mreddick · Post by **mreddick** » Fri Jan 20, 2006 8:47 am

We're new with DataStage so please bear with me.

I'm setting up a server job and trying to write to an Oracle table using the ODBC stage. Everything was going good until I tried mapping (via Transformer stage) a value to an Oracle column containing a pound sign (PCLIN#E). When I loaded the table meta data from the Oracle table itself, everything loaded fine including this column name. But, when I went to compile it, I am getting an error due to having this pound sign in the column name. So, I tried removing the # and it compiles fine but obviously errors when it runs since that column doesn't exist. I then tried adding the # back in manually, and DataStage tells me that a column name cannot contain a #.

I did some searching on these forums and found one similar post but it was dealing with a table name with a pound sign. There were a few suggested work arounds (prefix with \ to escape it and putting quotes around it) so I tried them but that didn't work for me with the column name.

This table is a production table and I cannot change the column name.

Is there a solution for this?

- Mark

kcbland · Post by **kcbland** » Fri Jan 20, 2006 9:53 am

Sorry, but the only option that I know works is to have a view created on the production server that aliases the column under a new name.

mreddick · Post by **mreddick** » Fri Jan 20, 2006 11:11 am

I was afraid of that.

Thanks for the response.

- Mark

ray.wurlod · Post by **ray.wurlod** » Fri Jan 20, 2006 7:23 pm

There is something in one of the manuals about these kinds of characters - I've read it quite recently. (As is usual on Saturdays I'm logged in from an airport lounge and can't check the details.) It may have been for DB2 stage. It may have been for parallel jobs. Open the Server Job Devloper's Guide anyway and do a search for "#".

chulett · Post by **chulett** » Fri Jan 20, 2006 8:52 pm

One such place would be the Oracle OCI Stage Guide. You've got to enable DS_ENABLE_RESERVED_CHAR_CONVERT and then # is represented by _035_ and $ by _036_ in the metadata. They suggest you do this and then import the metadata so that DataStage can handle the conversion for you.

Don't know if any such functionality exists for ODBC, however.

kumar_s · Post by **kumar_s** » Sat Jan 21, 2006 3:49 am

Hi Mark,

Wont the suggestion given by kenneth would the best.
A view can be created as a one time process, and can be used.

-Kumar

chulett · Post by **chulett** » Sat Jan 21, 2006 8:24 am

'Best' is a relative term.

It would certainly work and Mark has probably already gone and done exactly that. Just trying to discover some alternatives.

Kumar - as an aside and just to be a little anal, you seem to be awfully fond of the little 'rolling eyes' icon. Not sure if you understand the connotations of it. One rolls ones eyes to express exasperation or frustration, typically in response after someone else has said something exceedingly stupid. I doubt you mean it like that.

Of course now, the next response might very well be another visit from Mr Rolly Eyes and a message about "sticking it" and a place where "the sun don't shine".

loveojha2 · Post by **loveojha2** » Sun Jan 22, 2006 9:24 pm

In such cases
We provide the column name in the derivation and use a different name for the datastage column name.

Like

Column Name | Derivation
PCLINE | PCLIN#E

kumar_s · Post by **kumar_s** » Sun Jan 22, 2006 9:46 pm

Hi,
Is the rolloign eys really ment that

(This is for my own response

)
I was under the impression that this emoticons give the unsure, indecisive, hesitant suggestion.

Alright, let me try to post all my emoticons with smily.(Hopefully suits the circumstances)

-Kumar

mreddick · Post by **mreddick** » Mon Jan 23, 2006 6:50 am

Thanks everyone for all the suggestions!

We haven't tried implementing any change yet. I'll have to run some of these ideas past the other team members to see how we want to proceed.

ray.wurlod
I checked the Server Job Developer's Guide but didn't find anything about # as a column name.

chulett
This sounds like the best solution to me. The person we have in that is are "DataStage expert" is supposed to do something to get this to work for us. I'm not sure what he needs to do. So, for the time being, we're using ODBC.

loveojha2
I thought about this right away but from what I can tell at least, there is no such "derivation" attribute when writing to a table, just reading. If I'm wrong, please let me know.

Oh, and does anybody know if you can actually write to a view? I'm no DBA and haven't worked too much with Oracle, but I was under the belief that views were read-only.

Again, thanks for all the ideas everyone!

- Mark

kumar_s · Post by **kumar_s** » Mon Jan 23, 2006 6:59 am

HI Mark,

A simple syntax for creation of View would be

Code: Select all

CREAT VIEW <View_Name> AS SELECT <list of columns> FROM <table_name>

If you want to create a view for the full table, then the select command use

Code: Select all

....SELECT * FROM <table_name>

And now the New view name can be used very similar to the existing table.
Yes there are some constrainsts in using view.

-Kumar

loveojha2 · Post by **loveojha2** » Mon Jan 23, 2006 9:47 pm

I did an experiment with User Defined SQL and it worked. (I tested it on SQL Server).

My target table is like this
create table Abc (a# int primary key,b# int,c# int)

The Column names I used on the target link were a,b,c

The User defined SQL I used
INSERT INTO Northwind.dbo.Abc (a#,b#,c#) VALUES (?,?,?)

And It is working very fine, try it with Oracle and let us know the result.

peterbaun · Post by **peterbaun** » Tue Jan 24, 2006 5:05 am

Hi -

Had a similar problem once where special Danish characters were used in the field name.

Another way of solving this issue is by using custom sql the following way.

Import all the field names into the source stage.
Change the name of the fields that have the special characters to something appropriate.
Use custom sql.
In the custom sql you can write " select * from TableName"

This solution obviously requires that the table definition isn't changed and that the order of the fields are correctly specified.

Regards
Peter

peterbaun · Post by **peterbaun** » Tue Jan 24, 2006 5:36 am

Hello again,

Sorry - I will polish my glasses. You asked about writing to the DB I answered reading from the DB.

Regards
Peter

mreddick · Post by **mreddick** » Tue Jan 24, 2006 6:36 am

loveojha2 wrote:I did an experiment with User Defined SQL and it worked. (I tested it on SQL Server).

My target table is like this
create table Abc (a# int primary key,b# int,c# int)

The Column names I used on the target link were a,b,c

The User defined SQL I used
INSERT INTO Northwind.dbo.Abc (a#,b#,c#) VALUES (?,?,?)

And It is working very fine, try it with Oracle and let us know the result.

How do you tell it you want to use user defined SQL? When I connect an input to the ODBC stage, it isn't giving me the option to select "user defined SQL" as it does on an Output.