SQL & PL/SQL :: Pipeline Function - No Rows Returned
Aug 11, 2011
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 am trying to create one pipelined function but facing some errors as below
SQL> CREATE OR REPLACE 2 FUNCTION FUN_PIPELINED_EMP 3 RETURN org_typ Pipelined 4 IS 5 CURSOR c1 IS SELECT EMPLOYEE_ID,DESIGNATION,DEPT_ID,PROJECT_ID,MANAGER_ID FROM 6 EMPLOYEE_DETAILS1; 7 org_rec c1%rowtype; 8 begin [code]....
In the follow code example, is it possible to save the seeds that I generated into a table when I call this table function without expliciting doinginsert into <some_table>select select * from table(pkg_seed.getSeed(200)); I try the automonous_transaction clause but it does not work.
--drop package pkg_seed--drop type seed_tab CREATE or replace TYPE seed_rec AS OBJECT( id number,seed number); CREATE or replace TYPE seed_tab AS TABLE OF seed_rec; CREATE or replace PACKAGE pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED;END pkg_seed;/ CREATE or replace PACKAGE BODY pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED IS cursor cur_seed(vmaxrow number) is select rownum id, floor(dbms_random.value(1,1000) ) seed from dual connect by level <= vmaxrow; l_seed cur_seed%rowtype; BEGIN open cur_seed(maxrow); LOOP FETCH cur_seed into l_seed; pipe row(seed_rec(l_seed.id,l_seed.seed)); END LOOP; RETURN; -- the function returns a single result END getSeed;END pkg_seed;/ select * from table(pkg_seed.getSeed(200));
My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as ( select 1 as t from dual union all select 2 as t from dual union all select 3 as t from dual union all select 4 as t from dual union all select 5 as t from dual ) select * from data where t in (2,3)
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 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 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'
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]....
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 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 am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.
We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log. Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.
How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?
I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.
Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.
I can easily calculate the standard deviation for some rows with the STDDEV function. But that returns me a value expressed in the same units as the things being measured. (In other words, if I have times of 1,12,6,11 and 8 seconds, I will be told that the average time is 7.6 seconds, and the standard deviation is 4.39 seconds).
What I'd like to be able to do is to say that the record with value 1 is (say) 3 std deviations away from the average. Or that the 6 second row is within 1 standard deviation.
I am trying to find records which are more than 3 standard deviations of the mean, because they are the outliers I am interested in.
I cannot see how to convert a STDDEV result into being a "number of standard deviations".
(If I have not expressed myself clearly, I'm using this sort of stuff URL....and I'm trying to find >3-sigma records)
simply to multiply the STDDEV result by 1, 2, 3 and so on, and then compare to the time? That is, is it OK to do the following:
Mean = 7.6 seconds (from my sample data mentioned in the above post) StdDev = 4.29 seconds
So 2sigma would be 4.29*2 = 8.78 seconds, plus or minus the mean?
So any record with a time value between -1.18 (7.6-8.78) and 16.38 (7.6+8.78) seconds would be within 2-sigma, assuming normal distribution?
I am still wondering if there's a nice function or something that returns the sigma value for any given set of records?
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 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.
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'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.
