CODEWITHSUNDEEP
oracle-databaseplsql
wg102
wg102

Reputation: 3

How to verify if column is nullable in PLSQL

I am working on Oracle SQL Developer, and am relatively new to oracle. I created a package with an "insert into X... select..." Type of query. The code is as follow:

Insert into my_table t1 (t1.a, t1.b, t1.c) Select distinct t2.a, t2.b, t2.c from my_source t2 where ...

Note: the my_table t1 and my_source both have more columns than a, b, and c, but in theory, or at least, in dev environment, they are nullable. I am ONLY interested in those columns for now, but the error is on column "d"

It works in the dev environment no problem, but when run in production the following message happens "ORA-01400: cannot insert NULL into ("Schema_name"."table_name"."table_colum_name")".

My theory is that somehow the table column is nullable in the dev environment and not in the production environment.

My issue is that I cannot access directly the production environment tables to verify this, therefore I need to do one of those:

  1. Find a way to query the table in prod (most likely using database links) and verify is said column is nullable) OR
  2. Add a condition for my insert. But I insert column 1-3 and it says column 4 cannot insert null. Therefore I do not think this is doable.

I tried a few queries to check:

Thank you for any help

Upvotes: 0

Views: 3134

Answers (2)

Justin Cave
Justin Cave

Reputation: 231781

You probably want something like

select nullable
  from all_tab_columns
 where owner = '<<schema owner>>'
   and table_name = '<<table name>>'
   and column_name = '<<column name>>'

Note that

  • In 99% of cases, owner, table_name, and column_name should all be upper case in the data dictionary. You can have case sensitive identifiers in Oracle, it is just rare.
  • In Oracle, there is a pattern for data dictionary tables that there are user_, all_ and dba_ variants. So there is a user_tab_columns that shows information about all the columns of tables you own, a dba_tab_columns that shows information about all the columns of all the tables in the database, and an all_tab_columns that shows information about all the columns of all the tables that you have access to. Depending on the permissions you have and the owner of the table, you may be able to use the dba_ or user_ version of the data dictionary table instead of all_tab_columns.

Upvotes: 0

Dan Breidegam
Dan Breidegam

Reputation: 56

Could you run your select statement only against production? Just to confirm that it doesn't have a null value where you didn't see the same output in development. Secondly, is your development environment a copy/backup of the production environment? If nothing else, you should be able to request a backup be restored to development by the DBA who manages your production instance.

Upvotes: 0

Related Questions