PL/SQL :: Create Record Variable That Refers Dynamic Query Assigned To Ref Cursor?

Nov 11, 2012

Just explaining what I am trying to achieve:

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

[Code].....

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Cursor With Bind Variable And Cursor Record

Feb 25, 2011

Is it possible to:

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

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Substitution Variable With And Without Value Assigned

Jun 14, 2011

I have a table emp with columns [id,name,job_id,dept_id]. now I make a simple select query.

select id,name,job_id,dept_id
from emp
where id = &a
and name = &b
and job_id =&c
and dept_id =&d;

now in this case it will ask the values for all the columns and will according print result.

Now my questions is what will happen if I just pass the value for only id and name not for other two...?

View 35 Replies View Related

SQL & PL/SQL :: Bind Variable For Dynamic Query In Procedure

May 14, 2013

I have to use bind variable for dynamic sql in a procedure. Is there a way to have control on these values. Say for example:

Procedur MyProc
(
In_EmpID Number default null,
In_EmpName Varchar2 default null,
in_JoinDate Date default null
[code]....

I have more than 5 In parameters, all 5 is not compulsory by default they are null and sql formation is also dynamic with in the procedure.I need to map bind variable to a proper one.. Is there a way to handle bind variable.

View 2 Replies View Related

SQL & PL/SQL :: Define A Cursor Using Dynamic Query?

Aug 29, 2010

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

View 5 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

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

SQL & PL/SQL :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies View Related

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.

Explanation:

In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.

I need to query on this dynamic table and return the result as return result. But i am not able to do it.

Here T_web_loylty_report_table is a type.

CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......

View 2 Replies View Related

PL/SQL :: Dynamic Ref Cursor With Dynamic Fetch

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

SQL & PL/SQL :: Dynamic Variable Based On Another Field?

Apr 26, 2010

In the query below, I'm attempting to replace task codes with task descriptions by left joining to a multi-purpose control table.

In our corp, the description for the task code varies based on the value of change_program in each record, so rather than referencing the value of code_index.tabl statically as I do in the query below (201), I need a dyanamic variable to be defined for each record based on the value of work.change_program, which would represent the value for code_index.tabl.

For each record
if change_program =1 then v_tabl = 201
elseif change_program =2 then v_tabl = 202
elseif change_program =3 then v_tabl = 203
else v_tabl = 201

how to declare and use variables.

SELECT account,
change_program,
task_code_01,
task_code_02,
task1.longdesc,
task2.longdesc
FROM work

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Want To Define Dynamic Type For A Variable In Oracle 10g

Jul 17, 2013

I m trying to give table name as parameter to this function. It is compiling properly.But when an anonymous block is created to call this table value, it has to be fetched into a variable of Rowtype of this inputted table. So I am not able to create any ROWTYPE variable for this table dynamically.

//Function
create or replace function instant_tabula(tabula in varchar) return sys_refcursor
as
cur sys_refcursor;
str varchar2(20000);
begin
str := 'select * from ' || tabula;
open cur for str;
return cur;
end;
[code]....

View 7 Replies View Related

PL/SQL :: Dynamic Column In A Cursor

Sep 1, 2012

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

V_SQL := 'REPLACE(V_DATA,'TEST',CUR_DATA.'||V_FIELD||');';
EXECUTE IMMEDIATE (V_SQL);

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.

View 6 Replies View Related

SQL & PL/SQL :: How To Pass In Dynamic Variable For Calling Store Procedure

Apr 9, 2012

I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.

For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".

DECLARE
bef_val CUST%ROWTYPE;
aft_val CUST_TEMP%ROWTYPE;
BEGIN
SELECT * INTO bef_val FROM CUST WHERE name = 'ABC';
SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC';
[code]....

For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?

View 8 Replies View Related

SQL & PL/SQL :: Passing The Char Variable In Cursor

Apr 9, 2013

i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is taking

here is the detailed program ... why this is not taking and tell me how to pass the values through it..

declare
v_name char(6) ;
cursor c1(c_name char) is
select name, parent,child,status from relation
start with name='%'
connect by prior parent=child
union

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: Cursor And Variable - Procedure Will Not Compile?

May 20, 2013

I am running this procedure but it will not compile. I get the error "PLS-00356: 'REC.XX' must name a table to which the user has access"

All of the query results from the cursor are correct.

create or replace procedure SWDCADMIN.Hard_Delete_Client( cltId IN number)
IS
cursor c1 IS
select
t1.table_name xx,
t1.owner || '.' || t1.TABLE_NAME uu,

[code]...

View 15 Replies View Related

SQL & PL/SQL :: Used Variable In Open Cursor Statement

Mar 1, 2012

open cp_cursor for 'Select curtailprogramkey from curtailProgram where curtailprogramid like 'l_rec.curtailprogramid%'';

NOTE: l_rec.curtailprogramid is varible. what is wrong in the above statement?

View 2 Replies View Related

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

View 5 Replies View Related

Forms :: Fetching Value From Dynamic Cursor?

Dec 13, 2011

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.

Code----------

Declare
C_YEAR number(38);
C_LOC VARCHAR2(200);
C_INVNO VARCHAR2(200);
C_INVDT DATE;
C_CTRT VARCHAR2(200);
C_GLCD VARCHAR2(200);

[code]....

View 2 Replies View Related

PL/SQL :: Strong Type Dynamic Cursor?

Aug 21, 2012

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.

View 12 Replies View Related

Application Express :: From Cursor Variable How To Get Into Page

Feb 15, 2013

i am creating an apex page where i have 2 regions. From the Top region stores all fields entered into the bottom region Text fields like first name and last name and address fields are in region 2(bottom).After region 2, i have a add person button.

Once i click add person, that person will get into top i.e region 1.

Now, Region 1 got person1 first name ,last name
person2, first name,last name
etc..

I am not able to display like p1_first_name,p1_laast_name as the list is not stable..it is growing and not showing the person who already got saved..I can retrieve them from DB using a cursor..But from cursor vairable how to get into page vairable..

View 1 Replies View Related

PL/SQL :: Open Ref Cursor Using Collection As Bind Variable

Feb 26, 2013

Is it possible to bind collection while opening a ref cursor. Find below the code that I am trying. My goal is to open cursor once using collection variable. Can it be done using DBMS_SQL ?

DECLARE
TYPE typ_emp_rec_in IS RECORD
(
deptno NUMBER,
sal NUMBER
[code]......

View 4 Replies View Related

Precompilers, OCI & OCCI :: How To Use Cursor With Dynamic SQL In ProC

May 24, 2005

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)

[Code].....

View 4 Replies View Related

SQL & PL/SQL :: How To Skip Some Records In Open Dynamic Cursor

Jan 3, 2011

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

[Code]...

View 7 Replies View Related

Forms ::create History Record For Each Record Whether Updated Or Not

Sep 13, 2011

I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.

No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.

Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.

View 4 Replies View Related

Forms :: Dynamic Variable In Place Of Username In Each Select Statement Throughout Application

Jul 25, 2010

I have a problem that i have hard coded the username.tablename in each select statement of all forms of my application. Now i want to use a dynamic variable in place of username in each select statement throughout the application. The example is:

select * from scott.emp
and i want to write it as:
select * from variable.emp

But at compilation of the form the compiler should know the above variable name.

I have tried to use following select statement but it does not work.

select user into :global.username from user_users

I think perhaps my problem would be solved with Dynamic SQL Statement but i have no experience by using this statement.

View 4 Replies View Related

SQL & PL/SQL :: Error In Reading Data From Collection Variable For Ref Cursor

Jun 19, 2013

While reading data from collection variable using ref cursor . I am getting the below two errors.

PLS-00382:Expression is of wrong type
ORA-22905 Cannot access rows from a non-nested table item.

CREATE OR REPLACE PACKAGE APPS_GLOBAL.GIIOMEGAORDERLIST
AS
TYPE BU_LIST_TYPE IS TABLE OF VARCHAR(50);
TYPE OFFER_DETAIL IS RECORD
(
GII_BU VARCHAR(50),
GII_OFFER NUMBER,
[code]........

View 4 Replies View Related

SQL & PL/SQL :: Dynamic Cursor / Loop Will Accept Table Name From Parameter

Oct 11, 2013

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.

DECLARE
--initialize variables here
v_date varchar2(10);
v_rebuild_index varchar2(250);
v_sql VARCHAR2(250);
p_table_name varchar2(250) := 'DS_ABSENCE';
p_source_table varchar2(30) := 'STG_ABSENCE';
p_source_owner varchar2(30) := 'STG_SAP';
v_for_sql varchar2(1000);
[code]....

View 7 Replies View Related

PL/SQL :: PLS-00455 / Cursor CUR_1 Cannot Be Used In Dynamic SQL OPEN Statement

May 10, 2013

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;

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Update Of Cursor Records When Table Gets Updated Immediately

Apr 16, 2010

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

Table1

Col1prodvalue1rank
1A21
2A62
3A53
4B61
5B32
6B73
7C41
8C22
9C13
10C74

Output

Col1prodvalue1rank
1A51
2A52
3A33
4B101
5B02
6B63
7C51
8C52
9C03
10C44

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.

View 9 Replies View Related

Forms :: Dynamic Record Group For LOV?

Feb 22, 2011

I want to create a Dynamic record group and associate it to a LOV.for example: I want that for a set of people A, i want to fire the query in lov:

select * from emp where (condition 1)
and for another set of people B, I want to fire
select * from emp where (condition 2)...

How could I achieve it?

View 4 Replies View Related







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