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
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.
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.
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').
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].......
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:
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.
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.
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.
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.
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?
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'''
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.
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)
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]....
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
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.
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)
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),
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.
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.
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
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.
