Cursor Based Record vs Strong ref cursor












1















I'm new in Oracle databases and pl/sql language. My question is what's the differences between strong ref cursor and cursor based record - like e.g. "var_exmpl" variable which will store cursor values i.e. "cur_exmpl%rowtype"










share|improve this question





























    1















    I'm new in Oracle databases and pl/sql language. My question is what's the differences between strong ref cursor and cursor based record - like e.g. "var_exmpl" variable which will store cursor values i.e. "cur_exmpl%rowtype"










    share|improve this question



























      1












      1








      1








      I'm new in Oracle databases and pl/sql language. My question is what's the differences between strong ref cursor and cursor based record - like e.g. "var_exmpl" variable which will store cursor values i.e. "cur_exmpl%rowtype"










      share|improve this question
















      I'm new in Oracle databases and pl/sql language. My question is what's the differences between strong ref cursor and cursor based record - like e.g. "var_exmpl" variable which will store cursor values i.e. "cur_exmpl%rowtype"







      oracle plsql database-cursor






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Dec 29 '18 at 16:32









      Wernfried Domscheit

      24.1k42858




      24.1k42858










      asked Dec 29 '18 at 15:57









      J.DoeJ.Doe

      112




      112
























          1 Answer
          1






          active

          oldest

          votes


















          1














          You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.



          With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.



          Here are some examples:



          DECLARE
          TYPE employee_rt IS RECORD
          (
          employee_id employees.employee_id%TYPE,
          salary employees.salary%TYPE
          );

          TYPE strong_rc IS REF CURSOR
          RETURN employee_rt;

          strong_rec employee_rt;

          CURSOR twocols_cur
          IS
          SELECT employee_id, salary FROM employees;

          rec_from_explicit_cur twocols_cur%ROWTYPE;
          BEGIN
          OPEN strong_rc FOR SELECT employee_id, salary FROM employees;

          FETCH strong_rc INTO strong_rec;

          CLOSE strong_rc;

          OPEN twocols_cur;

          FETCH twocols_cur INTO rec_from_explicit_cur;

          CLOSE twocols_cur;
          END;
          /





          share|improve this answer
























          • So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?

            – J.Doe
            Dec 30 '18 at 13:49











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53971055%2fcursor-based-record-vs-strong-ref-cursor%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          1














          You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.



          With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.



          Here are some examples:



          DECLARE
          TYPE employee_rt IS RECORD
          (
          employee_id employees.employee_id%TYPE,
          salary employees.salary%TYPE
          );

          TYPE strong_rc IS REF CURSOR
          RETURN employee_rt;

          strong_rec employee_rt;

          CURSOR twocols_cur
          IS
          SELECT employee_id, salary FROM employees;

          rec_from_explicit_cur twocols_cur%ROWTYPE;
          BEGIN
          OPEN strong_rc FOR SELECT employee_id, salary FROM employees;

          FETCH strong_rc INTO strong_rec;

          CLOSE strong_rc;

          OPEN twocols_cur;

          FETCH twocols_cur INTO rec_from_explicit_cur;

          CLOSE twocols_cur;
          END;
          /





          share|improve this answer
























          • So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?

            – J.Doe
            Dec 30 '18 at 13:49
















          1














          You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.



          With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.



          Here are some examples:



          DECLARE
          TYPE employee_rt IS RECORD
          (
          employee_id employees.employee_id%TYPE,
          salary employees.salary%TYPE
          );

          TYPE strong_rc IS REF CURSOR
          RETURN employee_rt;

          strong_rec employee_rt;

          CURSOR twocols_cur
          IS
          SELECT employee_id, salary FROM employees;

          rec_from_explicit_cur twocols_cur%ROWTYPE;
          BEGIN
          OPEN strong_rc FOR SELECT employee_id, salary FROM employees;

          FETCH strong_rc INTO strong_rec;

          CLOSE strong_rc;

          OPEN twocols_cur;

          FETCH twocols_cur INTO rec_from_explicit_cur;

          CLOSE twocols_cur;
          END;
          /





          share|improve this answer
























          • So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?

            – J.Doe
            Dec 30 '18 at 13:49














          1












          1








          1







          You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.



          With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.



          Here are some examples:



          DECLARE
          TYPE employee_rt IS RECORD
          (
          employee_id employees.employee_id%TYPE,
          salary employees.salary%TYPE
          );

          TYPE strong_rc IS REF CURSOR
          RETURN employee_rt;

          strong_rec employee_rt;

          CURSOR twocols_cur
          IS
          SELECT employee_id, salary FROM employees;

          rec_from_explicit_cur twocols_cur%ROWTYPE;
          BEGIN
          OPEN strong_rc FOR SELECT employee_id, salary FROM employees;

          FETCH strong_rc INTO strong_rec;

          CLOSE strong_rc;

          OPEN twocols_cur;

          FETCH twocols_cur INTO rec_from_explicit_cur;

          CLOSE twocols_cur;
          END;
          /





          share|improve this answer













          You use ref cursor types to declare cursor variables. You can then fetch from those cursor variables as you would an explicit cursor. In both cases, you can and usually will fetch into a record.



          With an explicit cursor, you can declare the record based on the cursor. With a strong ref cursor type, I would declare a record based on the same type used to declare the ref cursor.



          Here are some examples:



          DECLARE
          TYPE employee_rt IS RECORD
          (
          employee_id employees.employee_id%TYPE,
          salary employees.salary%TYPE
          );

          TYPE strong_rc IS REF CURSOR
          RETURN employee_rt;

          strong_rec employee_rt;

          CURSOR twocols_cur
          IS
          SELECT employee_id, salary FROM employees;

          rec_from_explicit_cur twocols_cur%ROWTYPE;
          BEGIN
          OPEN strong_rc FOR SELECT employee_id, salary FROM employees;

          FETCH strong_rc INTO strong_rec;

          CLOSE strong_rc;

          OPEN twocols_cur;

          FETCH twocols_cur INTO rec_from_explicit_cur;

          CLOSE twocols_cur;
          END;
          /






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Dec 29 '18 at 17:58









          Steven FeuersteinSteven Feuerstein

          1,20158




          1,20158













          • So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?

            – J.Doe
            Dec 30 '18 at 13:49



















          • So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?

            – J.Doe
            Dec 30 '18 at 13:49

















          So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?

          – J.Doe
          Dec 30 '18 at 13:49





          So in both cases we get the same result using different cursors. There is no difference between them so which one is better for performance?

          – J.Doe
          Dec 30 '18 at 13:49


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53971055%2fcursor-based-record-vs-strong-ref-cursor%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Monofisismo

          Angular Downloading a file using contenturl with Basic Authentication

          Olmecas