Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>As table name is not known at compile time you need to use dynamic SQL(<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#BHCEBBAI" rel="nofollow">execute immediate</a>, native dynamic SQL, for instance) to be able to select from a table, name of which is stored as a string literal - you cannot accomplish it with static SQL</p> <p>Here is an example:</p> <pre><code>-- table which contains names of other tables -- in the table_name column SQL&gt; create table Table_Names as 2 select 'employees' as table_name 3 from dual 4 ; Table created SQL&gt; set serveroutput on; -- example of an anonymous PL/SQL block -- where native dynamic SQL (execute immediate statement) -- is used to execute a dynamically formed select statement SQL&gt; declare 2 type T_record is record( -- example of record for fetched data 3 f_name varchar2(123), 4 l_name varchar2(123) 5 ); 6 7 l_table_name varchar2(123); -- variable that will contain table name 8 l_select varchar2(201); 9 l_record T_Record; -- record we are going to fetch data into 10 begin 11 select table_name 12 into l_table_name -- querying a name of a table 13 from table_names -- and storing it in the l_table_name variable 14 where rownum = 1; 15 16 l_select := 'select first_name, last_name from ' || 17 dbms_assert.simple_sql_name(l_table_name) || 18 ' where rownum = 1'; -- forming a query 19 20 execute immediate l_select -- executing the query 21 into l_record; 22 -- simple output of data just for the sake of demonstration 23 dbms_output.put_line('First_name: ' || l_record.f_name || chr(10) || 24 'Last name: ' || l_record.l_name); 25 exception 26 when no_data_found 27 then dbms_output.put_line('Nothing is found'); 28 end; 29 / First_name: Steven Last name: King PL/SQL procedure successfully completed </code></pre> <p>As a second option you could use weakly typed cursors - refcursors to execute a dynamically formed select statement: </p> <pre><code>SQL&gt; variable refcur refcursor; SQL&gt; declare 2 l_table_name varchar2(123); 3 l_select varchar2(201); 4 begin 5 select table_name 6 into l_table_name 7 from table_names 8 where rownum = 1; 9 10 l_select := 'select first_name, last_name from ' || 11 dbms_assert.simple_sql_name(l_table_name) || 12 ' where rownum = 1'; 13 14 open :refcur 15 for l_select; 16 17 exception 18 when no_data_found 19 then dbms_output.put_line('Nothing is found'); 20 end; 21 / PL/SQL procedure successfully completed. SQL&gt; print refcur; FIRST_NAME LAST_NAME -------------------- ------------------------- Steven King SQL&gt; spool off; </code></pre> <p><a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#i7106" rel="nofollow">Find out more</a> about cursors and cursor variables</p>
    singulars
    1. This table or related slice is empty.
    1. POUsing SELECT within SELECT statement in ORACLE
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNick Krasnov
    1. USNick Krasnov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POUsing SELECT within SELECT statement in ORACLE
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COWhen I use your above example (refcursor) in Oracle 10g, I get a popup box saying to enter value of :REFCUR, whatever I enter, or even if I leave it blank it shows (Invalid SQL Statement), why is that? And why are is statement `OPEN :REFCUR FOR l_select ` ? Thanks
      singulars
      1. PO
      1. USiMan
    2. CO@iMan Popup box? Use SQL*PLUS - command line interface. The last example uses SQL*Plus.
      singulars
      1. PO
      1. USNick Krasnov
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload