Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here is a simple function which dynamically opens a cursor variable, using the passed table name parameter. </p> <pre><code>create or replace function get_details_by_dno ( p_tab in user_tables.table_name%type , p_dno in dept.deptno%type ) return sys_refcursor is rv sys_refcursor; stmt varchar2(32767); begin stmt := 'select * from ' ||p_tab ||' where deptno = :1'; open rv for stmt using p_dno; return rv; end; / </code></pre> <p>It also uses the DEPTNO as a filter; consequently the function <em>will fail</em> if we pass a table which doesn't have such a column. </p> <p>Some clients can interpret the ref cursor's metadata. For instance JDBC and ODBC ResultSets can do this. SQL*Plus can do it:</p> <pre><code>SQL&gt; exec :rc := get_details_by_dno('DEPT', 50) PL/SQL procedure successfully completed. SQL&gt; print rc DEPTNO DNAME LOC REGION ---------- -------------- ------------- ---------- 50 HOUSEKEEPING INTERNAL SQL&gt; exec :rc := get_details_by_dno('EMP', 50) PL/SQL procedure successfully completed. SQL&gt; exec :rc := get_details_by_dno('EMP', 50) PL/SQL procedure successfully completed. SQL&gt; print rc EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 8085 TRICHLER PLUMBER 8061 08-APR-10 3500 50 8060 VERREYNNE PLUMBER 8061 08-APR-08 4000 50 8061 FEUERSTEIN PLUMBER 7839 27-FEB-10 4500 50 8100 PODER PLUMBER 8061 3750 50 SQL&gt; </code></pre> <p>PL/SQL cannot do this. So we need to be explicit about the table and column names.</p> <pre><code>create or replace procedure print_details_by_dno ( p_tab in user_tables.table_name%type , p_dno in dept.deptno%type ) is rc sys_refcursor; emp_rec emp%rowtype; dept_rec dept%rowtype; begin rc := get_details_by_dno( p_tab , p_dno ); if p_tab = 'EMP' then fetch rc into emp_rec; while rc%found loop dbms_output.put_line('ename='||emp_rec.ename||' empno='||emp_rec.empno); fetch rc into emp_rec; end loop; elsif p_tab = 'DEPT' then fetch rc into dept_rec; while rc%found loop dbms_output.put_line('dname='||dept_rec.dname); fetch rc into dept_rec; end loop; end if; end; / </code></pre> <p>Let's see it running:</p> <pre><code>SQL&gt; set serveroutput on SQL&gt; exec print_details_by_dno('EMP',50) ename=TRICHLER empno=8085 ename=VERREYNNE empno=8060 ename=FEUERSTEIN empno=8061 ename=PODER empno=8100 PL/SQL procedure successfully completed. SQL&gt; exec print_details_by_dno('DEPT',50) dname=HOUSEKEEPING PL/SQL procedure successfully completed. SQL&gt; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POEXECUTE IMMEDIATE in plsql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USAPC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    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. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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