Forms :: Dynamically Passing The Columns Values?

Aug 5, 2013

I am trying a pl/sql block which which take a string and execute it dynamically. Suppose below is string

M_COL := Q'[(P_CODE=> ':DEPTNO',P_CODE_TYPE => 'STATE')]';

Now trying to execute it. using below

M_STR := 'SELECT CHK_DEPT' || M_COL || ' FROM EMP WHERE EMPNO=''7499''';
EXECUTE IMMEDIATE M_STR
INTO M_DATE;

Now what i want is M_STR vairable to executed as

SELECT CHK_DEPT(P_CODE=> DEPTNO,P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'

instead of

SELECT CHK_DEPT(P_CODE=> 'DEPTNO',P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'

other in other way in parameter P_CODE Column value of DEPTNO should be passed.Also note that DEPTNO column in string M_COL is Dynamic.i.e

M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]';

View 9 Replies


ADVERTISEMENT

PL/SQL :: Dynamically Assign Values To Different ROWTYPE-Columns By Ruletable

Jul 31, 2013

how to assign values from a "rule table" to a rowtype-variable.  The ruletable contains values for different columns in different tables.Now i need to assign those given values for given columns out of that rule table to the equivalent column in a rowtype-variable. 

    CREATE TABLE TBRULES
        (
            TABLE     VARCHAR2(50 BYTE)
        ,    COLUMN    VARCHAR2(50 BYTE)
        ,    VALUE     VARCHAR2(200 BYTE)
        );
  [code].....

View 15 Replies View Related

Forms :: Dynamically Passing Where Condition

Jul 4, 2011

I am working on 6i reports.I have below scenario to achieve.

I have FIND window and MAIN Window.

FIND Window: contains EMPNO, ENAME,and FIND button.

MAIN WINDOW: EMPNO, ENAME, SAL, JOB

If i enter something in FIND window, by clicking FIND button, it will populate data in MAIN window.I have below query in WHEN BUTTON PRESSED TRIGGER.

declare
cursor c1 is select empno, ename, job, sal from emp
where empno=nvl(:FIND.empno,empno)
and ename=nvl(:FIND.ename,ename)

begin
--here i am populating data into MAIN window block
end;

It's working fine.

REQUIREMENT: i want to pass where statement dynamically. Let us say if i pass only empno in FIND window, i want to pass 'WHERE empno=:FIND.empno' to existing select statement [I will keep select statement same without where condition]

If i pass empno and ename both, i want to pass 'WHERE empno=:FIND.empno and ename=:FIND.ename.

How to pass where condition to existing select statement based on parameters we pass in FIND window.

View 8 Replies View Related

SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns

Feb 17, 2011

My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.

SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......

What will be the case when I need to concatenate for more number of values.

like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.

View 10 Replies View Related

Forms :: Passing Values Without Using Parameter Or Global

Jan 24, 2012

How to pass a values from one to another, but without using parameters or globals. I've used parameters and every time i have to list the values of the form that values have been passed to, it ask do you want to save... even if I didn't passed any values. It's because my code in where-new-form instance, where i give values to a parameters.

View 17 Replies View Related

Forms :: Selecting And Passing Values For A Query?

Sep 6, 2010

I have a form in which the user selects values above from a LOV and then selects a run query button which then uses the values selected in the LOV to refine the query and output the appropriate values.

What i have done in the pre-query trigger is: (the block i am querying is called cars where the block i am selecting values from the LOV is called selection)

:cars.make := :selection.make; (repeated for other fields)

this worked fine untill i introduced a from and to date in my form in which i passed the data through to the query like this:

declare
v_dates_between varchar2(1000);
begin
v_dates_between := 'reg_date between ''' || :selection.from_date || ''' and ''' || :selection.to_date;
Set_Block_Property( 'cars', DEFAULT_WHERE, v_dates_between ) ;

Now i have added the above bit of code the from and to date work fine, but when a car make is selected it still bring back every car make and not just the one selected.

is it possibly because i am setting the where clause in the block property? perhaps i need to now incoperate my other selections into this where clause? are the two blocks exclusive to eachother? and if i put the whole code into the block property I assume i will get problems. E.G. the user leaves a selection criteria blank (if they want to query all car models they wouldnt select one from the list) i assume the query would only return back values which contain no car model which would be 0 records.

View 6 Replies View Related

Forms :: Passing Check Box Values To WHERE Clause?

Oct 26, 2013

I have created a Data block - 'CONTACTS' (Database data block) and has database item - 'Code', 'Descr'

The number of records displayed is set to 5.

Value When checked - 'Y'
Value When Unchecked - 'N'
Check box mapping of other values - 'unchecked'

The requirement is when i check one or multiple checkboxes, i should pass the 'Code' item values to the WHERE clause.

Right now whenver i am trying to do so, only the current record value is copied to the WHERE clause.

I have tried using basic loop as well as while loop but things havmt worked. Below is a basic code which will work for one record, request to guide me with muliple checkbox ticked.

IF :contacts.cb = 'Y' THEN

IF p_where is null then

p_where := :contacts.code;
else
p_where := p_where ||','||:contacts.code;
end if;
end if;
p_where:= 'where code in ('||p_where||')';

View 6 Replies View Related

Forms :: Passing Parameters To Next Page But Values Not Saved

Jul 6, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have a form which has few textboxes, and few dropdown, and 3 links. Data is entered in few boxes, and then when a link is clicked, it goes to another page, but i want to pass the text in the text boxes, and the selected value from the dropdown also, to be passed as parameters to this link... But since these values are not yet saved, its not getting passed.. like it is something like this in the address bar add_new_event?&event_ id= &event_ name= & event_dt=

View 1 Replies View Related

SQL & PL/SQL :: Passing Rowtype Column Dynamically Not Working

Sep 6, 2010

I am passing the column name for a rowtype dynamically and it is considered as a string by oracle and not giving the value of rowtype.column.i have ANONY 1 where i am passing value to attribute1 and ANONY 2 i am passing value as NULL.

IF l_comp_column IS NOT NULL AND l_mand_column = 'Y'
THEN
dbms_output.put_line(l_comp_column ||' IS NOT NULL');

l_flag := 'Y';
ELSIF l_comp_column IS NOT NULL AND l_mand_column = 'N'
THEN
l_flag := 'N';
END IF;
[code]....

View 9 Replies View Related

Forms :: Copy Values Of Two Columns Of First Data Block To Other?

Mar 7, 2012

I am working on a form having three data blocks
Travel_Inconvineance (Master)
TI_Card_Info (Detail)
TI_Limit_Indemnity (Detail)

On working TI_Card_Info i have 2 columns Plastic_Card_Id and Plastic_Card_Type which i want to be paste on TI_limit_Indemnity data block as i enter in TI_Card_Info.

View 1 Replies View Related

PL/SQL :: Get Columns Name And Value Dynamically From Trigger

Jul 9, 2013

I have to audit some table to log all modifications (inserts, updates, deletes). I wrote the following trigger (works as expected) : 

-- Trigger for DOCUMENT table audit
CREATE OR REPLACE TRIGGER TAUDIT_IUD_DOCUMENT
AFTER INSERT OR UPDATE OR DELETE ON DOCUMENT
FOR EACH ROW
DECLARE
l_user VARCHAR2(64);
[code].........   

Is there any way I can improve this ?I mean getting columns name and value (:NEW and :OLD) dynamically, instead of specifying them one by one (the trigger will have to be updated in case a new column is added). Something like this : 

FOR i in 1..DOCUMENT.COLUMN_COUNT LOOP
l_row := l_row || DOCUMENT.COLUMN_NAME(i) || :NEW.COLUMN_VALUE(i) || l_separator;
END LOOP; 

View 6 Replies View Related

SQL & PL/SQL :: Converting Rows To Columns Dynamically?

Oct 20, 2011

I Have a table like this. 12 rows and 8 columns.And column 1 Dt is going to change everytime,as date and months proceeds.and corresponding columns values are going to change.

I want to convert rows to columns dynamically..

DT COL1COL2COL3COL4COL5COL6 COL7
01/01/20111234567
01/02/2011131415161718 20
01/03/2011252627282930 32
01/04/201137383940414201/05/2011495051525354 55
01/06/2011616263646566 67
01/07/2011737475767778 29
01/08/2011858687888990 92
01/09/2011979899100101102 04
01/10/2011109110111112113114 115

i want result like this.

01/01/201101/02/201101/03/201101/04/2011
1 9 11 12
2 11 23 12
3 11 11 11
4
5
6
7
8

View 2 Replies View Related

SQL & PL/SQL :: How To Pass Different Number Of Columns Dynamically To A Query

Apr 26, 2010

I want to pass Number of columns dynamically to a query. I got success in SQL.

SQL> select &column_list from emp;
Enter value for column_list: empno,ename,sal

EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300

14 rows selected.

But the same i need to achieve in pl/sql. I try with the Ref cursor, but not succeeded.

View 15 Replies View Related

SQL & PL/SQL :: Select Columns From Different Tables Dynamically In A Function

Jan 25, 2013

im trying to select columns from different tables dynamically in a function . The parameter for the function will be table name and column id's, In this number of columns may vary . Is it possible to have dynamic %rowtype to store the cursor value in it.

View 2 Replies View Related

SQL & PL/SQL :: Generate Columns Dynamically By Depending On The Rows In A Table In 11G?

Nov 27, 2012

Is there any way to generate columns dynamically by depending on the rows in a table in 11G .

Ex: If the deptno in DEPT table is not constant,then how to generate the N numbers of columns based on the deptno. Below query is working when we hard coded the deptno (10,20,30,40).What else if we more number of departments and we don't know the departments also.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as dbo

SQL> SELECT *
FROM (SELECT deptno, job, sum(sal) sal
FROM SCOTT.emp GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(10,
20,
30,
40));

[code].....

View 4 Replies View Related

SQL & PL/SQL :: Audit Trail Trigger To Dynamically Pick Columns During DML?

Jun 13, 2010

I want to make a trigger , to store values that are affected during any DML(insert,update,delete).

CREATE OR REPLACE TRIGGER generate_log
BEFORE INSERT OR UPDATE
ON EMPLOYEE
FOR EACH ROW
DECLARE

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Ref Cursor - Values To Be Passed Dynamically

Sep 26, 2011

I have used ref cursor to retrun output in cursor from procedure.I have used bind variables in the v_query and passing values with using clause as given in the following code. It is working but client want to pass values dynamically.

OPEN p_cur FOR v_query
USING p_ht_nm, p_ht_treat_source, var_cycle (1),
var_cycle (2),
var_cycle (3),
var_cycle (4),
var_cycle (5),
var_cycle (6),
var_cycle (7),

[code]...

We can also use execute immediate with the above code. But in both the cases we have to pass values in using clause.

QUERY:
/* Formatted on 2011/09/24 21:13 (Formatter Plus v4.8. */
INSERT INTO z_ca_get_lot_id
SELECT DISTINCT attrbts.lot_id
FROM (SELECT DISTINCT lot_id
FROM z_alv_cert_lot_attrbts_syn
WHERE NAME = 'Heat'
AND UPPER (text_value) = :p_ht_nm) attrbts,

[code]...

View 8 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: ORA-00905 - Get Values From Lookup Table Dynamically

Aug 15, 2013

i need to get values from lookup table dynamically,i am getting the missing keyword error.

create or replace procedure xyz(cur1 OUT SYS_REFCURSOR)
AS
vsql varchar2(2000);
CURSOR CURSOR1 is SELECT DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend';
BEGIN
[code]....

View 14 Replies View Related

SQL & PL/SQL :: Passing Multiple Values To IN Clause In Function?

Jun 22, 2010

I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :

where account_code = P_CODE.

Eg. The value of :P_CODE is 'CS'.

I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.

I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0

View 3 Replies View Related

Application Express :: Comma When Passing Values In URL

Jun 20, 2012

I have two page , from first page to second page I am passing some values using url parameter passing but some values contains comma ex :- P1_ NATION  text field contains INDIA,USA,UK

but apex treated that as these are separate values and assign it to separate items

suppose my intention is like P2_NATION,P2_EMPLOYEE,P2_EMPID :INDIA,USA,UK,SAGAR ,123

but apex treating it as P2_NATION = INDIA :P2_EMPLOYEE=USA :P2_EMPID = UK

I am using apex4.1 , db 11g , ie , chrome , ff.

View 2 Replies View Related

PL/SQL :: Dynamically Assign Values Of Any Column Of Row Type Variable

Nov 12, 2012

need to dynamically assign value of each column of a row type variable:The example is like that:

Table "Student_list" is as follows:
---------
COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50)
[code]....

Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?

