All Forums Database
DS 10 posts Joined 07/12
19 Oct 2012
to_char function in stored procedure

Hi All,
The below query works fine when i ran it in sql assistant.
sel cast(to_char(trim(extract( month from <table.date_column>)),'09')||'01' ||to_char(trim(extract( year from <table.date_column>)),'0999') as date )
from table;
when I run the same query with insert as below  in a stored procedure gives error as ' function 'TO_CHAR' does not exist'
insert into table (.............) as
sel (.....,cast(to_char(trim(extract( month from <table.date_column>)),'09')||'01' ||to_char(trim(extract( year from <table.date_column>)),'0999') as date, ........ )
from table;
Please help me resolving it.

Fred 1096 posts Joined 08/04
23 Oct 2012

The to_char function is an ODBC extension and is being translated by the client driver.
Something like this should work:
cast(cast(cast(extract( month from table.date_column) as format '99') as CHAR(2)) ||'01'||cast(cast(extract( year from table.date_column) as format '9999') as CHAR(4)) as date format'mmddyyyy')
So should
cast(cast(cast(table.date_column as format 'mmyyyy') as char(6)) as date format 'mmyyyy')

DS 10 posts Joined 07/12
24 Oct 2012

I managed using trunc as below

sel (.....,trunc(<table.date_column>,'RM'), ........ )

Anyways thanks a lot!

DS 10 posts Joined 07/12
25 Oct 2012

Can any one tell me what should be done to make to_char function work in stored procedure


VandeBergB 182 posts Joined 09/06
25 Oct 2012

It looks like your system already has the Oracle UDF's installed, as it worked in SQL Assitant.
There may be some permission issues, or you may need to fully qualify the function call, check with your local handy dandy DBA.

Some drink from the fountain of knowledge, others just gargle.

Fred 1096 posts Joined 08/04
25 Oct 2012

TRIM returns VARCHAR. Even if you have TO_CHAR UDF (e.g. TD14), there is no specific function prototype for TO_CHAR that accepts VARCHAR arguments. So remove TRIM and pass the numeric value returned by EXTRACT.
Again, it is working in SQL Assistant only because the SQL you typed is not the SQL that is being sent to the database by the ODBC driver. Try it in BTEQ or in SQL Assistant with .NET provider and it will fail just as it does in the SP.

DS 10 posts Joined 07/12
26 Oct 2012

I tried by qualifing with TD_SYSFNLIB.
Doesn't worked.

You must sign in to leave a comment.