Re: EXECUTE IMMEDIATE v_sql_stmt is not executing

From: Mark Van de Wiel <mark.van.de.wiel_at_gmail.com>
Date: Mon, 10 Aug 2015 16:28:44 -0700
Message-ID: <55C933AC.6070202_at_gmail.com>

Nik,

Couple of things:

1) If you want to see the output of a SQL statement then this is not the way to go.

This may work if you define an array (or something similar and use select into in the dynamic SQL. Then loop through your array with dbms_output to get PL/SQL to present you the output.

Alternatively use package dbms_sql.

You could also perform "create table as <your sql>" in the statement and then following your PL/SQL code run some SQL to get output in sqlplus (or some other tool you use).

2) Your dynamic SQL statement should not have a semi-colon at the end.

Hope this helps.

Mark.

Which environment are you attempting to execute this in?

If SQL Plus, what is your server output setting?

/Hans

On 10/08/2015 4:58 PM, Nik Tek wrote:

Hi Andrew,

a) there is no output printing from the v_sql_stmt

b)  DBMS_OUTPUT.PUT_LINE('v_sql_stmt: ------------------------ ' ); didn't print

c)  If I copy/paste the output of DBMS_OUTPUT.PUT_LINE('v_sql_stmt: ' || v_sql_stmt);, I see the query working just fine.

-- http://www.freelists.org/webpage/oracle-l Received on Tue Aug 11 2015 - 01:28:44 CEST