A Data Engineer wants to check the status of a pipe named my_pipe. The pipe is inside a database named test and a schema named Extract (case-sensitive). Which query will provide the status of the…

Question

Options

• ASELECT SYSTEM$PIPE_STATUS("test.'extract'.my_pipe");
• BSELECT SYSTEM$PIPE_STATUS('test."Extract".my_pipe');
• CSELECT * FROM SYSTEM$PIPE_STATUS('test."Extract".my_pipe');
• DSELECT * FROM SYSTEM$PIPE_STATUS("test.'extract'.my_pipe");

• B
  85% (17)
• C
  5% (1)
• D
  10% (2)

Explanation

Option B is correct because SYSTEM$PIPE_STATUS is a scalar function, so it's invoked with SELECT ... function() - not SELECT * FROM. The pipe path is passed as a single-quoted string literal, and because the schema name Extract is case-sensitive, it must be wrapped in double quotes inside that string: 'test."Extract".my_pipe'.

• A is wrong on two counts: it wraps the path in double quotes (making it an identifier, not a string), and uses lowercase extract, which breaks the case-sensitive match for Extract.
• C is wrong because SELECT * FROM implies a table function - SYSTEM$PIPE_STATUS returns a scalar VARIANT value, not a result set you can query with *.
• D combines both errors from A and C: the SELECT * FROM syntax and the incorrect double-quote/lowercase wrapping.

Memory tip: Think of it as "single outside, double inside" - the entire path is a single-quoted string, and case-sensitive object names get double-quoted within it. And remember: if it returns one value, SELECT function() - no *, no FROM.

No community discussion yet for this question.