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]....
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.
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.
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.
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
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)
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)*
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?
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.
( 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
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?
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]....
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?
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.
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
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.
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
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.
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
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.
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;
