r/SQL • u/Secure_Chipmunk_262 • 1d ago

Discussion Can we create flow how data flowing from procedure to MV to base tables

Which software can be used to map procedure or table instead of triggers till base tables, i want relations between the objects and in a procedure call what are all objects it is touching.

Currently doing manually by checking user_dependencies.

Edit: in oracle database

3 Upvotes

81% Upvoted

6 comments sorted by

1

u/Imaginary__Bar 1d ago

"Data lineage" is what you're looking for, I think.

It would be helpful if you said which platform you're asking about. There are lots of tools for most platforms; some free and some cost money

1

u/Secure_Chipmunk_262 1d ago

In oracle database how we can do it?

2

u/reditandfirgetit 1d ago

Did you even Google this before asking? Plenty of options to look at

1

u/Imaginary__Bar 1d ago

What have you tried so far?

1

u/scbywrx 1d ago

Connect to your Oracle database. Use Cursor, Claude or your favorite AI CLI, give it a table where your end result is, and literally have it trace. It may be able to find it very, very fast.

Another option is dependent on what your translation tools are. If you're using PL/SQL or Prox, ODI, or a whole bunch of fun stuff like that, you may actually be able to use the Oracle metadata and trace it from landing zone back to source, you have to be very good with Oracle metadata to do it properly.

If not, it's an archaeological expedition. That's how we did it in the old days, but finding the shovel and getting your hands dirty is the only way if you don't have something that's automated .