Oracle SQL Pseudo-Columns Explained: Types, Uses, and Key Limitations

·1 views

Oracle SQL pseudo-columns behave like table columns but are not physically stored on disk, meaning they can be queried with SELECT but cannot be modified via INSERT, UPDATE, or DELETE. Built-in pseudo-columns include ROWID for unique physical row addressing, ROWNUM for sequential row numbering, NEXTVAL and CURRVAL for sequence management, and ORA_ROWSCN for tracking data changes via System Change Numbers. For hierarchical queries using CONNECT BY, Oracle provides additional pseudo-columns such as LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE, and SYS_CONNECT_BY_PATH to navigate and inspect tree structures. A common pitfall involves ROWNUM, which is assigned after the WHERE clause but before ORDER BY, making direct filters like ROWNUM > 1 unreliable without using a subquery. Unlike ROWNUM, ROWID is a permanent physical address that persists across transactions, and while ROWID cannot be indexed directly, ROWNUM cannot be indexed at all; custom pseudo-columns are not supported, though Oracle 11g introduced virtual columns as a logical alternative.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

Cisco Talos Uncovers Malware That Uses Four AI Models to Decide Its Next Attack

Cisco Talos disclosed CLOSEDQUORUM in September 2026, identifying it as the first publicly documented Windows malware that delegates tactical decision-making to artificial intelligence rather than a human operator. The Go-based implant polls four large language models — DeepSeek, Qwen, Mistral, and Google Gemini — which independently vote on the next action from a fixed menu including credential theft, code injection, persistence, and lateral movement. Whichever action receives the most votes is executed, with a preset tiebreaker hierarchy resolving any deadlock. Stolen data is encrypted and exfiltrated via a Discord webhook to a channel controlled by the attacker, though a human must still build, configure, and deploy the malware. Security researchers consider this a significant architectural shift because the most human-dependent phase of an intrusion — real-time tactical decision-making — has now been handed to AI, removing constraints tied to operator attention and working hours.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

New Developer Joins DEV Community to Explore Web, AI, and SEO Topics

A new member has introduced themselves to the DEV Community, an online platform for software developers. The individual expressed interest in learning and exchanging ideas within the community. Their areas of focus include web development, automation, artificial intelligence, and SEO. They also highlighted a goal of connecting with like-minded professionals in these fields.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Silent broker failure left 214 paid orders unshipped and undetected all weekend

A finance reconciliation at an unnamed company revealed 214 paid orders from the previous weekend had never been dispatched, with four customers already calling to complain. All system dashboards showed green because a years-old code change had silently swallowed the error — wrapping the messaging call in a try-catch that logged failures only as warnings. The root cause was a 12-minute broker outage during Saturday maintenance, which caused event publishing to fail after order rows and payments had already been committed to the database. The fix involved implementing the outbox pattern, writing events to a dedicated table within the same database transaction as the order, with a relay service handling retries and consumers deduplicating on event ID. An alert on the age of the oldest unsent outbox row has since caught three similar issues before they could silently accumulate into customer-facing problems.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

How a Single Loop Triggered 400 Database Queries and Slowed Pages to 9 Seconds

A common but hard-to-spot backend bug known as the N+1 query problem can cause a single loop to generate hundreds of individual database round trips. In the case described, iterating over 400 orders — each triggering its own hidden query — stretched a list page load time to nine seconds. The problem is difficult to catch because the offending code looks like a simple field access during review, testing, and standard metrics monitoring. The fix involves pulling all required data before the loop runs, rather than fetching it one record at a time. Developers are advised to count queries — not just measure milliseconds — and test against real data volumes to surface this class of bug early.

0 comments Read more at DEV Community