Home » SQL & PL/SQL » SQL & PL/SQL » multiple SQL statements in dynamic SQL query
multiple SQL statements in dynamic SQL query [message #282616] Thu, 22 November 2007 09:59 Go to next message
tox_tox
Messages: 2
Registered: November 2007
Junior Member
Hello experts,

is there a possibility in Oracle to place in dynamic query several SQL (e.g. SELECT) statements and to execute them additionally?

Example:
v_sqlQuery :='
SELECT column_1
FROM table_a

SELECT column_1
FROM table_b'

open cv_1 for to_char(v_sqlQuery);


Thank you in advance!

anton


Re: multiple SQL statements in dynamic SQL query [message #282618 is a reply to message #282616] Thu, 22 November 2007 10:33 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I have a feeling that you have vastly simplified what you are trying to do. If you have not, then simply union the 2 statements together in the string (and you don't need the to_char).
Re: multiple SQL statements in dynamic SQL query [message #282627 is a reply to message #282618] Thu, 22 November 2007 12:28 Go to previous messageGo to next message
tox_tox
Messages: 2
Registered: November 2007
Junior Member
hello publolee,

it was really disastrous example. I can make it a little bit complicated.

What is actually need to be done is
1)to read and count all IDs into temporary table doc_id_count.

2)to read and count all IDs and marital_fields into table status_presel

3) to match data using their ids

Ouput would be like:
v_doc_id married single widowed
Miller 2 3 4

I think the idea is becomming more clear - to write data in temporal table and then read them in the next SELECT statement by matching the IDs.

Comment: I am using a wrong syntax (T-SQL syntax) for coping into temporary tables.


v_counter := '
   SELECT
        NAMES.' || v_doc_id || '
        ,Count(*) Count
   INTO #doc_id_count
   FROM
        NAMES
   GROUP BY
        ....-- group by v_doc_id
   ';

v_status_pre := '
   SELECT
        NAMES.' || v_doc_id || '
        ,NAMES.'|| v_marital_field || ' status_field
        ,COUNT(*) Count
   INTO #status_presel
   FROM
         NAMES
   GROUP BY
   ... -- group by v_doc_id and v_marital_field
   ORDER BY
   ...
   ';

v_status := '
   SELECT
        #doc_id_count.' || v_doc_id || '
        ,#status_presel.Count married
        ,status_1.Count single
        ,status_pre2.Count widowed


   FROM
        #doc_id_count
        
        LEFT OUTER JOIN     #status_presel
        ON ...
        AND #status_presel.status_field = ''married''


        LEFT OUTER JOIN     #status_presel status_1
        ON ...
        AND status_1.status_field = ''single''
        
        LEFT OUTER JOIN     #status_presel status_2
        ON ...
        AND status_2.status_field = ''widowed''
   ORDER BY
...
   ';

v_sql := v_counter + v_status_pre + v_status;
open cv_1 for to_char(v_sqlQuery);



Any ideas how to do it? I would appreciate any comment.
Re: multiple SQL statements in dynamic SQL query [message #282692 is a reply to message #282627] Fri, 23 November 2007 00:51 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Putting several queries in a cursor is not possible (as you use "open for" it is a cursor)
2/ Putting several queries in a dynamic string in "execute immediate" is possible, you have to enclose them in a PL/SQL block
3/ Using temp tables is not in the Oracle way of programming
4/ In your case you can do the whole in one SQL query using subqueries.

Regards
Michel
Previous Topic: Null Value Storage in Oracle
Next Topic: HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL
Goto Forum:
  


Current Time: Fri Dec 02 12:46:22 CST 2016

Total time taken to generate the page: 0.16014 seconds