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
ADVERTISEMENT
Apr 24, 2013
I have a requirement to be coded like this:
A function to return pl/sql table(cant use ref cursor) whose columns varies every time it runs i.e,
means
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000))
type pl_tab is table of pl_tab_type
func f return pl_tab
as
...
end;
note : pl_tab_type will vary for each run of function f
i.e.,for example, pl_tab_type can be changed to as follows:
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000),col3 varchar2(1000))
how to return pl/sql table of dynamic type from func,
View 12 Replies
View Related
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
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
Feb 21, 2013
Does the Oracle 11g supports 'RETURNING INTO' clause in MERGE statement? if it was not available is there any alternate to achieve the same ?
View 3 Replies
View Related
Nov 8, 2010
I have been testing some functionalist, I have been trying to get the updated dept no values into an array and then print the count. But i am getting the following error.
I have implemented, the whole example is to know about 'dynamic sql returning clause into collection'
s@ORCL> select * from t_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 comp NEW YORK
20 Compt DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 Comp Ahaaa
80 data ab
80 data ab
80 data ab
80 data ab
9 rows selected.
s@ORCL> ed
Wrote file afiedt.buf
1 declare
2 type tp_dept is table of number;
3 arr_dept tp_dept;
4 begin
5 execute immediate q'['update t_dept set dname = 'Pointers' where deptno = 80 returning deptno into :out]'
6 returning into arr_dept;
7 dbms_output.put_line('The count is '||arr_dept.count);
8* end;
s@ORCL> /
returning into arr_dept;
*
ERROR at line 6:
ORA-06550: line 6, column 19:
PLS-00597: expression 'ARR_DEPT' in the INTO list is of wrong type
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored
View 4 Replies
View Related
Sep 25, 2012
I have created a amortization SELECT statement that amortizes loans based upon a table of loan information. The problem is this: when I run an ITERATION on numbers, the select statement returns a value but the ITERATION seems to be reading zero. This leads to a bunch of zeroes in the resultant table.
DROP TABLE MORTGAGE_FACTS
CREATE TABLE mortgage_facts (
C_USER1 CHAR(15),
C_USER9 NUMBER,
C_USER11 NUMBER,
C_USER10 NUMBER,
C_B1 NUMBER(19,2),
C_B2 NUMBER(19,2),
C_BUDGET NUMBER(19,2),
C_USER12 NUMBER(19,2));
INSERT INTO mortgage_facts VALUES ('Jones', 131828.81, 3.348, 72, 0, 0, 0, 0)
--BEGIN AMORTIZATION
SELECT
P,
C_USER1,
C_USER9,
C_USER11,
C_USER10,
C_B1,
C_B2,
[code]........
The first two results look like this:
P C_USER1 C_USER9 C_USER11 C_USER10 C_B1 C_B2 C_BUDGET C_USER12
0 Jones 131828.81 3.348 72 0 0 131828.81 2023.55
0 Jones 131828.81 3.348 1 367.8 -367.8 132196.61 2023.55
As you can see, in column C-B2, despite referencing the column C_USER12 which should have 2023.55 as a value in it, it is returning the mirror opposite of C_B1 which leads me to believe that the system is reading a ZERO in the column despite displaying 2023.55.
View 6 Replies
View Related
Jul 25, 2013
Below is the block which i am trying to test in scott schema. I dont want to substute IN clause values directly. So i have written cursor and have added in separate variable separeated by comma.But its not working.
declares varchar2(1000);s1 varchar2(1000);v number := 0;v1 varchar2(2000) := 'SCOTT';j number := 0;cursor hhis select ename from emp;beginselect count(*) into v from emp; for i in hh loops := s||''''||i.ename||''''; j := j+1;if j <> vthen s := s||',';end if;s1 := s1||s;s := null; end loop;dbms_output.put_line(S1); case when v1 in (s1) then dbms_output.put_line('Y'); else dbms_output.put_line('N'); end case;end;
View 3 Replies
View Related
Aug 31, 2012
I'm trying to use SYSTDATE in a WHERE clause of nested SELECTS..I want to select a range of info from two days back from today until today (or time it is being run). But when I run this, it says I have a missing expression...
SELECT XXXX
FROM XXXX
WHERE DATE BETWEEN TO_DATE(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') -2)
AND TO_DATE(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS'))
View 7 Replies
View Related
Nov 20, 2010
how to write a function that returns top value if not exists then next top for combination of customer_id and hierarchy.For instance :
If I've got table
customer_id ,hierarchy, function_code
123 |1 | Z1
123 |2 |67
123 |3 |5B
678 |10 |S2
678 |11 |Z2
345 |2 |11
For the customer ID 123 I want to return Z1, for customer 678 I want to return S2 and for customer ID 345 I want 11
Problem is that I'm new to the concept of looping. I know how to write a function that accepts customer_id as a value write a cursor and then check IF hierarchy = 1 the return FUNCTION_CODE IF hierarchy - 2 THEN ...
but I need something more universal as some of the customers may have hierarchy function 1 and that would be the top one for him but others might have function of hierarchy 10 as top and checking all of the possibilities using if would be just stupid. So how to write something universal ? And of course if function did not find any customer_id then return null.
View 9 Replies
View Related
Feb 5, 2012
I have the following function that I am using as a template for any function that executes a select statement and return a single value as an output.
The function is working but I wanted to take an expert opinion if it can be optimized.
CREATE OR REPLACE FUNCTION AFESD.F_AGR_GET_AGREEMENT_SERIAL
(I_NUMBER0 IN NUMBER, S_SUB_NUMBER VARCHAR2 DEFAULT NULL, I_TYPE_ID NUMBER)
[Code]....
In addition I want to use the parameter S_SUB_NUMBER that can be NULL and add it to the select statement of the cursor, but I dont know how to do that in one statement.
CURSOR C_AGREEMENT
IS
SELECT AGREEMENT_SERIAL
FROM VW_AGR_AGREEMENT
WHERE NUMBER0 = I_NUMBER0
AND TYPE_ID = I_TYPE_ID
-->and sub_number is null;
-->and sumb_number = s_sub_number
View 11 Replies
View Related
Apr 5, 2011
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:
cmd.Parameters.add ("parRefCursor", dbresult.RefCursor, direction.Result);
I'm being able to use it as a OUT parameter, but I would like to have it as a RETURN value from a Function.
View 4 Replies
View Related
Jul 14, 2010
I'm trying to debug this function to get the desired results. See attachment for the function code and the test data insert script.
----Create Test Table
CREATE TABLE VC_WORKINGDAYS
(
WK_ID number NUMBER(10,0),
WK_DATE DATE,
);
-- Insert test Data
INSERT INTO VC_WORKINGDAYS_1 VALUES (308, '25-MAR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (316, '06-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (324, '18-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (332, '03-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (340, '13-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (348, '25-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (356, '06-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (364, '16-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (372, '28-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (380, '08-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (388, '20-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (396, '01-AUG-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (404, '11-AUG-11');
SQL
----- Result Should be WHY
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),1)) FROM DUAL 14/JUL/10 15/JUL/10 is 1 working day from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),2)) FROM DUAL 14/JUL/10 16/JUL/10 is 2 working days from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),3)) FROM DUAL 14/JUL/10 19/JUL/10 is 3 working days from today
Attached File(s)
create_Function.zip ( 6.39K )
Number of downloads: 1
InsertData.txt ( 84.34K )
Number of downloads: 0
View 1 Replies
View Related
Jul 20, 2011
I am trying to run a dynamic select statement form a function and return the result into a variable, everything goes fine but the return is always null!
CREATE TABLE AFESD.MAJOR_ACCOUNT
(
NUMBER0 NUMBER(2) NOT NULL,
SHORT_NAME CHAR(35 BYTE) NOT NULL,
FULL_NAME CHAR(50 BYTE)
)
--Actually any table can do
CREATE OR REPLACE FUNCTION F_GEN_SELECT_INT
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2, S_COLUMNS IN VARCHAR2)
RETURN NUMBER
AS
I_RETURN NUMBER;
S_SQL VARCHAR2(300);
--S_DB_ERROR VARCHAR2(100);
[code]....
B.S. I didnt delete the commented lines to have your review comments.
View 21 Replies
View Related
Jan 22, 2011
I have a problem when trying to create a PLSQL function based on an XML extraction query.
I have three dummy tables:
SQL> get create_address
1 create table ADDRESS
2 (
3 id NUMBER not null,
4 house_number NUMBER,
5 house_name VARCHAR2(20),
6 street_name VARCHAR2(30),
[code]....
And the following dummy data for these:
1 insert all
2 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
3 values (1, 1, '', 'Tube Street', 'Norwich', 'Norfolk', 'NF12', '2DF')
4 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
5 values (2, 5, '', 'Dave Street', 'Edlington', 'Kent', 'CT34', '8GH')
6 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
[code]....
So far so good then. But, what I want to create is a function where I can pass in an id value and return the corresponding XML CLOB.
So I try, very simply, this:
SQL> get get_xml_data
1 create or replace function get_xml_data(p_id in number) return clob is
2 Result clob;
3 begin
4 select xmlroot(xmlelement("HomeData",
[code]....
And, alas, i'm greeted by this:
SQL> /
Warning: Function created with compilation errors.
SQL> sho err
Errors for FUNCTION GET_XML_DATA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5 PL/SQL: SQL Statement ignored
24/63 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -
SQL>
I've tried to redo the query in several different ways but so far nothing.
View 2 Replies
View Related
Sep 10, 2012
I am using the below function to return a blob (CSV) content, this is working fine..But i need to modify this function to display headline for the report and fixed width column in CSV ( then user no need to expand the each and every column ).
create or replace function GET_BLOB (
p_query varchar2
) return blob as
l_cursor integer;
l_cursor_status integer;
l_col_count number;
l_desc_tbl sys.dbms_sql.desc_tab2;
l_col_val varchar2(32767);
[code]....
View 10 Replies
View Related
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
Nov 8, 2011
I have question.Using procedure inside the function ?can I get better performance?
View 8 Replies
View Related
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
View Related
Mar 12, 2013
Application Express 4.1.1.00.23 ( plus all earlier versions that I've ever used)
When using the wizard to create a Validation of type "PLSQL Function returning Boolean", why is it mandatory to enter a value in the text field "Error Message" on the screen that follows? This message is never used as the message actually displayed comes from a PLSQL return statement.
View 5 Replies
View Related
Nov 20, 2012
I have this procedure
POSTCODE_TO_LAT_LNG_GM_API(postcode IN VARCHAR2, lat OUT NUMBER, p_long OUT NUMBER)
to convert a postcode into lat/long values. I then need to add them to the returned SQL statement so I used the string concat operator || with to_char but it comes up with this error when I try to apply the changes: Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic column'' checkbox below the region source to proceed without parsing.
ORA-00936: missing expressionh4.
h4. btw I'm using Oracle 11g release 11.2.0.3.0 and Apex version 4.1.1.00.23
DECLARE
l_lat NUMBER;
l_lng NUMBER;
l_SDO_GEOMETRY SDO_GEOMETRY;
[code]...
View 9 Replies
View Related
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
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
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
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
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
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
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
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
Mar 7, 2012
Can we use NVL Function in a from clause?
SELECT t1, t2, nvl(t3,0) t3 FROM
(select nvl(t1,0), nvl(t2,0),nvl(t3,0) from table1).........
can we NVL here?
View 4 Replies
View Related
