Previously, Sturgeon has said the new development should be reassessed but has not gone as far as opposing it outright.
The UK Oil and Gas Authority will make the final decision on whether Cambo will go ahead. The UK government has said an environmental impact assessment will be carried out first.
But lawyers are concerned that the government intends to use an out-of-date impact assessment that fails to take into account the latest climate data.
Sturgeon told MSPs on Tuesday that she had set out a proposal for climate assessment and that “I think the presumption would be that Cambo could not and should not pass any rigorous climate assessment”.
She added: “I don't think Cambo should get the green light. I don't think we can go on extracting oil and gas forever, and I don't think we can continue to give the go ahead to new oil fields.”
Greenpeace welcomed the comments. Sam Chetan Welsh, political advisor to Greenpeace UK, said: “Hopefully this, on top of the many similar comments from scientists, energy experts and leaders around the globe, clarifies the situation for the prime minister. Scotland doesn’t want Cambo, and neither does the rest of the world."
Sturgeon was responding to a question from Labour MSP Monica Lennon in a debate about the outcomes of COP26, who had argued that "if we are serious about averting climate catastrophe, Cambo cannot go ahead".
But the Scottish Conservatives said Sturgeon had "come off the fence and fully abandoned Scotland's oil and gas industry".
MSP Liam Kerr said: "By refusing to back the Cambo development, the SNP have deserted the industry they once cited as the cornerstone of their economic case for independence."
Developers Siccar Point Energy claim that Cambo would produce 50% fewer emissions from the outset than the average UK oilfield and is one of the first to go through the regulator’s stringent Net Zero Stewardship process.
Jonathan Roger, chief executive of Siccar Point Energy, said: "The UK’s production is in a huge decline. Without new fields we will import even more during the transition to new energy sources, which would have a greater carbon footprint. Cambo is important for UK energy security, but represents only 0.68% of new volumes expected to be approved globally this year."