Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The name of variable in code above misleaded you. Your variable <code>outtable</code> is in <code>table</code> type. It isn't possible to fetch record data into table of records, but you can fetch it into record itself. </p> <pre><code>DECLARE REFCUR SYS_REFCURSOR; TYPE RECORDTYPE IS RECORD (COL1 NUMBER, COL2 VARCHAR(100)); outtable RECORDTYPE; BEGIN SP_OUT_RefCur_PARAM(REFCUR); LOOP FETCH REFCUR INTO outtable; EXIT WHEN REFCUR%NOTFOUND; dbms_output.put_line(outtable.col1); END LOOP; CLOSE REFCUR; END; </code></pre> <p><strong>Update:</strong> If you want to fetch all data for better performance your application you need to use BULK COLLECT statement:</p> <pre><code>DECLARE REFCUR SYS_REFCURSOR; TYPE RECORDTYPE IS RECORD (COL1 NUMBER, COL2 VARCHAR(100)); TYPE TABLETYPE IS TABLE OF REFTABLETYPE INDEX BY PLS_INTEGER; outtable TABLETYPE; BEGIN SP_OUT_RefCur_PARAM(REFCUR); LOOP FETCH REFCUR INTO BULK COLLECT outtable; EXIT WHEN outtable.COUNT = 0; FOR indx IN 1 .. outtable.COUNT LOOP dbms_output.put_line(outtable(indx).col1);; END LOOP; END LOOP; CLOSE REFCUR; END; </code></pre> <p>Note: memory consumption with the BULK statement is much more than without.</p> <blockquote> <p>The most important thing to remember when you learn about and start to take advantage of features such as BULK COLLECT is that there is no free lunch. There is almost always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory." (<a href="http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html" rel="noreferrer">Oracle Magazine</a>)</p> </blockquote> <p>But if you are just fetching and processing the rows - a row at a time there is no needs in <code>BULK</code> statement, just use the cursor <code>FOR LOOP</code>. (<a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:1203923200346667188" rel="noreferrer">Ask Tom</a>)</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to fetch Oracle reference cursor into table variable?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAlexander Myshov
    1. USAlexander Myshov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to fetch Oracle reference cursor into table variable?
      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. COthanks alexander,that worked, so if we are inserting record by record we need to insert into record itself, if we are bulk inserting (bulk collect) then we need to create a table variable instead of table type variable right?
      singulars
      1. PO
      1. USsam
    2. CO@sam As I understand you, if you want to use `BULK COLLECT` you can to use just of collection of records. I just updated my answer, so you can find more information now.
      singulars
      1. PO
      1. USAlexander Myshov
 

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