The Campbeltown colloquial word "hoora" originates from the expression "whore of a", which is a generic intensifier — "a whore of a fall", "a whore of a goal", etc. Due to the inclusion of the mildly obscene "whore", the expression itself was considered mildly obscene, but its shortened form "hoora" has lost all suggestion of "whore" (and therefore all opportunity for offence) amongst its younger users, and is rapidly becoming a staple of the dialect.
As this is a highly localised word, not present in standard English or widely-recognisable Scots, there is no universally accepted spelling, with variants such as "hura" or "huura" common. Generally, the more vowels included, the more intense the meaning (in both spoken and written communication).
"Hoora" is often used as equivalent to "very", but is flexible and depending on context can replace most any adjective the speaker wants it to. In this way hoora is related to words such as "hella" and "wicked" in the USA. It can also be used as a noun to signify a large number.
Did ye see Black Swan? Hoora queer, a had no idea hoot wis heppnin! There were a hoora weans up the front as well so a couldnae even concentrate...