PL/SQL :: Doing DML Inside Pipeline Function

Oct 10, 2013

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));

View 15 Replies


ADVERTISEMENT

SQL & PL/SQL :: Creating One Pipeline Function

Sep 18, 2012

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]....

View 13 Replies View Related

SQL & PL/SQL :: Create Pipeline Function In Package

Feb 13, 2013

Can we create a Pipelined function in A Package ? I know we can create it standalone function.

View 11 Replies View Related

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.

CREATE TYPE r_report_mth_rec_obj AS OBJECT (
acct_num VARCHAR2 (20),
acct_name VARCHAR2 (80),
fund_group VARCHAR2 (80),
fund_type VARCHAR2 (80),
share_class_code VARCHAR2 (10),
share_class_description VARCHAR2 (20),
curr_code VARCHAR2 (10),
[code]...

View 16 Replies View Related

SQL & PL/SQL :: Pass Multiple Values As Single Input Parameter Into Pipeline Function

Dec 23, 2012

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)

View 2 Replies View Related

SQL & PL/SQL :: Remove String Before And After Pipeline To Get String Between Pipeline?

May 10, 2010

I have a string like below:

string = 'HEADER||MEAL||15'

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?

View 9 Replies View Related

SQL & PL/SQL :: To Use A Function Inside A View

Sep 7, 2011

I am trying to create a view of a query that i would be using regularly...the query contains a function call in it...can i use it..

If yes when i try to do it . It gives out an oRa-01031:in sufficient privileges.

View 3 Replies View Related

SQL & PL/SQL :: Using Procedure Inside Function?

Nov 8, 2011

I have question.Using procedure inside the function ?can I get better performance?

View 8 Replies View Related

Table Creation Inside Function

Feb 25, 2012

i am trying to create table inside function where in after creating table when am trying to access the table with select statement oracle is throwing error 'Table/view doesnot exist -00942', below is the code snippet

create or replace function example (mkey in varchar2) return varchar2
is
g_key varchar2(100);
l_tbl_ntext exception;
pragma exception_init(l_tbl_ntext , -942);
begin

begin
execute immediate 'select * from example1';

exception
when l_tbl_ntext then
null;
end;
execute immediate 'create table example1(skey varchar2, g_key varchar2) storage(buffer_pool, keep)';
end example;
/

select * from example;

View 8 Replies View Related

SQL & PL/SQL :: Cursor With Parameter Inside Function

Mar 7, 2010

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

View 7 Replies View Related

SQL & PL/SQL :: Using Function Inside Query And Without Cursor?

Mar 17, 2013

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

How do I do that using PL/SQL and without Cursor.

View 4 Replies View Related

SQL & PL/SQL :: Function Returning A Table Inside Where Clause?

Apr 5, 2010

DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;

MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').

View 22 Replies View Related

Forms :: Calling Function Inside The Procedure

Jun 2, 2011

I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?

View 4 Replies View Related

SQL & PL/SQL :: Function Batch - Cannot Perform DML Operation Inside A Query

Oct 8, 2013

Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error

SQL Error : ORA-14551: cannot perform a DML operation inside a query

Below Is the

FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS
BEGIN
INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);
RETURN 1;

[code].....

View 27 Replies View Related

Function Or Pseudo-column EXISTS May Be Used Inside SQL Statement

Oct 10, 2013

I am encountering error in this code.

WHILE EXISTS ( SELECT * FROM tblOrgChart WHERE fxOrgID = v_chrTempKeyDept )
LOOP
v_intDept := CAST(v_chrTempKeyDept AS NUMBER) + 1 ;
v_chrTempKeyDept := LPAD('',3 - LENGTH(CAST(v_intDept AS VARCHAR2)),'0') || CAST(v_intDept AS VARCHAR2) ;
END LOOP;

Error: PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only

View 1 Replies View Related

SQL & PL/SQL :: How To Bypass Putting Select Inside Count Function In Query

Oct 21, 2012

I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :

1. The number of assignments created from it (after a given constant assign date)

2. The number of assignments created from it that have been approved.

3. The number of unique users who have either created or approved an assignment from it

Test data :

create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int,
approver_id int, assign_date timestamp, approved_yn varchar2(10));
insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');

[code]....

Assignment :

id r_paper_id assigner_id assignee_id approver_id assign_date approved_yn
-----------------------------------------------------------------------------------------------------------
11 100 200 100 23-10-12 12:00:00.000000000 AMY
22 200 100 200 22-10-12 12:00:00.000000000 AMN
32 100 200 101 24-10-12 12:00:00.000000000 AMY

[code]....

Research_paper:

id name
----------
1A
2B

Expected result :

r_paper_id created approved unique_users
-----------------------------------------------
1 3 2 4
2 3 2 3

