PLSQL, VARCHAR2, and concatenating strings!!!

From: <argosy22_at_my-deja.com>
Date: Wed, 04 Aug 1999 15:07:33 GMT
Message-ID: <7o9kvc$g9n$1@nnrp1.deja.com>

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.

• including the INSERT statement: -1401: ORA-01401: inserted value too large for column

ls_account char(11) := '';
- just on Selecting from the cursor,
  and populating the variable, no insert: PL/SQL procedure successfully completed.

• including the INSERT statement: -1401: ORA-01401: inserted value too large for column

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