logo
down
shadow

Oracle - pl sql selecting from SYS_REFCURSOR


Oracle - pl sql selecting from SYS_REFCURSOR

By : user3861961
Date : November 21 2020, 07:01 PM
I hope this helps . A SYS_REFCURSOR won't work for the intended use - you need to create an Oracle TYPE:
code :


Share : facebook icon twitter icon
use oracle cursor within a sys_refcursor

use oracle cursor within a sys_refcursor


By : my_tree_2012
Date : March 29 2020, 07:55 AM
Hope that helps If you're coming in with a number of IDs, then first prize would be to run only one SQL query to fetch the lot in one go, using a bulk in-bind for the IDs. This would probably require a modification to dashboard_package.visits, or writing a new version of the visits procedure to accept a PL/SQL table of IDs instead of a single ID.
If your hands are tied WRT modifying dashboard_package, then you could write a pipelined function to return the rows for a set of IDs:
code :
-- create some helper types for the pipelined function
create type visitobj as object
(id             number
,cat            dashboard.cat%type
,cat_order      dashboard.cat_order%type
,subcat         dashboard.subcat%type
,label_text     dashboard.label_text%type
,current_month  varchar2(13)
,ly_month       varchar2(13)
,ytd_tot        varchar2(13)
,lytd_tot       varchar2(13)
,ly_tot         varchar2(13));
create type visittable as table of visitobj;

create or replace function test_refcur
   return visittable deterministic pipelined is
   refCursorValue SYS_REFCURSOR;
   cat            dashboard.cat%type;
   cat_order      dashboard.cat_order%type;
   subcat         dashboard.subcat%type;
   label_text     dashboard.label_text%type;
   current_month  varchar2(13);
   ly_month       varchar2(13);
   ytd_tot        varchar2(13);
   lytd_tot       varchar2(13);
   ly_tot         varchar2(13);
begin
  for id in (/*iterate through the IDs*/) loop
   dashboard_package.visits(refCursorValue, id);
   loop
      fetch refCursorValue into cat, cat_order, subcat, label_text,
                                current_month, ly_month, ytd_tot,
                                lytd_tot, ly_tot;
      exit when refCursorValue%NOTFOUND;
      pipe row (visitobj (id, cat, cat_order, subcat, label_text,
                          current_month, ly_month, ytd_tot,
                          lytd_tot, ly_tot));
   end loop;
  end loop;
  return;
end test_refcur;

-- now you can simply do this:
SELECT * FROM TABLE(test_refcur);
Oracle sys_refcursor from normal SQL

Oracle sys_refcursor from normal SQL


By : Riyaz Ahmad
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , An anonymous PL/SQL block does not return anything so you won't be able to use the cursor you open in the anonymous PL/SQL block in your client application. In order to return the data to the client application, you would need to use a named PL/SQL block (i.e. a stored procedure or a stored function). If you are not allowed to create named PL/SQL blocks, you won't be able to return a cursor you open in PL/SQL to your client application.
Printing Oracle Sys_refcursor in Oracle SQL Developer 1.5

Printing Oracle Sys_refcursor in Oracle SQL Developer 1.5


By : user2039493
Date : March 29 2020, 07:55 AM
Hope that helps You would need to loop over the ref cursor and for each row in it, print out the individual fields. In your updated version you need to fetch the cursor into local scalar variables, not another ref cursor:
code :
set serveroutput on;
declare
  result sys_refcursor;
  lsn number; -- guessing the data type
begin
  emp.emp360_utils.GET_EMPLOYEEs(222334,result); 
  loop
    fetch result into lsn; -- and other columns if needed
    exit when result%notfound;
    dbms_output.put_line(lsn);
  end loop;
end;
/
variable result refcursor

begin
  emp.emp360_utils.GET_EMPLOYEEs(222334, :result); 
end;
/

print result
Which is better for selecting among these 1.sys_refcursor 2. custom JSON 3.custom object/record type from Oracle 12c pls

Which is better for selecting among these 1.sys_refcursor 2. custom JSON 3.custom object/record type from Oracle 12c pls


By : user3371281
Date : March 29 2020, 07:55 AM
this will help As your can see for yourself in the code for option 1, the SYS_REFCURSOR maps to a ResultSet in Java, so the cursor is closed when you close the ResultSet.
Note that ResultSet objects are automatically closed when the Statement is closed, or when the Statement executes another SQL statement, and that the Statement is closed when the Connection is closed.
Return sys_refcursor of the oracle to c#

Return sys_refcursor of the oracle to c#


By : Janet Andrade
Date : March 29 2020, 07:55 AM
may help you . C#: , Is there a reason why you do not use a function instead of procedure?
Related Posts Related Posts :
  • Refactoring SQL
  • Create view or SP, only if the DB contains a pattern
  • Sql joining a table
  • Optimize the sql query
  • SQL server management studio Express
  • insert into select from in a table with identity column
  • Is is faster to filter and get data or filter then get data?
  • SQL server 2005, syntax error
  • SQL SELECT Join?
  • How to limit results by SUM
  • Need Help with SQL Subquery
  • SQL Join Tables
  • pipelined function
  • how to generate primary key values while inserting data into table through pl/sql stored procedure
  • Calling stored procedure from another stored procedure and returning result as new columns
  • Are these tables respect the 3NF Database Normalization?
  • Complex query making site extremely slow
  • SQL Full-Text Indexing Issue
  • finding a relationship accross many tables
  • Using Parameter Values In SQL Statement
  • oracle sql plus spool
  • Multiple rows update trigger
  • SQL - Conditionally evaluate WHERE clauses
  • Locking User account created under Windows Authentication in SQL Server
  • trigger execution against condition satisfaction
  • oracle sql developer is truncating my results
  • “Function” calling inside stored procedure
  • Excel VBA to Update SQL Table
  • I've caught an exception!! Now what?
  • How to select the last two records for each topic_id in MySQL
  • SQL Server 2005 triggered audit tables moved to SQL Server 2008, now trigger does not respond when trying to insert row
  • Using NHibernate to insert/update using a SQL server-side DEFAULT value
  • How to convert column type from varchar to date in PostgreSQL?
  • Combining the UNIQUE and CHECK constraints
  • MS SQL Server decimal data type rounds up
  • sp_addlinkedserver on sql server 2005 giving problem
  • Transitive SQL query on same table
  • Strange use of the index in Mysql
  • Broken count(*) after adding LEFT JOIN
  • Product Catalog Schema design
  • ODBC Connection String Problem
  • What's a reasonable number of rows and tables to be able to join in MySQL?
  • CFQuery where param
  • Split a Column in SQL Server
  • What does the % symbol do at the end of a string in SQL?
  • sql server 2005 express - Invalid Object Name error
  • Merge overlapping date intervals
  • Loop Control within a DataflowTask in ETL
  • SQL | How to always round up regardless of the last integer value, even when that may be 0
  • how to convert any char to - in sql server?
  • how to create function in postgres
  • Need to speed up the results of this SQL statement. Any advice?
  • How do I retrieve 10 days data up to date 'XYZ' from mysql with Perl when some days data are missing, i.e. public holida
  • How to export data with Oracle SQL Developer?
  • Aggregate SQL column values by time period
  • Are soft deletes a good idea?
  • DBCC SHRINKFILE 1 sproc for multiple databases
  • SQL VBA syntax issue
  • How to select a subset of results from a select statement
  • Oracle SQL: ROLLUP not summing correctly
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk