Oracle execute dynamic sql select script and return tabular data

Question

I am trying to execute dynamic sql select statement and return the result back to C# ExecuteReader.

I can retrieve the result in sql server using following sql code.

Create Table TestCustomer(ID Varchar(50), CustomerName Varchar(100));

Insert Into TestCustomer (ID, CustomerName) Values ('1', 'One');
Insert Into TestCustomer (ID, CustomerName) Values ('2', 'Two');
Insert Into TestCustomer (ID, CustomerName) Values ('3', 'Three');

DECLARE @SQLScript VARCHAR(MAX), @CustomerName VARCHAR(100);

SET @CustomerName = 'Two';
SET @SQLScript = 'select * from TestCustomer where CustomerName = '''+ @CustomerName +''' ';
EXEC(@SQLScript);

But when I try to retrieve the result on oracle server using following oracle sql code, I don't see table result is coming out like sql server does.

DECLARE 
SQLScript VARCHAR2(4000);
CustomerName VARCHAR2(20) := 'Two';
BEGIN
SQLScript := 'select * from TestCustomer where CustomerName = :CustomerName';
EXECUTE IMMEDIATE SQLScript USING CustomerName;
END;

How to return the table result once the dynamic sql script is executed, so that c# can get the table result?

@Crowcoder, If oracle can return the result, C# will be fine. — Frank Myat Thu
– Frank Myat Thu, Commented Jul 16, 2020 at 16:09
Please try to use cursor instead of execute immediate, community.oracle.com/thread/702648 — Govind
– Govind, Commented Jul 16, 2020 at 16:13

Littlefoot · Accepted Answer · 2020-07-16 16:35:44Z

In Oracle, you have to return the result into something. For example, into a local variable as this example shows:

Sample table:

SQL> create table testcustomer as
  2    select 1 id, 'One' customername from dual union all
  3    select 2   , 'Two'              from dual;

Table created.

PL/SQL block:

SQL> set serveroutput on
SQL> declare
  2    sqlscript      varchar2(4000);
  3    l_customername testcustomer.customername%type := 'Two';
  4    retval         testcustomer.id%type;
  5  begin
  6    sqlscript := 'select id from testcustomer ' ||
  7                 'where customername = :1';
  8    execute immediate sqlscript into retval using l_customername;
  9
 10    -- retval now contains the ID returned by that SELECT statement
 11    dbms_output.put_line('ID = ' || retval);
 12  end;
 13  /
ID = 2

PL/SQL procedure successfully completed.

SQL>

However, if you want to actually return that value so that it could be used elsewhere, consider using a function. Slightly adjusted, code looks like this:

SQL> create or replace function f_test
  2    (par_customername in testcustomer.customername%type)
  3    return testcustomer.id%type
  4  is
  5    sqlscript      varchar2(4000);
  6    retval         testcustomer.id%type;
  7  begin
  8    sqlscript := 'select id from testcustomer ' ||
  9                 'where customername = :1';
 10    execute immediate sqlscript into retval using par_customername;
 11
 12    -- retval now contains the ID returned by that SELECT statement
 13    return retval;
 14  end;
 15  /

Function created.

SQL> select f_test('One') from dual;

F_TEST('ONE')
-------------
            1

SQL>

Of course, you can return various things; I returned a scalar value. You can return a refcursor, an array, ... it depends on what you need.

See if it helps.

Alex Poole · Accepted Answer · 2020-07-16 16:53:46Z

If you're on 12c or higher you can use dbms_sql.return_result to sort of replicate what you can do with SQL Server:

DECLARE 
  SQLScript VARCHAR2(4000);
  CustomerName VARCHAR2(20) := 'Two';
  MyCursor SYS_REFCURSOR;
BEGIN
  SQLScript := 'select * from TestCustomer where CustomerName = :CustomerName';
  OPEN MyCursor FOR SQLScript USING CustomerName;
  DBMS_SQL.RETURN_RESULT(MyCursor);
END;
/

Read more in the 12cR1 'what's new' section and PL/SQL language guide.

As of Oracle Database 12c, a PL/SQL stored subprogram can return query results to its client implicitly, using the PL/SQL package DBMS_SQL instead of OUT REF CURSOR parameters. This technique makes it easy to migrate applications that rely on the implicit return of query results from stored subprograms from third-party databases to Oracle Database.

Incidentally, with the code you had:

SQLScript := 'select * from TestCustomer where CustomerName = :CustomerName';
EXECUTE IMMEDIATE SQLScript USING CustomerName;

Oracle won't complain but the query is not actually executed at all; see this note.

Collectives™ on Stack Overflow

Oracle execute dynamic sql select script and return tabular data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related