CODEWITHSUNDEEP
oracle-databaseplsql
Craig
Craig

Reputation: 11

VARCHAR Leading 0's are lost when executing PLSQL procedures

I have a PL/SQL script which executes a number of procedures on an oracle DB. The script defines:

DECLARE
    productkey VARCHAR2(100);

BEGIN
  productKey := '000000000070307037';

  ...

  ProcedureName(productKey);

The procedure expects a VARCHAR2

PROCEDURE     ProcedureName (
  productKey VARCHAR2
  )

The procedure inserts into a table:

BEGIN
    Insert into Mytable
      (
        THIS_PRODUCT_KEY
      )
    Values
      (productKey);

When I query that table, the product Key = 70307037, ie the leading 0's have been lost.

I saw some similar questions where TO_CHAR was suggested, I tried defining productKey in the script using TO_CHAR, and also modifiying the procedure to write using TO_CHAR:

BEGIN
    Insert into Mytable
      (
        THIS_PRODUCT_KEY
      )
    Values
      (TO_CHAR(productKey,'000000000000000000'));

Still coming through without the leading 0's.

There are multiple queries that join using the product key and don't work when the 0's are missing.

Why would I lose the 0's when the variable is a VARCHAR ?

Upvotes: 0

Views: 84

Answers (1)

Ali Yesilli
Ali Yesilli

Reputation: 2200

I believe that "THIS_PRODUCT_KEY" column in your "Mytable" table is not a varchar2 column. I think it is number. If you change the datatype of the "THIS_PRODUCT_KEY" column to varchar2, it won't lose the 0s.

Upvotes: 2

Related Questions