SQL & PL/SQL :: Dynamic SQL Returning Clause Into Collection
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
ADVERTISEMENT
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
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
Feb 20, 2010
I want to have a dynamic collection as a parameter to procedure.This collection should access dynamic column set.
while calling this proc user may not aware of what column set is going to be sent to this proc.
View 8 Replies
View Related
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
Jul 20, 2011
I am creating a db function to select * from table_name to be used by the application for retrieving lookup data. I found many examples for how to return cursor as a result of executing a dynamic sql but don't know the optimum way.
following is my current trial:
CREATE OR REPLACE PACKAGE types AS
TYPE Cursortype IS REF CURSOR;
END;
/
CREATE OR REPLACE FUNCTION F_GEN_SELECT_CURS
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2)
/*This function maps S_ACC_MAJOR_ACCOUNT_LIST CoolGen service
[code].......
View 5 Replies
View Related
Nov 8, 2011
I'm trying to build a dynamic sql inside an Oracle procedure, and return it as a SYS_REFCURSOR to ADO.NET using ODP.NET in Oracle 11R2. Below is my table:
CREATE TABLE "TFWDB_ENTIDADE"
(
"CD_ENTIDADE" NUMBER(10,0) NOT NULL,
"DC_ENTIDADE" VARCHAR2(100 BYTE) NOT NULL,
"NM_ENTIDADE" VARCHAR2(255 BYTE) NOT NULL,
"CD_TIPO" NUMBER(5,0) NOT NULL,
"ID_STATUS" CHAR(1 BYTE) DEFAULT ('1') NOT NULL,
CONSTRAINT "PKFWDB_ENTIDADE" PRIMARY KEY ("CD_ENTIDADE"),
CONSTRAINT "CHFWDB_ENTIDADE" CHECK (ID_STATUS IN ('0', '1'))
);
Below is the procedure I build to SELECT it:
create or replace procedure spfwdb_sl_entidade_1(
p_cd_entidade in number,
p_dc_entidade in varchar2,
p_nm_entidade in varchar2,
p_cd_tipo in number,
p_id_status in char,
[code]........
I'm calling this code from C#, and I get an empty Result Set. What am I doing wrong? Can I output the dynamic sys_refcursor that I created? Below is my C# code using ODP.NET:
string pConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASERVER)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORASID)));User Id=user;Password=password;";
OracleConnection oConn = new OracleConnection(pConnectionString);
oConn.Open();
string pNomeProc = "spfwdb_sl_entidade_1";
[code]........
View 7 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
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
Jul 12, 2013
How can I create PLSQL table type dynamically?
I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.
View 2 Replies
View Related
Aug 18, 2011
I have to create a WHERE clause like :
SELECT * FROM wc_claims WHERE part_nbr in l_part_nbr ;
I have three tables:
table A has - user_id, supplier
table B has - supplier, part_nbr
table C , wc_claims, has part_nbr and 78 other columns.
what i need to do is when user logs in with his user_id, he should see only that info from table C, corresponding to his supplier, and the part_nbr.
SELECT supplier from table_A WHERE user_id = 'abc' ;
supp_abc
SELECT distinct part_nbr from table_B WHERE supplier = 'supp_abc' ;
partA, partB, partC,........partZZ
SELECT * FROM wc_claims WHERE part_nbr in (partA, partB, partC,........partZZ );
I have created a function :
create or replace Function wcl_partnbr ( p_supp IN varchar2 )
RETURN CLOB
IS
[Code].....
so that I can use that in the WHERE clause, because one supplier can have thousands of parts...so it works fine, if there are 200 part numbers for one supplier, but above that.. it doesnt.
View 15 Replies
View Related
Jul 9, 2010
I having below table :
EXAM2@orcl> desc search_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROL NOT NULL VARCHAR2(7)
CNM NOT NULL VARCHAR2(30)
FNM NOT NULL VARCHAR2(30)
MNM NOT NULL VARCHAR2(30)
DOB NOT NULL DATE
YEAR NOT NULL NUMBER(4)
EXAM NOT NULL VARCHAR2(1)
MAIN_SUPP NOT NULL VARCHAR2(1)
In a dotnet application user is going to input/select all/any of the above column in where clause.If user has given value for rol column then ref cursor should be :
select * from search_table where rol like <given value>
endif
and/or
If user has given value for cnm column then ref cursor should be :
select * from search_table where rol like <given value> and/or cnm like <given value>
endif
I mean whatever value is being given; it should be part of where clause.For it i am going to :
EXAM2@orcl> CREATE OR REPLACE PACKAGE rollsearch AS
2 TYPE t_cursor IS REF CURSOR;
3 Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
4 c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
5 io_cursor IN OUT t_cursor);
6 END rollsearch;
7 /
EXAM2@orcl> CREATE OR REPLACE PACKAGE BODY rollsearch AS
2 Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
3 c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
4 io_cursor IN OUT t_cursor)
5 IS
6 v_cursor t_cursor;
7 sql varchar2(1000);
8 BEGIN
9
What should i write further; so that i may get ref cursor as whereed clause for given value. So that i may populate my datagrid view for that web form.
View 14 Replies
View Related
Jul 1, 2013
I need to build a SQL statement dynamically such that the WHERE clause looks/works as follows: WHERE x LIKE '%1%' OR x LIKE '%2%' OR x LIKE '%3%' .
View 2 Replies
View Related
Mar 1, 2013
I have code as below
Scenario1
FOR i in (Select .................... from table1 Where id in(select id from Sample_table where status='Submit'))
Loop
.
.
.
End loop;
Scenario2
FOR i in (Select .................... from table1 Where id in(select id from Sample_table where status='Saved'))
Loop
.
.
.
End loop;
Scenario3
FOR i in (Select .................... from table1 Where id in(select id from Sample_table where status='Inprogress'))
Loop
.
.
.
End loop;Query is same here but only chage is where clause
Total length of this query is exceeding varchar2 length. so i cant use variable to hold this query and append where clause dynamically.
Do we have any other method to change where clause dynamically?
View 6 Replies
View Related
Nov 23, 2011
I have a dynamic query which has this clause in it: WHERE [COLUMN NAME] IN (' || theString || ')
My problem is that theString is being passed in through a C# call and the variable is a bunch of strings concatenated together and separated by a comma. Ex: theString = "'val1','val2'"
How many quotes are supposed to go around val1 and val2?
I've tried the following and none work:
'val1','val2'
''val1','val2''
''val1'',''val2''
'''val1'',''val2'''
''''val1'',''val2''''
When I run the procedure in Oracle it works with '''val1'',''val2'''
View 1 Replies
View Related
Dec 5, 2012
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
View 1 Replies
View Related
Sep 20, 2012
There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.
declare
type type_name is table of varchar2(10);
type type_name1 is table of type_name;
names type_name1:=type_name1(type_name('hello'));
begin
-----HOW TO PRINT A VALUE--------
-----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT
null;
end;
1) need to print the values of names(1)
2)Assign a value to names(2)
View 3 Replies
View Related
Mar 22, 2013
I am describing a SQL statement to get it's column list:DECLARE
cur NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;
[Code]....
Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?
View 4 Replies
View Related
Apr 8, 2013
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3
ORA-00932: inconsistent datatypes: expected NUMBER got CURSER
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at "TW.PRINT_REF_CURSOR", line 28
ORA-06512: at line 9
Here is my code:
set serveroutput on
exec DBMS_OUTPUT.ENABLE(1000000);
declare
l_cursor sys_refcursor;
begin
[code]....
Is there a solution or bypass?
View 3 Replies
View Related
Apr 23, 2010
can we use something like this
"select ... order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
View 6 Replies
View Related
Jun 30, 2010
What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?
View 1 Replies
View Related
Sep 29, 2010
I have been doing some code in collection for testing. I have been doing the below but getting the error.
SQL> desc t_mine;
Name Null? Type
----------------------------------------------------- --------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SQL> ed
Wrote file afiedt.buf
1 declare
2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME;
3 v_elapsed PLS_INTEGER;
4 type allobjects_record is record
5 (owner varchar2(1000)
[code].......
ERROR at line 15:
ORA-06550: line 15, column 15:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 15, column 3:
PL/SQL: SQL Statement ignored
I followed the below link
[URL].......
View 5 Replies
View Related
Jan 2, 2012
In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.
Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.
View 9 Replies
View Related
Mar 22, 2012
create table testing
( id number (10),
key number (10)
)
insert into testing values (1,10)
insert into testing values (1,10)
insert into testing values (2,10)
insert into testing values (2,20)
insert into testing values (3,10)
My requirement is to return
id key
1 10
1 10
because both their id and key are same
View 2 Replies
View Related
Jan 24, 2013
I have the below requirement,
i got a table(deptid, deptname, address,city,zip, state, other columns) i want to write a query to determine any error(records with different values) because i expect all records grouped-by(deptid, deptname, address,city,zip, state) to have the same deptid(pls note that deptid isnt unique),
or a specific deptid should have only one record based on the grouping (deptid, deptname, address,city,zip, state),
View 2 Replies
View Related
Jun 5, 2013
I have a table which lists previous jobs, whcih has monthto,yearto, monthfrom and year from fields. I am trying to find the most recent job (of which they might be multiple ending on the same month) Initially, i did the following:
ran a sub query to find the most recent (employee id, max (yearto * 12 + monthto), then another query where I got a list of the most recent jobs, and returned the first one using rownum = 1 (note this may be inconsistent between runs as thee rownum is calculcated before nay ordering clause. This worked, but i figured using the first function might be better as it gives an order
select employee_id from
(select employee_id
,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest
from employees
group by employee_id;
but this seems to return more than one entrty:
table date below:
drop table employee_list;
drop table employee_historic;
[code]...
What is the best way to return a single row, if the primary key is not available / applicable.
View 9 Replies
View Related
Jul 11, 2012
Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.
View 7 Replies
View Related
Jun 15, 2010
What are the various collection types available in Oracle 9i.
Mention the differences between them.
View 4 Replies
View Related
Mar 24, 2011
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));
create or replace type testingtype_tab as table of testingtype_obj;
Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
View 4 Replies
View Related
Aug 8, 2012
The following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
[code].....
how to use collection variables as OUT parameters in procedure.
View 1 Replies
View Related