SQL & PL/SQL :: How To Handle Ref Cursor Returned From Another Function
Dec 16, 2011
declare
type osd_refone is ref cursor;
osd_ref osd_refone;
l_status number;
[code]......
abc_reports in this pack "ab_report" it is the function it having the ref cursor as out parameter . when am executing the above anonymous block am getting the below error,so how can i print the out ref cursor data in my block.
ERROR at line 8:
ORA-06550: line 8, column 12:
PLS-00221: 'OSD_REF' is not a procedure or is undefined
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
I need to create a function where in data from 5 rows is clubbed into one row. Like this I have around 425 rows which should be clubbed to 85 rows. Requirement is similar to pivot but not exactly like a pivot as different columns need to be taken from those 5 rows. This is for reporting purpose in order to get data in the desired report format.
SQL mentioned below works fine. It does return data.When below code is used as a normal procedure with OUT parameter as Index by table of Record type code works fine. It returns data. Functionality is met. But when used as a pipeline function, it returns no data.
Below code gets compiled but returns nothing. I didn't find anything on Google or any website for same.
I have a business need to have a db function that would construct and return a (temporary) CLOB value.
here is its sample code:
create or replace package PKG_TEST_CLOB as function FN_TEST_TEMP_CLOB return clob;
[code]....
when this function is invoked from a SQL Statement...
*** select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB from dual; ***
... the NOCACHE_LOBS counter in V$TEMPORARY_LOBS for my session is incremented by 1
when this function is invoked via a PL/SQL block...
*** declare l_clob clob; begin select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB into l_clob from dual; end; / declare
[code]....
... the counter doesn't budge
In real life, this function will be used by a Reporting Tool (cognos) via SQL. I tested it, and it seems that it is allocating a new temp lob segment with every invocation.
I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?
The code I have is as follows:
CREATE OR REPLACE PACKAGE qi_test IS
TYPE typ_qi_data IS RECORD( iQIFlag NUMBER(1), iIPFlag NUMBER(1), iRiskIndicator NUMBER(1), iDenominator NUMBER(8), iNumerator NUMBER(8) [code]........
I want to be able to execute the above function using dynamic SQL. Initially tried:
DECLARE f2_data qi_test.typ_qi_data_tab; BEGIN EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;' USING OUT f2_data; [code]......
...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:
f2_data := qi_test.get_f2_data;
...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.
Currently, I'm reading the online book Oracle Concepts, Chapter 3: 3 Indexes and Index-Organized Tables, section: Reverse Key Indexes in order to understand this topic.
As I understand for each pair of (key, rowid) in the index structure, the rowid for each row in the table obviously remains the same but the bytes of the key are reversed before the key is stored. So for example on a 32 bit machine (just an example) a key = 10 AB CD EF will be stored as FE DC BA 01 , am I right?
According to the documentation, this becomes interesting in RAC environments in order to remove a hot spot from the index (when multiple instances repeatedly modify the same block) with the disadvantage that in some cases there cannot be Index Range Scan any more as data in the index is not sorted by column key when it is stored.
I was just curious to see how bytes of each key are reversed and after a bit googling I found an article where Tom Kyte shows with an example by using dump function (which as I understand gives the internal representation of a given expression) the difference in the sequence of bytes. Here is the link
[URL]
So based on his instructions I tried to do my own test, yet I don't get the same result, that is, the bytes are not reversed for me once we rebuild the index by REVERSE key word.
I'm going to write down here the test that I did, where is/are my error(s)
Test Case: (I use a copy of the employees table in hr sample schema) SQL> CREATE TABLE emp_test AS SELECT * FROM hr.employees; Table created. SQL> CREATE INDEX emp_test_idx ON emp_test(first_name);
[code]...
Now, a test SQL Query using the index we've just defined (just for giving an example)
SQL> SELECT first_name, 2 dump(first_name, 16) as dump_result 3 FROM emp_test 4 WHERE first_name = 'Kelly';
FIRST_NAME DUMP_RESULT -------------- ---------------------------------- Kelly Typ=1 Len=5: 4b,65,6c,6c,79
[code]...
So, according to the above Execution plan, Oracle does an Index Range Scan using the index that I defined on my table that is, emp_test_idx. According to the output of dump, the key (first_name) in that index is stored (in terms of bytes) as 4b,65,6c,6c,79
Which as we can see corresponds to the first name 'Kelly', the first name we specified in the above SQL query.
Now let's rebuild the index
SQL> ALTER INDEX emp_test_idx REBUILD REVERSE; Index altered. SQL>
Once the index keys have been reversed, I run the very same query in order to see the difference
SQL> SELECT first_name, 2 dump(first_name, 16) as dump_result 3 FROM emp_test 4 WHERE first_name = 'Kelly';
[code]...
So the second time after the index has been reversed, I still get the very same sequence of bytes, that is, 4b,65,6c,6c,79, whereas I expected to get 79,6c,6c,65,4b (that is, the reversed order of the initial bytes sequence)
I am strugling hard to pass a cursor to my function as in parameter.here is my code
Function migrate_audits (sys_audit_ids SYS_REFCURSOR ) return number; Function migrate_audits (sys_audit_ids in sys_refcursor ) return number is v_return number; v_sys_audit_id number; begin LOOP FETCH sys_audit_ids INTO v_sys_audit_id; [code]....
passing cursor to a function is not possible in oracle? what other option I have to pass collection to the function ?
I have code inside function ..... cursor cur1 is select * from sarchkler where sarchkler_appl_no = in_appl_no begin select max(saradap_appl_no) into in_appl_no from saradap; for rec1 in cur1 loop ...... my question I get variable for cursor after cursor declaration
I've seen several code samples that show how to return a REFCURSOR from a Stored Function, but when I try it with C# it gives me allways WRONG NUMBER OF ARGUMENTS or somehing like that.
I presently don't have my code in here, but it is something like this:
To display highest marks,least marks,average marks,total marks of the student name entered.
desc stud; Name Null? Type ----------------------------------------- -------- ---------------------------- SID NUMBER NAME VARCHAR2(20) M1 NUMBER M2 NUMBER
I have strange problem when i try to return a ref cursor holding data from a select on a oracle global temporary table. If i iterate through the cursor , i can see the values but the function as such returns nothing through the ref cursor. I tried the temporary table as both delete on commit and preserve on commit
create or replace PACKAGE BODY BILL AS
FUNCTION FILTERI RETURN BILL.refcursor IS testcursor BILL.refcursor;
ttstatus INT; iSuccess INT; returns INT; TruncatedSQL1 VARCHAR2(32767); BEGIN [code].........
I have Two cursor record block..which is attached in form..
My TASK IS
In my first Block, When DBCR Column = 'D' Then in backend this column value should be save as a '1' WHEN DBCR Column = 'C' Then Then in backend this column value should be save as a '2'
My Both Field is on Data Block...
In Property palette of this field can we write any decode condition..so it reflects directly on database.
declare ret_val number; begin exec p_buildinfo('252657020001', to_date('20120820','YYYYMMDD'),to_date('20120928','YYYYMMDD'),ret_val, 0); DBMS_OUTPUT.PUT_LINE('Value Returned Is : '||ret_val) ; end;
I getting the below error
ORA-06550: line 3, column 10: PLS-00103: Encountered the symbol "P_BUILDINFO" when expecting one of the following:
:= . ( @ % ; The symbol ":=" was substituted for "P_BUILDINFO" to continue.
the procedure structure is
CREATE OR REPLACE PROCEDURE p_buildsinfo ( var_p_cod CHAR := NULL, var_p_dat_from DATE := NULL, var_p_dat_to DATE := NULL, po_var_l_nxt_seq IN OUT NUMBER, var_p_consol_flg NUMBER default 0 )
I have employees under a supervisor defines as below:
select LOGIN_USER_ID from APPWMS.VIEW_EMP_LATEST_INFO where SPVSR_LOGIN_USER_ID='erbrand' and EMP_STAT_CODE='ACTIVE'
Now I need to determine if all above employees are clocked in , clocked out or not clocked between yesterday and today using following:
select to_char(CLOCK_IN_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockIn,to_char(CLOCK_OUT_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockOut ,LOGIN_USER_ID,--CLOCK_IN_DATE,CLOCK_OUT_DATE, CLOCK_OUT_DATE-CLOCK_IN_DATE,trunc(sysdate) , trunc(sysdate-1), case when CLOCK_OUT_DATE is null then 'Not clocked out' else
[code]....
The first SQL gives me 66 rows while second gives me 40 rows. For 26 people , no rows are returned which means these people donot have a clock in record between two timestamps.
How can we modify query to show those 26 people as 'Not clocked In'
I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.
how to proceed further and the ways how can I write this requirement.
1 select s.reg_no,s.course_code, 2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag 3 from attendance a ,src s 4 where a.semester_code=1 5 and a.semester_year=2013 6 and s.semester_code=1 [code]....
is it possible to use the records returned by a query as column names in a select query.
select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%') from v$database; * ERROR at line 1: ORA-01427: single-row subquery returns more than one row
I am oracle clinical 4.6. In Oracle clinical when i am trying to submit "Print DCF" it should run and generate the report in PDF format. But I am getting the following error message.
MSG-00010: 328900: SRW.RUN_REPORT failed. Updating failure text. REP-1825: Before Report trigger returned FALSE.
Is there a way we could define a record or a nestedtable with a type based on weak refursor i.e
TYPE RC IS REF CURSOR; C2 RC; Type t is table of c2%rowtype; Following is some more explanation of what I am trying to do.
I have a table T with column A and B. Column A is a primary key with number 1,2,3,4,5,6, Column B has diffrent sql stmts stored. i.e 'Select * from emp', Select count(1) from dept' and so on. So table will look like
1 Select * from emp 2 Select count(1) from dept
Now I want to select statements stored in table T one by one and execute them by using cursor. Problem arises as i need to fetch the cursor into some variable but the outcome of each statment is diffrent and oracle does not allow to use cursorname%rowtype for a weak ref cursor.
I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
I'm working with sqlldr and i try to insert data from a csv file to a CTL file. One field of my table contains 5 characters but one row has 6 characters in this field, so it's rejected by oracle. (Logical, you can't insert 6 chars in a 5 chars field)
an error is visibly returned, so i wondered how you could catch the value of this error?is it a code? a message?
I'd like to add to my script a condition so that the end of the script would continue even if this error code is returned for that CTL execution.
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS TYPE emp_cur_type IS REF CURSOR; PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER); END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and sid = 7 and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 4 opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 11 <----- 7+ opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors) opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 53 <----- 17+ opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
