Oracle - select a specific column from a ref cursor
get column names from cursor oracle
ref cursor with multiple columns
ref cursor in oracle example techonthenet
parameterized ref cursor in oracle
how to pass value from one cursor to another cursor in oracle
ref cursor attributes in oracle
ref cursor for loop in oracle example
I have a table named Table1. It has lots of columns, one of them is Column1. I don't know the other columns, they may even change sometimes. There is a strongly typed ref cursor type which returns Table1%rowtype, named cur_Table1. I have a stored procedure named SP1 which has an out parameter of type cur_Table1. I'm calling this SP1 stored procedure from another database that only sees this stored procedure, but not the table or the type itself. How do I select only Column1 from the returned cursor? I know I can fetch into a record or as many variables as the cursor has columns, but I only know of one column's existence so I can't declare the complete record or correct number of variables.
You can do this with
DBMS_SQL, but it ain't pretty.
Table and sample data (COLUMN1 has the numbers 1 - 10):
create table table1(column1 number, column2 date, column3 varchar2(1000), column4 clob); insert into table1 select level, sysdate, level, level from dual connect by level <= 10; commit;
Package with a procedure that opens a ref cursor and selects everything:
create or replace package test_pkg is type cur_Table1 is ref cursor return table1%rowtype; procedure sp1(p_cursor in out cur_table1); end; / create or replace package body test_pkg is procedure sp1(p_cursor in out cur_table1) is begin open p_cursor for select column1, column2, column3, column4 from table1; end; end; /
PL/SQL block that reads COLUMN1 data from the ref cursor:
--Basic steps are: call procedure, convert cursor, describe and find columns, --then fetch rows and retrieve column values. -- --Each possible data type for COLUMN1 needs to be added here. --Currently only NUMBER is supported. declare v_cursor sys_refcursor; v_cursor_number number; v_columns number; v_desc_tab dbms_sql.desc_tab; v_position number; v_typecode number; v_number_value number; begin --Call procedure to open cursor test_pkg.sp1(v_cursor); --Convert cursor to DBMS_SQL cursor v_cursor_number := dbms_sql.to_cursor_number(rc => v_cursor); --Get information on the columns dbms_sql.describe_columns(v_cursor_number, v_columns, v_desc_tab); --Loop through all the columns, find COLUMN1 position and type for i in 1 .. v_desc_tab.count loop if v_desc_tab(i).col_name = 'COLUMN1' then v_position := i; v_typecode := v_desc_tab(i).col_type; --Pick COLUMN1 to be selected. if v_typecode = dbms_types.typecode_number then dbms_sql.define_column(v_cursor_number, i, v_number_value); --...repeat for every possible type. end if; end if; end loop; --Fetch all the rows, then get the relevant column value and print it while dbms_sql.fetch_rows(v_cursor_number) > 0 loop if v_typecode = dbms_types.typecode_number then dbms_sql.column_value(v_cursor_number, v_position, v_number_value); dbms_output.put_line('Value: '||v_number_value); --...repeat for every possible type end if; end loop; end; /
How to fetch one column from a sys_refcursor? - Ask Tom, How do I get only few columns from a sys_refcursor and pass it as an declare cur sys_refcursor; rec t%rowtype; begin open cur for select stored procedures - Oracle - select a specific column from a ref cursor - Stack Overflow. I have a table named Table1. It has lots of columns, one of them is Column1. I don't know the other columns, they may even change sometimes. There is a strongly typed ref cursor type which returns Stack Overflow.
Given the original question, jonearles's answer is still correct, so I'll leave it marked as such, but I ended up doing something completely different and much better.
The problem was/is that I have no control over SP1's database, I just have to call it from somewhere else as a 3rd party client. Now I managed to get permission to see not only SP, but also the type of the cursor. I still don't see the table but now there is a much cleaner solution:
In the other database I have been granted access to see this type now:
type cur_Table1 is ref cursor return Table1%rowtype;
So in my database I can do this now:
mycursor OtherDB.cur_Table1; myrecord mycursor%rowtype; ... OtherDB.SP1(mycursor); fetch mycursor into myrecord; dbms_output.put_line(myrecord.Column1);
See, I still don't need any access to the table, I see the cursor only. The key is that the magical %rowtype works for cursors as well, not just tables. It doesn't work on a sys_refcursor, but it does on a strongly typed one. Given this code, I don't have to care if anything changes on the other side, I don't have to define all the columns or records at all, I just specify the one column I'm interested in.
I really love this OOP attitude about Oracle.
Fetch Ref cursor to another ref cursor - Ask Tom, refcursor(for some reason I can't change out cursor type). Below is the open l_ref_cursor for select col1,col2, col3 from my_table; loop If you only need the first two columns, then only fetch those in the calling procedure. Summary: in this tutorial, you will learn about PL/SQL cursor variables and how to manage cursors variables using REF CURSOR. Introduction to PL/SQL cursor variables. A cursor variable is a variable that references to a cursor. Different from implicit and explicit cursors, a cursor variable is not tied to any specific query. Meaning that a
Don't know if it's an option or not, but wouldn't a better solution be to create a function that returns the specific value you're looking for? That avoids the overhead of sending the extra data. Alternatively, you could define a cursor with a set of known fields in it that both parties know about.
Get Columns from Ref Cursor // oraclerecipes, The following example shows you how to get a list from an SQL SELECT query which is located in a string and dynamically openend into a REF Select from a REF CURSOR - Oracle Server. This may be a dumb question, but I've never got my head round the strange TABLE (CAST ()) syntax. I have a function which returns a REF CURSOR. What I'd like to do is to use this function in a SELECT statement. Something like: CREATE FUNCTION f (args) RETURNS SYS_REFCURSOR
Fetching ref cursor with unknown number of columns, Hello, I'm executing the cross tab function (Pivot) and it return a query for the cross Subject: [oracle-db-l] Fetching ref cursor with unknown number of columns. Using REF CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application. A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database.
4.9 REF CURSORs and Cursor Variables, A cursor variable is not tied to a single particular query like a static cursor. order of fields with compatible data types and can also optionally return a result set. Whenever Oracle executes an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE, it automatically creates an implicit cursor. Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor’s information and statuses such as SQL%ROWCOUNT , SQL%ISOPEN , SQL%FOUND , and SQL%NOTFOUND .
PL/SQL Cursor Variables with REF CURSOR, Different from implicit and explicit cursors, a cursor variable is not tied to any specific query. Meaning that a cursor variable can be opened for any query. The most The problem here (besides the fact that SQL Server doesn't use cursors like Oracle, from what I can gather) is that you should never use SELECT * in your queries. The SQL language (certainly the T-SQL language) does not do things generically.
- Please explain how you propose to call a procedure in another database (or any database, for that matter) without being able to see the types of the arguments.
- I did a "grant execute on SP to another", but didn't grant anything else, not on the table or the types package. And it works.
- Oh, and on the calling side, I put the out parameter of SP into a sys_refcursor, of course.
- Wow... and I thought it would be something trivial like "select column1 from cursor".
- I have no control over SP's database, I just have to call it from somewhere else as a 3rd party client.