Toad World® Forums

PL/SQL execute immediate select to grid


#1

Ok, I feel pretty dumb. I use PL to insert/update and other dml activities, but
I have an odd request which requires me to code PL to produce a data extract for
a user. I never had to use PL to select stuff to the grid.

I can’t seem to get the select statement to output to the grid.

begin
execute immediate (‘select sysdate from dual’);
end;

begin
dbms_output. put_line (‘select sysdate from dual’);
end;

Both execute, but no output to grid. To work around this, I created a temp
table, populate it with PL, then select * from temp. But my users have no create
privs.

Thanks. Regards,
Tom


#2

Individual Select statements will be executed by the editor and populated in the
grid.

IE:

Select sysdate from dual;

Debbie


#3

The problem is I am using dynamic parameters in my statement:

v_dynam := ‘select ‘||c_row.ccyymm| |
’ ,this’||
’ ,that||
’ ,somehing_else| |
’ from US’||c_row.cycle_ cd||c_row. mon_id||
’ where this = '||c_row2.this;
execute immediate v_dynam;


#4

The problem is I am using a cursor to populate parameters in my select statement
and looping the cursor:

declare
v_dynam varchar2(1000) ;
cursor tbl_cursor is
select account_id
,user_no
,cycle_cd
,mon_id
,ccyymm
from adm.account
where account_id in (1234,1235,1236)
and mon_id in (1,2,3)
;
begin
for c_row in tbl_cursor loop
v_dynam := ‘select ‘||c_row.ccyymm| |
’ ,account_id’ ||
’ ,user_no’||
’ ,seizure_dt’ ||
’ ,metric1’||
’ from US’||c_row.bill_ cycle||c_ row.mon_id| |
’ where account_id = '||c_row.account_ id;
execute immediate v_dynam;
end loop;
end;


#5

The code is a simplified version of what I’m doing, but the main issue is I need
to go to multiplte tables depending on an ID and Month I get from another query.
Hence the cursor gets the cirteria that bulds the table name and other things.

This works great if I insert into a temp table my dynamic sql, but I’d rather
just output it to the grid rather than insert into a table.


#6

The editor executes one statement at a time and allows use of bind variables using the colin.

Select * from table_name where column_name = :a;

The values are to be supplied by the user and you will be prompted for the value. This type of user input can be used in Toad Reports, Query Builder, Pivot Grid reports, etc.

If you want to build a report that executes a query with a bind variable without user input, you need to use Automation. See this posting.

http://www.toadworld.com/BLOGS/tabid/67/EntryID/498/Default.aspx

Debbie


#7

At one time we did display ref cursor out parameters from functions and procedures. But I don’t think that is going to help you in this situation.

TDA is not set up for PL/SQL development. It is mainly a SQL IDE.