CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Francesco
Francesco

Reputation: 55

Building some dynamic query select and display its output immediately in Oracle PL/SQL

in my actual job I need, very often, to read some tables and acting consequently, sometimes updating these data manually. So I built a PL/SQL block that creates my SELECT statements (yes, with the "FOR UPDATE" clause, just commented). As an example, this is just one of the queries I build:

phtr_QUERY := 'SELECT *
 FROM   ' || tabriabi_impianto || '.pdfhtr t 
 WHERE t.k_abi=''' || tabriabi_abi || ''' ';

if length(myNag) > 0 then
 phtr_QUERY := phtr_QUERY || 'and t.ndg like ''%' || myNag || '%'' ';
end if;

if length(myPrat) > 0 then
  phtr_QUERY := phtr_QUERY || ' and t.pratica like ''%' || myPrat || '%'' ';
end if;
phtr_QUERY := phtr_QUERY || crlf || ' order by 2 ';
phtr_QUERY := phtr_QUERY || crlf || '--for update';
phtr_QUERY := phtr_QUERY || crlf || ';';

Then I copy these statements from the Output window (obtained through the dbms_output.put_line) and paste it into a new SQL Window and executing it, obtaining the results in multiple tabs.

I was wondering if there is a better way, some commands that I can use just to have the (editable) results directly without the need of cut&paste...

TIA.

F.

Upvotes: 1

Views: 527

Answers (3)

simplify_life
simplify_life

Reputation: 405

select a.rowid, a.* from table_name a;

will open in edit mode in many tools.

Upvotes: 1

Ceiling Gecko
Ceiling Gecko

Reputation: 3186

A very horrifying/hackish way to do what you want would be to store the resulting query in a temporary table, afterwards you could do something like the process described here:

How can I use an SQL statement stored in a table as part of another statement?

Please Note: This is probably a bad idea.

Upvotes: 1

Yaroslav Shabalin
Yaroslav Shabalin

Reputation: 1644

I was wondering if there is a better way, some commands that I can use just to have the (editable) results directly without the need of cut&paste

You should understand that editing features are features of database tool you are using. When you insert, update or delete some record in the results grid this tool translates your actions into respective SQL statements and executes it on the fly.

As a kind of workaround I suggest you to create a stored procedure which takes some parameters as 'table name', 'where conditions' and then creates updateable database view. After execution of procedure and preparation of the view you can run "select ... for update" and work with returned data as you do it now.

Upvotes: 0

Related Questions