Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> PLSQL, VARCHAR2, and concatenating strings!!!
Hello,
I am trying to concatenate two
numeric fields, NUMBER(6), AND number(4)
into one char, or varchar2 variable,
to insert into a varchar2(10) field in
another table.
The tables are:
SQL> DESCRIBE LAWSON.GLAMOUNTS
Name Null? Type ------------------------------- -------- ---- COMPANY NOT NULL NUMBER(4) FISCAL_YEAR NOT NULL NUMBER(4) ACCT_UNIT NOT NULL CHAR(15) ACCOUNT NOT NULL NUMBER(6) SUB_ACCOUNT NOT NULL NUMBER(4)....
SQL> DESCRIBE CATEGORY
Name Null? Type ------------------------------- -------- ---- COST_CTR NOT NULL VARCHAR2(15) ACCOUNT NOT NULL VARCHAR2(10) RECTYPE NOT NULL VARCHAR2(1)...
GLAMOUNTS.ACCOUNT and SUBACCOUNT are to be concatenated into CATEGORY.ACCOUNT.
In my PLSQL procedure:
The variable definiations I have tried at different times are:
ls_account varchar2(10) := ''; ls_account varchar2(11) := ''; ls_account char(10) := ''; ls_account char(11) := '';
The cursor is:
cursor pop_key is select A.ACCT_UNIT cost_ctr, A.ACCOUNT, a.sub_account FROM GLAMOUNTS A WHERE ...
Using the cursor:
FOR c2 in pop_key loop
Begin
ls_account := (rtrim(TO_CHAR(c2.ACCOUNT, '000000')) ||
ltrim(to_char(c2.sub_account, '0000')));
/* rtrim, and ltrim are required, because otherwise,
*/
/* there will be blanks between ACCOUNT, and SUB_ACCOUNT,
*/
/* where there should be none
Insert into CATEGORY ( COST_CTR, ACCOUNT, RECTYPE, ... ) VALUES ( C2.COST_CTR, ls_account, 'A', ... ); Exception When Dup_val_on_index then Null; When others then dbms_output.put('Other error: ' || SQLCODE || ': '); dbms_output.put_line( SQLERRM ); End; End loop; /* END pop_tmp_histkey1 */
The errors using the different variables:
ls_account varchar2(11) := '';
- just on Selecting from the cursor,
and populating the variable, no insert:
PL/SQL procedure successfully completed.
ls_account char(11) := '';
- just on Selecting from the cursor,
and populating the variable, no insert:
PL/SQL procedure successfully completed.
ls_account varchar2(10) := '';
- just on Selecting from the cursor,
and populating the variable, no insert:
-6502: ORA-06502: PL/SQL: numeric or value error
ls_account char(10) := '';
- just on Selecting from the cursor,
and populating the variable, no insert:
-6502: ORA-06502: PL/SQL: numeric or value error
So, if I can get it into the variable,
I can't get it into the table.
How can I insert the information?
Why are there so many problems with the
concatenation of strings?
Thanks in advance for all your help,
Argosy
P.S. I use the alias at dejanews as an anti-spam device. It works rather well.
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Wed Aug 04 1999 - 10:07:33 CDT
![]() |
![]() |