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
ADVERTISEMENT
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
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
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
View Related
Jul 20, 2010
i have list item populated with many table names from a schema. i have grid in oracle forms 10g and i want to fill the grid with at least four/more columns.I want to fire the list change trigger when each time any one table name is selected.
how can i find the columns names dynamically for filling the grid.
View 6 Replies
View Related
Apr 15, 2013
in below program,i have to pass column name to cursor at runtime..but this giving error.
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record hr.employees%rowtYPE;
v_stmt_str VARCHAR2(200);
colname varchar2(10):='Last_name';
BEGIN
[code]....
dbms_output.put_line(emp_record);
*
ERROR at line 13:
ORA-06550: line 13, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored
View 2 Replies
View Related
Aug 8, 2012
How can we pass column index or column name in cursor for loop.
View 3 Replies
View Related
May 1, 2011
I have a problem executing a function.
There are two tables,
1. Table with column names of a second table mapped to certain variables.
ex: Table1
col1, var1
col2, var2
col3, var3
--------
--------
2. Table with the values for columns as given in table 1 ex:
col1, col2, col3, col4, col5,
a , aa , 1 , x1 , p
b , ab , 2 , x2 , q
c , ac , 3 , x3 , r
I have to select values from table2.col1 do some processing and calculate values and store it in a table then do the same thing with col2 and so. This needs to be done for all the columns that appear in table1.For example in table 1 i have only three columns mentioned thus i have to process col1, col2 and col3 from table2. col4 and col5 will not be processed since they do not appear in the first table.
The problem is i have hundred columns in table 2 and the user can add up to hundred columns in table 1 as and when it is required.
I have created a cursor to first select column name from table 1 where variable is not null.For each value in cursor i put it in a local variable.
Second step is to select values from table2 where instead of column name i am using the local variable.But the problem is instead of choosing values from col1 the query returns the value as col1 (the value of local variable)
View 2 Replies
View Related
May 18, 2013
How can we dynamically get maximum value permitted in a number column? Is there any in-built function in oracle for this?
e.g. Accrued_Interest NUMBER(10,4)
In this case maximum value that can be inserted in this column is "999999.9999".
View 2 Replies
View Related
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
Oct 29, 2012
I have created report with the collections. Report query has been changing dynamically but i got problem with report header names and headers names has not been changing dynamically it is always showing like c001,c002....etc so, i have created global item(G_ITEM) in shared components--->Applications items after that I have created process(before headers) and assigning some value to G_ITEM and used &G_ITEM to column headres in IR Report but here &G_ITEM is not showing any value.
How can i achieve dynamic headers names by using *&G_ITEM(global items)*
View 1 Replies
View Related
Feb 19, 2013
Version 4.0.2.00.07
I have an Interactive Report with a column that I need to create a link on but I need to pass the value of another column.
This report shows the Features of a particular Release. There is a select list for the Release that is selected and executes the report. There is a column that indicates whether the Feature has a Commitment attached to it. If there is have that a link to another report that shows all the Commitments for that Feature by passing the FEATURE_ID which is also a column on the Feature report.
Query:
SELECT
....
,CASE WHEN ft.feature_id IN (SELECT cc.feature_id
FROM customer_commitments cc
WHERE ft.feature_id = cc.feature_id
) THEN 'Yes' ELSE 'No'
END "Commitment Exists"
,ft.feature_id
....
FROM ....If "Commitment Exists" has a 'Yes', the 'Yes' is a link that will pass the FEATURE_ID to another report that will show the Commitments for that Feature. If the "Commitment Exists" has a 'No' then no link. I was also wondering if the Commitment report could open in a seperate window and make it modal?
View 7 Replies
View Related
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
May 27, 2013
I am trying to calculating the total column based on two columns, Total=unitprice*no.of items
I wrote this Javascript in page properties>javascrpt>Execute when Page Loads
"$('input[name=f05],input[name=f06]').change( function(){
parent_row = $(this).parents('tr:first');
sal = ( parent_row.find('input[name=f05]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f05]').val() );
comm = ( parent_row.find('input[name=f06]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f06]').val() );
total = sal * comm;
parent_row.find('input[name=f07]').val(total);
});
But its not Giving any result in total column?
View 3 Replies
View Related
Oct 24, 2013
Here is an example
(
pnLeadID p_lead.lead_id%TYPE ,
)
IS
v_lead_rec p_lead%ROWTYPE;
v_lead_rec := dml_p_lead.get_rec(pnLeadID);
-- %TYPE is used to declare a field with the same type as
-- that of a specified table's column:
-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:
so pnleadid it gets the collumn in p_lead table
so for v_lead_rec is saying what ever is in the collumn pnleadid =v_lead_rec
View 3 Replies
View Related
Oct 26, 2010
It seems that dml trigger doesn't fire when lob field is being updated using dbms_lob package.
As it stated in Oracle documentation:
QUOTE Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.
I need to know that table was updated (or is about to be updated), how can I do that in case it is lob field that is being updated?
View 4 Replies
View Related
Dec 5, 2012
How can I do something like that...It has to be possivel... Not this way, but somehow..
CREATE OR REPLACE PROCEDURE access_reg_column (reg_user user%ROWTYPE)
IS
v_column_name VARCHAR (50);
BEGIN
V_column_name := 'name';
DBMS_OUTPUT.put_line (reg_user.'v_column_name'); ????
END;
View 2 Replies
View Related
Jul 6, 2012
This error is returned when executing a Function with the RETURN X%ROWTYPE is used.Here is the code.
CREATE OR REPLACE function FE_GET_addr (
p_PIDM IN addr.addr_PIDM%TYPE,
p_atyp1 IN VARCHAR2,
p_atyp2 IN VARCHAR2,
p_atyp3 IN VARCHAR2,
p_atyp4 IN VARCHAR2
[code]....
View 5 Replies
View Related
Oct 26, 2010
It seems that dml trigger doesn't fire when lob field is being updated using dbms_lob package. As it stated in Oracle documentation:
Quote:Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.
I need to know that table was updated (or is about to be updated), how can I do that in case it is lob field that is being updated?
View 1 Replies
View Related
Oct 11, 2012
using as template this table:
create table t1 (c1 number,c2 number);
CREATE OR REPLACE
TYPE REC IS RECORD (
R1 T1%ROWTYPE,
R2 NUMBER
);
I'm trying to create one RECORD type with all the columns from table T1 + one new field R2. But gives me an error.
The point to use T1%ROWTYPE and not to hardcode the columns from T1, is due to if we add a new column to T1, is created when is executed again RECORD definition and not to add the column.
View 3 Replies
View Related
May 17, 2010
What is functional diffrence between type, %rowtype and record.
View 3 Replies
View Related
Apr 20, 2012
Can we create TABLE type object by using %ROWTYPE in SQL.
I am bale to create PL/SQL table type object .But i am unable to create SQL type
SQL> declare
2 type table_emp is table of scott.emp%rowtype index by binary_integer;
3 employees table_emp;
4 begin
5 select * bulk collect into employees from scott.emp;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> create or replace type table_emp is table of scott.emp%rowtype index by binary_integer;
2 /
Warning: Type created with compilation errors
SQL> show errors
Errors for TYPE DBO.TABLE_EMP:
LINE/COL ERROR
-------- ----------------------------------------------------------
1/19 PLS-00355: use of pl/sql table not allowed in this context
0/0 PL/SQL: Compilation unit analysis terminated
SQL>
How can I create global table type object with %rowtype
View 3 Replies
View Related
Jun 1, 2010
Script for test:
CREATE TABLE TEST(empno VARCHAR2(4), empname VARCHAR2(50), empstd NUMBER(2))
insert into test values(0001,'A',2);
insert into test values(0002,'B',5);
insert into test values(0003,'C',2);
insert into test values(0004,'D',7);
insert into test values(0005,'E',9);
Now I want to get empno for the particular employees based upon subscript and I have written below
DECLARE
CURSOR cur_rec
IS
SELECT *
FROM TEST;
TYPE cur_type IS TABLE OF cur_rec%ROWTYPE;
v_cur_rec cur_type;
BEGIN
OPEN cur_rec;
FETCH cur_rec
BULK COLLECT INTO v_cur_rec;
[code]....
how to access individial item froma table of rowtype data.
CM: Added [code] tags, please do so yourself next time, see the orafaq forum.
View 5 Replies
View Related
Apr 1, 2013
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
View 3 Replies
View Related
Mar 22, 2013
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
View 1 Replies
View Related
Dec 3, 2012
below SQL I'm pulling data manually(by entering specialist name) instead of that I want this query to be modified so that the sys is pulled dynamically, I.e. like select specialist from t.
CODESELECT
type, Hari,Rakesh,Santhosh, Total
FROM (
select
(type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari,
max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh,
max( decode( SPECIALIST, 'Santhosh', cnt, null ) ) Santhosh,
Sum(cnt) total
from (
select
[Code]...
View 1 Replies
View Related
Feb 17, 2010
I have created one form using oracle ids. i want add value into combo box in runtime.
View 3 Replies
View Related
Dec 8, 2010
i am trying to pass operators dynamically.
SQL> create table test(Amount number,Name varchar2(10));
Table created.
SQL> insert into test values(100,'USA');
1 row created.
SQL> insert into test values(150,'NEWYORK');
1 row created.
SQL> insert into test values(200,'SCOTT');
[code].....
I would like to pass the operators from another table. How to pass the operators from other table.
View 8 Replies
View Related
Sep 15, 2013
I want to add number of zeros after a number dynamically. eg: If number is 2 and number of zeroes to be added is 5 then final output should be 200000;
View 12 Replies
View Related
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
