Convert all selected columns to_char

Question

I am using oracle SQL queries in an external Program (Pentaho Data Integration (PDI)). I need to convert all columns to string values before I can proceed with using them.

What i am looking for is something that automatically applies the

select to_date(col1), to_date(col2),..., to_date(colN) from example_table; 

to all columns, so that you might at best wrap this statement:

select * from example_table;

and all columns are automatically converted.

For explanation: I need this because PDI doesn't seem to work fine when getting uncasted DATE columns. Since I have dynamic queries, I do not know if a DATE column exists and simply want to convert all columns to strings.

EDIT

Since the queries vary and since I have a long list of them as an input, I am looking for a more generic method than just manually writing to_char() infront of every column.

Harbour

Use select to_char(col1),to_char(col2)...,to_char(coln) from example_table. – Enosis

That method would be very clumsy for me since my queries and the columns selected change for every query. The queries are automatically read in from a long table. I made an edit to specify this more precisely. – Harbour

Answer 1

If you are looking for a solution in PDI, you need to create a job (.kjb) where in you take 2 transformations. First .ktr will rebuild the query and the Second .ktr will execute the new query.

1. First Transformation: Rebuild the query

• Read the columns in the Source Table Step (use Table Input step in your case). Write the query select * from example_table; and limit the rows to either 0 or 1. The idea here is not to fetch all the rows but to recreate the query.
• Use Meta Structure Step to get the meta-structure of the table. It will fetch you the list of columns coming in from the prev. step.
• In the Modified JavaScript step, use a small snip of code to check if the data type of column is Date and then concat to_Char(column) to the rows.
• Finally Group and Set the variables into a variable.

This is the point where the fields are recreated for you automatically. Now the next step is to execute this field with the new query.

2. Second Transformation: Using this set variable in the next step to get the result. ${NWFIELDNAME} is the variable you have set with the modified column in the above transformation.

Hope this helps :)

I have placed the code for the first ktr in gist here.

Else

Wow nice, I was not expecting to get a solution specific for pentaho kettle. I will have a closer look at your suggestion. Looks good! – Harbour

Answer 2

select TO_CHAR(*) from example_table;

You should not use * in your production code, it is a bad coding practice. You should explicitly mention the column names which you want to fetch.

Also, TO_CHAR(*) makes no sense. How would you convert date to string? You must use proper format model.

In conclusion, it would take a minute or two at max to list down the column names using a good text editor.

Rajput

I have a lot of incoming and varying queries. If I need to look up all column names and write them down to have the single DATE column converted, that would be possible, but not a good option for me. I made an edit to clarify. – Harbour

Answer 3

I can so not immagine an application that does not know about the actual data types but if you really want to automa(gi)cally convert all columns to strings, I see two possibilities in Oracle:

• If your application language allows you to specify the binding type, you simply bind all your output variables to a string variable. The Oracle driver than takes care to convert all types to strings and this is for example possible with jdbc (Java).
• If (as it seems) your application language does not allow the first solution, the best way I could think of, is to define a view for each select you want to use with the appropriate TO_CHAR convertions already and then select from the view. Those views could eventually also be generated automatically from the table repository (user_table) with some PL/SQL.

Please also note, that TO_CHAR will convert your columns acccording to the NLS settings of your session and this might lead to unwanted results, so you might also want to always specify how to convert:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

Uroscopy

Answer 4

using these 2 tables, you could write a procedure with looks at the columns on each table and then performs the appropriate TO_CHAR depending on the current datatype

select * from user_tab_columns

select * from user_tables

psuedo code

begin
  loop on table -- user_tables
    loop on column -- user_tab_columns
      if current data_type = DATE then
        lnewColumn = TO_CHAR(oldColumn...(
      elsif current data_type = NUMBER then
        ...

Handley