Oracle Invalid Number Error When Inserting

Question

I am trying to run insert-select query. But whatever I do, I get "Invalid Number" error.

My query is like this;

INSERT INTO banka_odeme (banka_id, banka_ad, banka_tutar, aciklama, banka_ismi)
SELECT 
    (SELECT MAX(banka_id) + 1 FROM banka_odeme),
    w.first_name || ' ' || w.last_name,
    TO_NUMBER(w.total),
    TO_NUMBER(w.id),
    'SiPay'
FROM 
    woo_orders w 
WHERE 
    NOT EXISTS (SELECT 1 FROM banka_odeme WHERE banka_odeme.aciklama = TO_NUMBER(w.id));

My table structure is like this; (Please check the image links below)

Banka_Odeme Table

Woo_Orders Table

Thanks in advance to anyone who tries to help :))

I've tried every possible query combinations and it still gives the same error. :(

Answer 1

• w.total and w.id are already numbers - don't use TO_NUMBER on them.
• Don't use (SELECT MAX(banka_id) + 1 FROM banka_odeme) to generate the next number in a sequence, use a proper SEQUENCE.
• banka_odeme.aciklama is a string and w.id is a number. When you compare them either convert the string to a number TO_NUMBER(banka_odeme.aciklama) = w.id(but if it's always convertible to a numeric then why are you storing it as a string) or convert the number to a string banka_odeme.aciklama = TO_CHAR(w.id).
• If you want to merge one table into another then you can use a MERGE statement.

So:

DECLARE
  v_sql VARCHAR2(200);
  v_id  BANKA_ODEME.BANKA_ID%TYPE;
BEGIN
  SELECT MAX(banka_id) + 1
  INTO   v_id
  FROM   banka_odeme;

  v_sql := 'CREATE SEQUENCE banka_odeme__banka_id__seq START WITH ' || v_id;
  EXECUTE IMMEDIATE v_sql;
END;
/

Then:

MERGE INTO banka_odeme b
USING woo_orders w
ON (b.aciklama = TO_CHAR(w.id))
WHEN NOT MATCHED THEN
  INSERT (
    banka_id,
    banka_ad,
    banka_tutar,
    aciklama,
    banka_ismi
  ) VALUES (
    banka_odeme__banka_id__seq.NEXTVAL,
    w.first_name || ' ' || w.last_name,
    w.total,
    TO_CHAR(w.id),
    'SiPay'
  );

fiddle