PLSQL Basics – Outputting Multiple Columns and Rows

oracleplsql

I'm new to PLSQL so I have a very basic question that I hope somebody will help me with. I would like to run a straight forward select statement against an 11g DB and then a slightly modified version of that SQL statement if it's a 12g database. The results should get outputted to the screen. The code below works until I add a SQL statement. I tried 'execute immediate' but that didn't work and then using a cursor – but for multiple columns and multiple rows that can't be the answer as it's so long winded. What am I missing here?
The statements could be anything, like for example:

--11g
select NAME,OPEN_MODE,DBID from v$database; 
--12c
select NAME,OPEN_MODE,DBID,con_id from v$database;

I'd just like to know how to approach handling any SQL select statement (that returns multiple rows and cols) in such a code block.
Thanks.

DECLARE
    v_dbver    BINARY_INTEGER;
BEGIN
    SELECT TO_NUMBER(SUBSTR(version,1,2)) INTO v_dbver FROM v$instance;
    DBMS_OUTPUT.PUT_LINE('Version ='||v_dbver);
IF v_dbver < 12 THEN
    DBMS_OUTPUT.PUT_LINE('Running against 11g');
    <SQL statement>
ELSIF v_dbver >= 12 THEN
    DBMS_OUTPUT.PUT_LINE('Running against 12c');
    <SQL statement>
END IF;
END;
/

Best Answer

Edit: Please see the additional notes at the bottom for how this can likely be done by using PIPELINE functions.

PL/SQL in ORACLE does not return Datasets in the same way that other RDBMS do (like SQL Server, where you can just pop a random SELECT statement anywhere in a block or procedure and it will output the results to the grid).

Oracle will only output to the screen as a standard SELECT query or via a refcursor.

What you're doing is known as an anonymous block. Anonymous Blocks do not display the results of a SELECT statement. They exist to "get work done." I wrote a blog post that explains this a bit, you're welcome to take a look: https://sqldevdba.com/f/t-sql-vs-plsql-series-part-2-select-into

For your issue: You have a few options, including:

  1. Output the Select statement using a RefCursor. (https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets)

2. Write the results to a table (Using Insert INTO ... SELECT FROM), then Select from that table when done. (This is my preferred approach when running scheduled items like reports). This will also be the approach that gets this "out the door" the fastest, as you'll then be able to query the results immediately.

  1. Printing each record to the DBMS_OUTPUT (yuck) using a resultset
  2. Use a cursor to work through each record of the resultset, and write the data to a csv file.

For option 2 (which I'd likely take but of course you don't have to), it would be something like:

--If you have an existing table (TableName) that you always want to use
DECLARE
    v_dbver    BINARY_INTEGER;
BEGIN
    SELECT TO_NUMBER(SUBSTR(version,1,2)) INTO v_dbver FROM v$instance;
    DBMS_OUTPUT.PUT_LINE('Version ='||v_dbver);
    
    --This table needs to exist already, so you'll have to have created it ahead of time
    TRUNCATE TABLE TableNameHere; --Truncate the table to remove artifacts
    
IF v_dbver < 12 THEN
    DBMS_OUTPUT.PUT_LINE('Running against 11g');
    --11g
    INSERT INTO TableName
    (NAME,OPEN_MODE,DBID)
    SELECT NAME,OPEN_MODE,DBID 
    FROM v$database; 
    
ELSIF v_dbver >= 12 THEN
    DBMS_OUTPUT.PUT_LINE('Running against 12c');
    --12c
    INSERT INTO TableName
    (NAME,OPEN_MODE,DBID,con_id)
    SELECT NAME,OPEN_MODE,DBID,con_id 
    FROM v$database;
END IF;
END;


--If you want it to DYNAMICALLY create the table:


DECLARE
    v_dbver    BINARY_INTEGER;
BEGIN
    SELECT TO_NUMBER(SUBSTR(version,1,2)) INTO v_dbver FROM v$instance;
    DBMS_OUTPUT.PUT_LINE('Version ='||v_dbver);
    
    --This table needs to exist already, so you'll have to have created it ahead of time
    TRUNCATE TABLE TableNameHere; --Truncate the table to remove artifacts
    
IF v_dbver < 12 THEN
    DBMS_OUTPUT.PUT_LINE('Running against 11g');
    --11g
    CREATE TABLE TableName_Timestamp --This will auto-create the table 
    AS
    SELECT NAME,OPEN_MODE,DBID 
    FROM v$database; 
    
ELSIF v_dbver >= 12 THEN
    DBMS_OUTPUT.PUT_LINE('Running against 12c');
    --12c
    CREATE TABLE TableName_Timestamp --This will auto-create the table 
    AS
    SELECT NAME,OPEN_MODE,DBID,con_id 
    FROM v$database;
END IF;
END;

EDIT!! Well, my world has been turned upside down. While this approach isn't perfect, it CERTAINLY changes things.

Return many rows on a plsql Oracle10g

This solution allows you to set up and deploy a function, and have that function return datasets from a SELECT statements. Give it a shot!!

Related Question