Stored Procedure to Insert Records 1

[MsHart2u]

I am attempting to create a stored proc (my first using Oracle)... It gives me 2 blasted error messages. Please tell what I am missing... I cannot see it. I have counted my columns and I get equal counts on the select and the insert.(arrrgggghhh!!!)

Error Message
1 Line 21 Col 1 PL/SQL: SQL Statement ignored
1 Line 21 Col 13 PL/SQL: ORA-00947: not enough values


CREATE OR REPLACE PROCEDURE sp_createpayrecords
(t_trade_date IN varchar2,
t_settle_date IN varchar2,
t_initials IN char,
t_cusip IN char,
t_trade_number IN char
)

AS
BEGIN
INSERT INTO wt_payfile
(
trade_date ,trade_number ,settle_date,
account_number ,account_name ,rep_id,
executing_rep , cusip ,gross,
id ,description ,prod_code,
net ,eid ,team_name
)
SELECT
t_trade_date ,t_trade_number ,t_settle_date,
acct_no ,name1 ,payto_rep,
payto_rep ,t_cusip ,pay_gross,
t_initials+ CASE WHEN FC_FLAG='Y' THEN 'OLB' ELSE 'NET'
END id,
CASE WHEN FC_FLAG='Y' THEN 'FC' ELSE 'LB'
END + ' W TRADE' description,
CASE WHEN FC_FLAG='Y' THEN '5921' ELSE '5940'
END prod_code,
pay_net ,employee_no ,team_name
FROM wt_commissions;
END;


[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[SantaMufasa]

MsHart,

The "+" operator puzzles me. Either it's a new option in Oracle that I haven't seen before, or it's a mis-use of an operator. If you want to concatenate what is on the left of the "+" to what is on the right of it, then I sugget you use "||" (at least for my peace of mind). Also, I count 15 expressions in your INSERT list, but only 14 expressions in the SELECT statement.

Might one (or both) of these two situations explain your error?

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Also, MsHart, I just noticed that we are "threading" here in the "Oracle 8i" forum...Sorry, but the PL/SQL in Oracle 8i does not support the CASE syntax...period. You will need to rewrite your code using "IF" statements, or upgrade to Oracle 9i.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MsHart2u]

Thank you for your constructive input. I changed my plus(+) to || as you advised. I am a newbie to Oracle, the reference book I inherited is 8i, but the server verison is actually 9.2.0.1 so I will change forums. Your advice worked as does my script now. Thanks a heap, Mufasa.

[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[Beilstwh]

You could have also written it as

CREATE OR REPLACE PROCEDURE sp_createpayrecords
(t_trade_date IN varchar2,
t_settle_date IN varchar2,
t_initials IN char,
t_cusip IN char,
t_trade_number IN char
)

AS
BEGIN
INSERT INTO wt_payfile
(
trade_date ,trade_number ,settle_date,
account_number ,account_name ,rep_id,
executing_rep , cusip ,gross,
id ,description ,prod_code,
net ,eid ,team_name
)
SELECT
t_trade_date ,t_trade_number ,t_settle_date,
acct_no ,name1 ,payto_rep,
payto_rep ,t_cusip ,pay_gross,
t_initials||decode(FC_FLAG,'Y','OLB','NET') id,
decode (FC_FLAG,'Y','FC','LB')||' W TRADE' description,
decode(FC_FLAG,'Y','5921','5940') prod_code,
pay_net ,employee_no ,team_name
FROM wt_commissions;
END;

Bill
Oracle DBA/Developer
New York State, USA