I wrote the following query for that :

SELECT rp.id r_paper_id,
COUNT(*) created,
COUNT(
CASE
WHEN a.approved_yn = 'Y'

[code]....

But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.

View 7 Replies View Related

SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body

Jul 26, 2011

CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....

I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?

I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?

View 10 Replies View Related

PL/SQL :: Calling A Function In Remote Database Inside A Stored Procedure

Apr 9, 2013

There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:

CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN

[Code]...

There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................

where my mistake is?

View 7 Replies View Related

SQL & PL/SQL :: Call Function Generates ORA-14551 /cannot Perform A DML Operation Inside Query

Aug 9, 2011

Calling function

select PACK.MAIN('blah') from dual

generates:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245

Unfortunately the Body is not accessible to see.The spec of the function is:

FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:

var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar

But this generates: ORA-01008: not all variables bound

View 3 Replies View Related

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function

Feb 4, 2013

I have the following C code:

class Factorial {
  public:
  int getVal (int a);
};
[code]....

/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.

Just in case, listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

SQL & PL/SQL :: Specifying INTO Inside Execute Immediate?

Jun 29, 2011

The following code works

set serveroutput on
declare
a int;
begin
execute immediate 'select employee_id from employees where first_name=:ab' into a using 'Donald' ;
dbms_output.put_line(a);
end;

but this one doesn't

set serveroutput on
declare
a int;
begin
execute immediate 'select employee_id into :1 from employees where first_name=:2' using a,'Donald';
dbms_output.put_line(a);
end;

Am I not allowed to specify a bind variable with an into clause inside execute immediate ?

View 9 Replies View Related

SQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function

Jun 10, 2010

What is advantage of Deterministic function over normal function?

What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?

View 4 Replies View Related

Loop Inside Cursor

Sep 11, 2012

I have a cursor returning some value.

for each value returned by the cursor i need to traverse through 31 rows(1 row per day * no of days in the month).

E.g. if cursor returns service_name as xyz then for xyz there can be 31 rows(service may not be used on some days)

I need to go to all of them and take some values and move them to a flat file. how should that be done?

Attached File(s)
Query.png ( 20.99K )
Number of downloads: 9

View 1 Replies View Related

SQL & PL/SQL :: Execute Immediate Inside A WHILE Loop

Jan 6, 2011

I have a dynamic query which i want to run till it return zero records.

I am using WHILE loop for that but it is giving compilation error:

The query is

execute immediate ' Delete from tbl_archive_trade_list
where deal_id in (
select deal_id from tbl_archive_trade_list where trade_id in (
select trade_id from ' || main_trade_group_table || ' where tradegroup_id in (
select tradegroup_id from ' || main_trade_group_table || ' a , tbl_archive_trade_list b

[Code]...

I want to run this Query in While loop till the above command return 0 records.

I tried giving the above statement inside WHILE loop but it is failing.

Without the WHILE loop the above statement works fine and executed properly.

View 5 Replies View Related

SQL & PL/SQL :: Query Inside For Loop

Nov 3, 2011

i am trying to do something the following .. but I can't get the syntax correctly for the select statement inside the secondary_loop ...

EmailBodyHTML := '';
main_loop := '';
secondary_loop := '';

[Code]....

View 4 Replies View Related

View Inside The Procedure

Jun 12, 2013

I am having a view say name vw_mytable , i need to call this view inside the stored procedure it is saying as Grant execute on

usera.vw_mytable to userb;ORA-02204: ALTER, INDEX and EXECUTE not allowed for views

how do i create a store procedure to call a view from another user.Usera is having a view and i need to create procedure in userb and call usera.vw_table.

View 1 Replies View Related

SQL & PL/SQL :: How To Use NOT NULL Inside DECODE

Jun 28, 2007

DECODE(FIELD_1,NOT NULL,'working',NULL)

Is it possible to do such? else how to proceed so?

View 9 Replies View Related

SQL & PL/SQL :: Using Variable Inside A View

Mar 24, 2013

i want to put a variable inside a view. create or replace view loading as

SELECT H.ORGANIZATION_ID ORG_ID,H.HEADER_ID,H.REQUEST_NUMBER, H.DATE_REQUIRED ISSUE_DATE,H.ATTRIBUTE1,
H.ORGANIZATION_ID,L.INVENTORY_ITEM_ID,SS.SALES_ID,S.SECONDARY_INVENTORY_NAME SUB_INV_CODE,S.DESCRIPTION SALESPERSON,I.SEGMENT1 ITEM_CODE,
[code]....

i want this condition to be like and to_date(H.DATE_REQUIRED,'dd-mm-rrrr') = to_date('Vdate','dd-mm-rrrr') i want Vdate as a variable parameter.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved