Home » SQL & PL/SQL » SQL & PL/SQL » Zero fill an OUT parameter
Zero fill an OUT parameter [message #439180] Thu, 14 January 2010 15:43 Go to next message
tirebiter
Messages: 8
Registered: January 2010
Junior Member
Yes, this again.

I need to zero fill an outbound parameter value in a procedure, and I can't seem to get it to work.

Simply put, I have a procedure with an output parameter called MCN_COUNT of type varchar2. I'd like that to be a string of length 4 containing numeric characters that are zero padded on the left from a numeric variable called COUNTER.

So, I have tried every combination of:

SELECT TO_CHAR(COUNTER, '0009') INTO MCN_COUNT from dual;

It doesn't work.

I've tried to not use "COUNTER" and instead the actual column from the table, e.g.

SELECT TO_CHAR(MCN_COUNTER, '0009') INTO MCN_COUNT from MCN_TBL;

It still doesn't work

Let's say that the current counter is 25. I'd like the procedure to return: '0025'

Any ideas?

TIA


Re: Zero fill an OUT parameter [message #439181 is a reply to message #439180] Thu, 14 January 2010 15:53 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SQL> SELECT TO_CHAR(87,'0999') FROM DUAL;
TO_CH
-----
 0087



But,
SQL> SELECT TO_CHAR(11000,'0999') FROM DUAL;
TO_CH
-----
#####

So, you need to take this point into consideration.


You can NOT use a procedure in a SQL. You need to use Function

Regards,
Ved

[Updated on: Thu, 14 January 2010 15:57]

Report message to a moderator

Re: Zero fill an OUT parameter [message #439182 is a reply to message #439180] Thu, 14 January 2010 15:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It should work that way.

What exactly does "doesn't work" mean? Does it return a pink bunny? Razz

But you could just do

MCN_COUNT := TO_CHAR(COUNTER, '0009');


in the procedure, no need to select from dual.

The format mask '0009' would return ' 0025' by the way, since it leaves an extra space for a possible minus sign. to exclude that space use 'fm0009' as a format mask.

Post EXACTLY what you did, EXACTLY as it happens in SQL*PLus.

Re: Zero fill an OUT parameter [message #439184 is a reply to message #439181] Thu, 14 January 2010 16:04 Go to previous messageGo to next message
tirebiter
Messages: 8
Registered: January 2010
Junior Member
Its_me_ved wrote on Thu, 14 January 2010 15:53
SQL> SELECT TO_CHAR(87,'0009') FROM DUAL;
TO_CH
-----
 0087

You can NOT use a procedure in a SQL. You need to use Function

Regards,
Ved
Yes, I know how to do this in sqlplus or SQL Worksheet.

However, I am using another program that has high-level SQL functionality via ODBC and I want to minimize the amount of traffic through the driver. That's why I want to write as much of the database centric code in a procedure as possible.

I don't have much experience with Oracle overall, and I don't know how a function differs from a procedure, particularly if it is being invoked over ODBC. Does that work?

Is it really not possible to get a procedure to output the numeric value 25 as a character string of '0025'?

I'd even opt for a clunky way such as concatenating a string of zeros in front of the number and taking the rightmost 4 characters. Is that possible? Does anyone have a sample for doing this?


Thanks again.

Re: Zero fill an OUT parameter [message #439185 is a reply to message #439182] Thu, 14 January 2010 16:16 Go to previous messageGo to next message
tirebiter
Messages: 8
Registered: January 2010
Junior Member
ThomasG wrote on Thu, 14 January 2010 15:53
It should work that way.

What exactly does "doesn't work" mean? Does it return a pink bunny? Razz

But you could just do

MCN_COUNT := TO_CHAR(COUNTER, '0009');


in the procedure, no need to select from dual.

The format mask '0009' would return ' 0025' by the way, since it leaves an extra space for a possible minus sign. to exclude that space use 'fm0009' as a format mask.


Post EXACTLY what you did, EXACTLY as it happens in SQL*PLus.



Yeah, sorry for the "doesn't work". I've been programming computers for 35 years and that is an annoying phrase.

"doesn't work" means:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at PFADMIN.SP_NEXT_MCN_COUNTER", line 13
ORA-06512: at line 5

I also tried your suggestion of "MCN_COUNT := TO_CHAR(COUNTER, '0009');" before my first post, and it gets the same error messages.

Thanks for the "FM" suggestion. I'm sure I would have been scratching my head about that one too.

Re: Zero fill an OUT parameter [message #439186 is a reply to message #439184] Thu, 14 January 2010 16:17 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Aha! ODBC is another can of worms entirely.

How are you trying to call the procedure over ODBC?
What kind of driver are you using?

Here is an example of calling/running a procedure from Access VBA with the Microsoft ODBC driver for example.

Depending on the program and driver you use it might even be impossible to call procedures at all.

Re: Zero fill an OUT parameter [message #439187 is a reply to message #439185] Thu, 14 January 2010 16:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at PFADMIN.SP_NEXT_MCN_COUNTER", line 13
ORA-06512: at line 5


Then you have a variable in line 13 of SP_NEXT_MCN_COUNTER where the VARCHAR variable is defined to small.

For example, if you have defined it as VARCHAR2(4) and use the format mask '0009' then that error would happen, since the leading space for the minus sign is added, remember?
Re: Zero fill an OUT parameter [message #439188 is a reply to message #439185] Thu, 14 January 2010 16:25 Go to previous messageGo to next message
tirebiter
Messages: 8
Registered: January 2010
Junior Member
ThomasG wrote on Thu, 14 January 2010 15:53
Post EXACTLY what you did, EXACTLY as it happens in SQL*PLus.



I can get this to work just fine in SQL*Plus, but that's not what I'll be using for this solution. I need to get the zero filled string as output from the procedure.

The program that I'll be calling the procedure from is sort of a 4GL ETL environment. It can do the zero padding, but I was hoping to keep the coding on that side as simple as possible.

Plus, it's becoming a challange now.

Thanks again.
Re: Zero fill an OUT parameter [message #439189 is a reply to message #439187] Thu, 14 January 2010 16:30 Go to previous messageGo to next message
tirebiter
Messages: 8
Registered: January 2010
Junior Member
ThomasG wrote on Thu, 14 January 2010 16:19
Quote:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at PFADMIN.SP_NEXT_MCN_COUNTER", line 13
ORA-06512: at line 5


Then you have a variable in line 13 of SP_NEXT_MCN_COUNTER where the VARCHAR variable is defined to small.

For example, if you have defined it as VARCHAR2(4) and use the format mask '0009' then that error would happen, since the leading space for the minus sign is added, remember?


Boy, am I dumb (one of my favorite Moe Howard lines).

I put the "fm" in the format mask and boom, my wasted afternoon is lost forever.

It is now working with the "MCN_COUNT := to_char(counter, 'fm0009');" version.

Thanks for making me look just as stupid as I know I was being.

Embarassed

Regards,
Re: Zero fill an OUT parameter [message #439191 is a reply to message #439189] Thu, 14 January 2010 16:31 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You know how it is.

Most of the time, the longer you search for a problem the stupider it turns out to be. Laughing
Re: Zero fill an OUT parameter [message #439192 is a reply to message #439184] Thu, 14 January 2010 16:36 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SQL> create or replace procedure t600
( a out varchar2)
as
begin
select to_char(25,'0999') into a from dual;
dbms_output.put_line(a);
end;
/
[code]
[/code]
SQL> variable a varchar2(4);
SQL> exec t600(:A);
BEGIN t600(:A); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error 
ORA-06512: at "HR.T600", line 5 
ORA-06512: at line 1 


Elapsed: 00:00:00.00
SQL> variable a varchar2(5);
SQL> exec t600(:A);
0025                                                                                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> spool off



[Updated on: Thu, 14 January 2010 16:46]

Report message to a moderator

Re: Zero fill an OUT parameter [message #439193 is a reply to message #439186] Thu, 14 January 2010 16:41 Go to previous messageGo to next message
tirebiter
Messages: 8
Registered: January 2010
Junior Member
ThomasG wrote on Thu, 14 January 2010 16:17
Aha! ODBC is another can of worms entirely.

How are you trying to call the procedure over ODBC?
What kind of driver are you using?

Here is an example of calling/running a procedure from Access VBA with the Microsoft ODBC driver for example.

Depending on the program and driver you use it might even be impossible to call procedures at all.

Well, my problem is solved, but I'll reply anyway.

The program I'm using has a very specialized set of SQL functions built in which includes commands to call procedures. I used it with Oracle a few years ago and it worked just fine (as well as with SQLServer, DB2, ASE, MySQL and others that I can't recall offhand). It was even able to return a cursor into a result set from a procedure that I thought was kind of a magic trick at the time.

I'm building a sort of middleware component and the ODBC driver is whatever the client has installed. I think this will eventually be running Oracle 10i.

Thanks to all who replied. The answers were quick and very useful.

Re: Zero fill an OUT parameter [message #439194 is a reply to message #439193] Thu, 14 January 2010 16:48 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
ODBC should not be the problem but the code you are running
Re: Zero fill an OUT parameter [message #439195 is a reply to message #439194] Thu, 14 January 2010 16:51 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
@Its_me_ved

The problem is already SOLVED. You can stop now posting wrong code.
Re: Zero fill an OUT parameter [message #439196 is a reply to message #439192] Thu, 14 January 2010 17:00 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SQL> select length(to_char(25,'0999')) from dual;

LENGTH(TO_CHAR(25,'0999'))                                                                                                          
--------------------------                                                                                                          
                         5                                                                                                          

Elapsed: 00:00:00.04
SQL> select length(to_char(25,'fm0999')) from dual;

LENGTH(TO_CHAR(25,'FM0999'))                                                                                                        
----------------------------                                                                                                        
                           4                                                                                                        

Elapsed: 00:00:00.02
SQL> spool
currently spooling to c:\g.txt
SQL> spool off


So, to avoid numeric or value error:character string buffer too small you need to use

'fm0999' instead of '0999'
Re: Zero fill an OUT parameter [message #439197 is a reply to message #439195] Thu, 14 January 2010 17:02 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
I am not posting wrong code but to show what or where was the error. Got it??
Re: Zero fill an OUT parameter [message #439198 is a reply to message #439180] Thu, 14 January 2010 17:06 Go to previous messageGo to next message
tirebiter
Messages: 8
Registered: January 2010
Junior Member
I just love the interwebs.
Re: Zero fill an OUT parameter [message #439199 is a reply to message #439194] Thu, 14 January 2010 17:11 Go to previous message
tirebiter
Messages: 8
Registered: January 2010
Junior Member
Its_me_ved wrote on Thu, 14 January 2010 16:48
ODBC should not be the problem but the code you are running

I agree completely. My only reason for mentioning ODBC was to point out that I wanted to put the least amount of traffic through it as possible. It is a performance hog and it teaches the hard lesson to use it as close to zero times as can be managed and still get the work done. Hence I make an effort to do as much in procedures as I can.
Previous Topic: Compound bitmap join index on one dimension (merged 2)
Next Topic: procedure inside transaction
Goto Forum:
  


Current Time: Thu Dec 08 03:51:05 CST 2016

Total time taken to generate the page: 0.08930 seconds