what's the difference between ::= and := in oracle?
I am not sure about ::= as I have not seen that in Oracle but the wiki says about :=
In computer programming languages, the equals sign typically denotes either a boolean operator to test equality of values (e.g. as in Pascal or Eiffel), which is consistent with the symbol's usage in mathematics, or an assignment operator (e.g. as in C-like languages). Languages making the former choice often use a colon-equals (:=) or ≔ to denote their assignment operator. Languages making the latter choice often use a double equals sign (==) to denote their boolean equality operator.
Also check here:
The assignment operator in PL/SQL is a colon plus an equal sign (:=). PL/SQL string literals are delimited by single quotes
The only place (that I'm aware of) where ::= is used is in the syntactical description of PL/SQL (or any other language, for that matter) using Backus-Naur Form (BNF). The ::= symbol is a part of the BNF descriptive language itself, not a part of the language being described. There are many tutorials for BNF -- have fun!