0

I keep getting this error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 44 ORA-06512: at "SYS.DBMS_SQL", line 1721 Please what am I doing wrong here, because I have checked and it seems the code is fine. I declare a boolean payment_status, and reassigned a value 'tru' to it

DECLARE
   pyld_id                 number(30);  
   p_amt                   number(30);
   p_user_id               number(30);
   p_reference             varchar2(100);
   p_name                  varchar2(100);
   p_narration             varchar2(100);
   p_payment_date          DATE default sysdate;
   p_net_amt               NUMBER default null;
   p_payment_type_id       number(30);
   p_transaction_type_id   number(30);
   p_payment_id            number(30);
   payment_status           boolean ;

    done boolean ;
    p_order number(30);
    p_id number(30);    
    p_parent_id number(30); 

p_order_id number(30);

BEGIN
p_payment_id := 1;
done := TRUE;

p_reference := 'vartesr56';
p_payment_id := 45;
p_user_id := 19411 ;
p_amt := 2000;
p_net_amt := 2000;

p_name := 'Test';
p_narration := 'Product Activation';
p_transaction_type_id := 1;
p_payment_type_id := 1;
payment_status := TRUE;


PSTK_PAYMENT_PACKAGE.add_payment(p_amt, p_user_id, p_reference, p_name, p_narration, p_payment_date, p_net_amt, p_payment_type_id, p_transaction_type_id, p_payment_id, payment_status);

if payment_status != TRUE then
---do somethinh
DBMS_OUTPUT.ENABLE ('NULL');

p_id := 19411;
p_order_id := 3080514;
p_user_id := 19411;
-- get the parent id of the user
DBMS_OUTPUT.PUT_LINE('wants to assign');
select draft_parent_user_id into p_parent_id from users where id = p_id fetch first 1 rows only;

-- set the parent id of the user
update users set PARENT_USER_ID = p_parent_id where id = p_id;    
DBMS_OUTPUT.PUT_LINE('updated');

order_pkg.confirm_order(p_order_id=>p_id,p_user_id=>p_user_id ,p_done => done);

   if done = true then
       ---Do something
       DBMS_OUTPUT.PUT_LINE('NULL');
    else
       -- apex_error.add_error (
        --p_message          => 'Unable to activate package. Please contact admin',
       -- p_display_location => apex_error.c_inline_in_notification );
DBMS_OUTPUT.PUT_LINE('NULL');
    end if;

END IF;

END;
Efam
  • 23
  • 3
  • If you remove all calls to *executable* statements (select, update, package/function/procedure calls), then code executes successfully. I suggest you do the same and uncomment piece by piece of code until you find the culprit. We can't do it, we don't have your tables nor data. – Littlefoot Oct 31 '22 at 09:22
  • Line 44 in what you posted is a blank line. Presumably the error is actually coming from one of the package calls - including the full error stack you get would be helpful. Check the declaration of `add_payment` and `confirm_order`, and make sure you're sending the arguments in the correct order (and/or add [the `arg_name =>` named-parameter](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-subprograms.html#GUID-A5DA8CF5-1BCC-4ABE-9B68-DB593FF1D2CC)) and data types. – Alex Poole Oct 31 '22 at 09:45

0 Answers0