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)
SELECT rp.id r_paper_id, COUNT(*) created, COUNT( CASE WHEN a.approved_yn = 'Y'
[code]....
But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below.
Insert Into Test1 values(10,'Thomas','Manager','LA'); Insert Into Test1 values(11,'George','Chairman','LA');
declare MTest1Test1%RowType; str1VarChar2(1000):='Name'; MValue1Varchar2(100); begin Select * Into MTest1 from Test1 where ID=10; str1:='Select MTest1.'||str1||' from dual'; execute immediate str1 into mvalue1; dbms_output.put_line('mvalue1 '||mvalue1); end; /
I am getting the error:
ORA-00904: "MTEST1"."NAME": invalid identifier ORA-06512: at line 9
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below. sort it out?
Create table Test1(ID Number(10), Name varchar2(100), Description varchar2(1000), Site varchar2(100));
[Code]...
I am getting the error:
ORA-00904: "MTEST1"."NAME": invalid identifier
ORA-06512: at line 9
My objective is to get the name 'Thomas' in the variable - MValue1. I don't want to use the statement MValue1:=MTest1.Name because, moving forward, I want to have the column name to be dynamic, taken from an array of column names.
I need to achieve it by giving only one select query to get entire record and fetch the column values one by one, by substituting the column name from an array of column names, instead of writing that many lines of code / querying that may times to the database.
is it possible to use the records returned by a query as column names in a select query.
select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%') from v$database; * ERROR at line 1: ORA-01427: single-row subquery returns more than one row
How to dynamically execute a select command whether it is retrieved a single record or multiple record in oracle database 10g. i have tried with the command execute immediate but it was not successful.
is it possible can i delete or drop multiple tables in a single drop and delete statement.
We have an requirement to build (XML) Select statement dynamically based on the mapping table (which is in hierarchy format)
I have removed xml keyword, so query will not work. but need procedure which will recursively check mapping table (parent and child table) and build below given select statement.
While writing a procedure I went into this problem. Whenever I write Query : Select * from dba_pending_transactions It works fine.
But whenever I use same Select Query inside PL-SQL block it gives error Table or view not exist. Dba_pending_transactions is view.
SQL> declare 2 v_count number(2); 3 begin 4 execute immediate 'select count(*) from dba_ending_transactions' into v_count; 5 dbms_output.put_line(v_count); 6 end; 7 / declare * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at line 4
Same error I get when i use it inside a procedure.
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.
Step# 2 manipulated the tables: Inserted 3 records in Table_C.
Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:
CREATE OR REPLACE TRIGGER TABLE_A_TR after INSERT OR UPDATE OR DELETE ON TABLE_A FOR EACH ROW DECLARE [code]......
Step# 3 compiled the created trigger and I've successfully compiled it. Step# 4 Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A. Insert into TABLE_A values (1,'testa','testb')
I've successfully insert the values into TABLE_A however I've observed that the trigger didn't execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there's an issue in my insert statement but I've successfully populated the values into TABLE_B. So I'm wondering why the trigger didn't execute the insert statement.
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.
Below is the block which i am trying to test in scott schema. I dont want to substute IN clause values directly. So i have written cursor and have added in separate variable separeated by comma.But its not working.
declares varchar2(1000);s1 varchar2(1000);v number := 0;v1 varchar2(2000) := 'SCOTT';j number := 0;cursor hhis select ename from emp;beginselect count(*) into v from emp; for i in hh loops := s||''''||i.ename||''''; j := j+1;if j <> vthen s := s||',';end if;s1 := s1||s;s := null; end loop;dbms_output.put_line(S1); case when v1 in (s1) then dbms_output.put_line('Y'); else dbms_output.put_line('N'); end case;end;
How do you specify the value in START WITH dynamically during runtime. For example consider the bewlo scenario:
CREATE TABLE ISCT ( ITEM_NO VARCHAR2(15 CHAR) NOT NULL, ITEM_TYPE VARCHAR2(3 CHAR) NOT NULL, ITEM_TYPE_SCO VARCHAR2(3 CHAR) NOT NULL,
[Code].....
Now if you create a view:
create or replace view test_v (ITEM_NO, ITEM_TYPE, ITEM_NO_SCO,ITEM_TYPE_SCO) as SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO FROM [Code] .......
Then run the below query:
select * from test_v where item_no_sco = '00245905' --
the output is got within a second. but the real scenario is that both table icont and isct have millions of rows. In that case even this small query of the view takes 10minutes as there a FULL TABLE JOIN.
How do I give the START WITH in the view to make it faster i.e. even with millions of rows the output of this comes in seconds:
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]....
To display highest marks,least marks,average marks,total marks of the student name entered.
desc stud; Name Null? Type ----------------------------------------- -------- ---------------------------- SID NUMBER NAME VARCHAR2(20) M1 NUMBER M2 NUMBER
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.
Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual; but i get the following error: ORA-14551: cannot perform a DML operation inside a query.
The intention of the code is to perform an insert into my table based on passing in values via an object into Stored Procedure Apply_Users_Update
Package Definition create or replace PACKAGE OTMP_TCIS_RS AS --1 PROCEDURE Get_UserInfo PROCEDURE Get_UserInfo( o_OutCode OUT INT, i_language IN VARCHAR2); FUNCTION Get_UserInfo( i_language IN VARCHAR2) RETURN NUMBER; [code]....
select * from (select pa_request_id ,max(status) status ,max(approved_amount) approved_amount ,min(level_id) level_id ,max(req_amount) req_amount from target_aggregation_attendee group by pa_request_id) where status = 'Approved')
My Main Query:
select TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername, TARGET_EMPLOYEE.GE_ID as requesterGEID, TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName, TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg, TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg, TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
WHILE EXISTS ( SELECT * FROM tblOrgChart WHERE fxOrgID = v_chrTempKeyDept ) LOOP v_intDept := CAST(v_chrTempKeyDept AS NUMBER) + 1 ; v_chrTempKeyDept := LPAD('',3 - LENGTH(CAST(v_intDept AS VARCHAR2)),'0') || CAST(v_intDept AS VARCHAR2) ; END LOOP;
Error: PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only
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)*
Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error
SQL Error : ORA-14551: cannot perform a DML operation inside a query
Below Is the
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS BEGIN INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID) VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1); RETURN 1;