View 7 Replies View Related

Windows :: Passing Values / Arguments To Batch File

Aug 26, 2012

I have a batch file Menu.bat which should pass accept the input parameters from the end users and pass the entered values to a different batch file.

After entering option 2 the user should be prompted with an option to enter the path. E.g. /shared/folder.

Now '/shared/folder' should be passed to a different batch file named R.bat

Below is the Menu.bat file.

@echo off
color 00
title Security Audit Adaptor

:main
cls
echo //MAIN MENU\
echo.
echo 1. Got to Google
echo 2. Enter path
[code].......

View -1 Replies View Related

Application Express :: Passing Item Values To Javascript

Nov 23, 2012

APEX 4.2 - Base code from Denes examples

CODE ON HTML HEADER

<script language="JavaScript" type="text/javascript">
function f_setglp(pThis,p_app_item,p_other_record_level_item)
{    
var curr_id = $x(pThis).id; // OK !
var curr_glp = $x(p_app_item).id; // returns undefined
var curr_orli = $x(p_other_record_level_item).id; // returns undefined

[Code]...

The Javasxcript function fires OK on click in the "Product" attribute but: PMO_GLP_PK is an Application Item that Javascript can´t reference as I called YES_NO is another tabular form attribute and the reference returns also "undefined"

what is the best way to implement this ?

View 4 Replies View Related

Application Express :: Passing Values To Modal Popup?

Aug 23, 2012

this modal popup, it works correctly but not passing values from page page1 to my page3. How do i pass these paramater values to page3,

function modalWin() {
if (window.showModalDialog) {
window.showModalDialog("f?p=&APP_ID.:3:&SESSION.:POP:NO::P3_EMPNO:#P1_CUSTOMER_ID#::","name","dialogWidth:600px;dialogHeight:400px");

[Code].....

View 4 Replies View Related

PL/SQL :: How To Fetch Values From Two Columns Using Values From One Column

Jul 25, 2013

From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?

View 2 Replies View Related

PL/SQL :: Passing String Values To Partition Clause In A Merge Statement?

May 24, 2013

I am using the below code to update specific sub-partition data using oracle merge statements.

I am getting the sub-partition name and passing this as a string to the sub-partition clause.

The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.

We are using Oracle 11gr2 database.

Below is the code which I am using to populate the data.

declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin

[code]....

View 2 Replies View Related

Application Express :: Dynamically Update A Number Field By Summing Of Page Item Values

Jun 28, 2012

I'm trying to update a number field on an apex page by summing up the values of multiple page items. I've tried to follow this tutorial

[URL]........

especially the "Create a Set Value Dynamic Action Using PL/SQL" part.

In the tutorial example they return an page item value P3_SAL multiplied by a multiplier they've determined by another page item's case. What I want to do is much simpler in that I just want to add up multiple page item values and then display them in a number field at the bottom of the page.

so the code I tried to use based on the tutorial is in the tab: Home >Application Builder>Application 103>Page 3>Edit Dynamic Action>Create / Edit Action

Set Type: PL/SQL Function Body

PL/SQL Function Body:

BEGIN
return :P3_ITEM1+P3_ITEM2+P3_ITEM3;
END;

Page Items to Submit: P3_ITEM1,P3_ITEM2,P3_ITEM3

But it gives me an error that I need to declare the identifiers, yet it works if I only use one item and I can perform any arithmetic.

i.e.

BEGIN
return :P3_ITEM1+1000;
END;

View 2 Replies View Related

SQL & PL/SQL :: Passing Values From Oracle Object Type To PLSQL Type

Mar 8, 2013

I have created the below types and oracle objects.

create or replace type T_EMA_NP_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)

create or replace type T_EMA_NP_RANGE_LNPTICKET_TAB AS TABLE OF T_EMA_NP_RANGE_LNPTICKET_REC
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC

The following types are created in the Package specification

type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
--
subtype t_ema_lnpticket is T186_IN_REQ_PARAMETER.T186_EMA_LNPTICKET%TYPE; -- Number
type t_ema_lnpticket_tab is table of t_ema_lnpticket index by binary_integer;

I have the following two procedures

PROCEDURE getEMAReturnResponse(
p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_np_setdel_resp_rec,
p_ema_range_lnpticket_tab IN t_ema_np_range_lnpticket_tab,
p_endof_event IN varchar)

PROCEDURE Return_Response(p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_resp_rec,
p_ema_lnpticket_tab IN t_ema_lnpticket_tab,
p_endof_event IN varchar2)

getEMAReturnResponse Procedure:

Accessed by Java application to pass the values. Should call the Return_Response procedure and pass the values received from Java.

Return_Response Procedure

The p_ema_lnpticket_tab is a sort of array that can have multiple values. Please see the example of values. Has all the business rules and validation that should be adhered.

Example of Vaules
p_in_call_request_id = 1
p_ema_resp_rec = 12345, 'Operation Failed'
p_ema_lnpticket_tab = (1,2,4,5)
p_endof_event = Y

View 2 Replies View Related

PL/SQL :: Passing Values From Table Type To Oracle Object Type

Mar 8, 2013

I have created the below types and oracle objects.

create or replace type T_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
--
create or replace type T_EMA_NP_RANGE_LNPTICKET_REC as object
(
ticket number
)
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC

The following type is created in the Package specification

type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);

I have the following two procedures

Procedure getResponse(p_call_request_id IN number, p_resp_rec IN t_setdel_resp_rec,
p_range_ticket_tab IN t_range_icket_tab, p_endof_event IN varchar)

PROCEDURE ProcessResponse(p_call_request_id IN number, p_resp_rec IN t_resp_rec,
p_ticket_tab IN t_ticket_tab, p_endof_event IN varchar2)

The get Response procedure is a wrapper procedure exposed to Java to pass values. The Process Response procedure is a main procedure where all logics and business rules are handled.

The Problem is:

How can I pass the values from get Response procedure to Process Response procedure. So that rules and validations are applied. Please note the p_ticket_tab may have many ticket numbers corresponding to p_call_request_id.

Values E.g. :
p_call_request_id = 1
p_resp_rec (1234, 'Error found')
p_range_ticket_tab (1,2,3,4,5)
p_endof_event = 'Y'

View 7 Replies View Related







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