SQL & PL/SQL :: Returning Cursor For Dynamic Database
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].......
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'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.
The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?
-- Incorrect indicative example 1.
DECLARE v_cur SYS_REFCURSOR; BEGIN UPDATE table1(f1, f2) SET ('v1', 'v2') WHERE f3 = 'v3' RETURNING <updated_rows> INTO v_cur END;
-- Incorrect indicative example 2.
DECLARE v_cur SYS_REFCURSOR; BEGIN OPEN v_cur FOR UPDATE table1(f1, f2) SET ('v1', 'v2') WHERE f3 = 'v3' END;
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:
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 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.
So, for lack of a better way to explain it, I need to do the following.
I need a EXECUTE IMMEDIATE to be able to use a CURSOR but its saying its not declared. Is their a way to pass the cursor so a Execute Immediate can use it...
I know thats just a small part... but it shows the end goal. I need to append a VARCHAR that has the COLUMN NAME I need from the CURSOR. CUR_DATA is the open cursor. V_DATA is just a VARCHAR with text in it.
Is it possible to define a cursor using dynamic sql. Reason being is, I want to fetch data from 4 diffrent tables but dont want 4 diffrent cursors for this purpose.
I have a procedure that takes an in parameter . Cursor is declared in this procedure. Again is there a way to use dynamic sql so that this cursor declared in procedure uses all 4 table one at a time i.e cursor c1 is select * from table_name(I want this table name to be updated every time with new table name).
I have used a dynamic cursor for fetching value from different tables hence table name assigned dynamically during run time but i face an error ORA-06562 type of argument must match type of column and bind variable with error ORA-06212 But i frequently check the table structure and Declared variable there is no mismatch between them.
I'm currently using Oracle 11.2G and I was wondering if you can declare a dynamic cursor as a strong ref cursor type or can it only be declared as weak type ref cursor? Now, if it can only be declared as a weak type ref cursor, is there any way to avoid having to explicitly declare the type and field names being returned by the cursor? Meaning, is there anyway to avoid have to do something like this...
TYPE example_rec IS RECORD ( col1 VARCHAR2, col2 NUMBER, col3 NUMBER, etc... )
Instead, I was wondering if I could just set the fields and their types by setting them as a TABLE OF the ref cursor? The reason I'm wondering if this is possible is because I have a lot of fields being returned in my cursor, and I was hoping on avoiding having to type each one out and its type.
I am using dynamic Pl/SQL with ProC and having problems with cursor. I've to execute stored procedure dynamically and get the result of select list into the cursor. The EXECUTE command is working fine but the FETCH gets failed with error "ORA-01002: fetch out of sequence". I've read that cursor variable can not be used with dynamic SQL.
Stored Procedure: ------------------ ROCEDURE open_mod_cur ( curs IN OUT cur_type, module_id IN varchar2)
After opening a dynamic cursor, usually fetch hit record into some variables. However, if I do not want to "FETCH INTO " operate Just only skip this record.
DECLARE TYPE weak_cur_type IS REF CURSOR; weak_cur_1 weak_cur_type; weak_cur_2 weak_cur_type; vs_dsql VARCHAR2(2048); vd_create_time DATE; vn_count NUMBER(8); vn_total_amount NUMBER(13);
I have a plsql block construct where i want to use for loop dynamically , the query which for cursor for for loop will accept the table name from parameter and join them to return the result. the resultant data will iterate in loop and do the execution.
create or replace package cognos_pk as /* Creates Package Header*/ TYPE project_type IS record( /* A record declaration is used to */ c1 NUMBER /* provide a definition of a record */ ); /* that can be used by other variables*/ TYPE project_type1 IS REF CURSOR return project_type; /* Variable declaration */ procedure conosg_sp (result1 out project_type1); /* SP declaration */ end;
I am having a table with 4 columns as mentioned below
For a particular prod the value greater less than 5 should be rounded to 5 and value greater than 5 should be rounded to 10. And the rounded quantity should be adjusted with in a product starting with order by of rank with in a prod else leave it
I have taken all the records in to a cursor. Once after rounding the request of 1st rank and adjusting the values of next rank is done. Trying to round the value for 2nd rank as done for 1st rank. Its not taking the recently updated value(i,e adjusted value in rounding of 1st rank).
This is because of using a cursor having a value which is of old value. Is there any way to handle such scenario's where cursor records gets dynamically updated when a table record is updated.
1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)
2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012
3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)
4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.
5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point
6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.
PS:
1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.
2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)
Code : =================================================================================================== create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2) is
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
I am using SQL Developer.I am self-teaching myself PL/SQL. What I am trying accomplish is to run a select query across many database links at runtime and to insert that query onto a local table. So far, I have only gotten as far as querying the database link names. I am stuck at where my variable calls the database link name. It does not recognize the database link name, and I can't quite grasp the reason why. Below is the first part of my script which does retrieves the column values no problem:
If I break the script down to the bare select query, it will query absolutely fine.If I run the script in its entirety, it will not pick up the variable dbl as a dynamic database link. 02019. 00000 - "connection description for remote database not found"
If I comment out line 031 and prevent the looping of querying of database links (which will only fetch the first value of db_link from dba_db_links), the script will complete and I will have a row inserted into my local table.
I am suspecting it's the looping that is incorrect but I understand it is not going to do anything beyond line 059.
i'm working in an Oracle 10g database on an IBM AIX server.
I have 3 tables (tables A, B and C).
Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
I also have a Master table which is used to store the data from Tables A, B and C via the insert statement.
I'm trying to create a dynamic SQL insert statement using a shell script to insert data from the columns in Tables A, B and C into my Master table. Master table does contains all columns from Tables A, B and C, although a column name could be spelled differently. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".
I build the dynamic query using a for loop in my shell script (see below).
The problem is that i can't get the correct columns in the Master table in the dynamic SQL for the insert because depending on the table i'm selection from, the columns are different. So how do i get the correct columns in the SQL for the Master table?
Example Shell Script
--Archive_Rates.txt contains: Table A, Table B, Table C (but the next time my process runs, Archive_Rates might contain Table D, Table E and Table F -- each which have different column...but all columns are still in the Master table)
for tbl in `more Archive_Rates.txt` do echo 'BEGIN WORK; ' > rc1.sql echo ' ' >> rc1.sql echo 'insert into Master' >> rc1.sql echo '(prod, rate, rate_exp) ' >> rc1.sql
I have the following statement dynamic sql statement in a database package.
begin EXECUTE IMMEDIATE 'SELECT REPLACE(SUBSTR('||V_WHERE_CLAUSE||',1,3, ''AND'', NULL) INTO V_WHERE_FILTER FROM DUAL'; EXCEPTION NULL; end;
When executed it gives following error.
ORA-20000: ORA-20000: ORA-00936: missing expression ### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 931 ### END TRACE ### ### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 935 ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 164 ### END TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 184 ORA-06512: at line 9
I have a select box with products, and a dynamic action that updates a display only element with the price, based on the product selection.After the selection is made and the page is submitted, I have noticed that the price is not stored in the database. If I change the display only value to a text box, the data is saved. Is this expected behavior? If so, can I add something to the text box to make it not editable?
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS TYPE emp_cur_type IS REF CURSOR; PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER); END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and sid = 7 and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 4 opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 11 <----- 7+ opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors) opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 53 <----- 17+ opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
-define a cursor with bind variables -get a cursor record from these cursor -and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000; DECLARE --works fine CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3; --doesn't work --CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1; crec c1%rowtype; BEGIN --works fine OPEN c1; --isn't possible ? --OPEN c1 USING 3;
