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.
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.
I had never done this before, basically I need to get the result of the SQL query which had three columns and pass into thesend _mail proc, should I build a variable to hold the 3 value and send mail from it?
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.
ID S_OWNER SOURCE_TABLE A_OWNER ARC_TABLE CONDITION_COLUMN PERIOD_VALUE PERIOD_UNIT
1 wedb Auction_table wedb Arc_Auction_tableAuction_date 15 Days 1 wedb Sales_table wedb ArcSales_table Sales_date 180 Days 1 hr Accounts_table hr Arc_Accounts_tableAccount_date 2 Years 2 concor Concur_table con Arc_Concur_table Last_update_date 4 Months
Like this 1000 entries are there in the control_table.
I want to pass all the columns from my control table dynamically.I am able to pass all the columns dynamically,except the PERIOD_VALUE.I have stucked up how to implement this condition.My aim is to get the data which is <= sysdate-PERIOD_VALUES based on PERIOD_UNIT.
For Example:
For Auction_date column I want to get the data which 15 days old. For Sales_date column I want to get the data which 180 days old. For Account_date column I want to get the data which 2 yers old. For Last_update_date column I want to get the data which 4 months old. [code]....
Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.
I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:
SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS
If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....
DECLARE stmt_str varchar2(200); cur_hdl int; rows_processed int; FIRST_NAME varchar2(200); LAST_NAME varchar2(200); BEGIN cur_hdl := dbms_sql.open_cursor; -- open cursor stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS'; [code]....
However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
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;
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.
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.
I am trying to perform a dml operation(insert) to insert data into a table. I created a procedure in which a insert statement is generated. In another procedure I am trying to execute this auto generated statement using execute immediate. For that I created 2 variables . First one contain the insert statement and other one contain the list of columns. I m passing these strings to execute immediate like this
Execute Immediate(v_query) using(v_col_list)
after execution i m getting the following error
ORA-01008: not all variables bound ORA-06512": at ' db_name.load_data' , line no 217
but when i tried it with list of column into using clause it is working like
Execute Immediate(v_query) using col1,col2,col3
I have more than 150 tables in the schema and i am creating a single procedure to load the data into base tables by using external tables. By passing the table name to procedure it generates the update and insert statements but i am getting error while executing the statements with dynamic sql.
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));
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.
I have a scenario to frame an xml as below, below i have given a sample data
Create table xml_type (msg varchar2(1000), desp varchar2(1000), val number) ;
[Code]....
Actually i need the output as below, i tried and succeeded doing it in Procedure , but they are asking me to do it in SQL query. How to get from an sql query.
I have following requirement. Let say i have to generate a range based on "start number" and "end number" dynamically.Some kind of hash buckets.
e.g Start Number : 1 and End Number : 1001.
Also i want to divide that range based on some dynamic value like for above example 10 ranges of 100 each. and both 1 and 1001 should be included only once and the next row start number cannot be same as previous row end number.
Means
Range 1 1 -101 Range 2 102-200 Range 3 201-300 ... Range 10 901-1001
Is there any way of doing it automatically. I tried with Model clause. it works fine for even cases but for odd i have issues also when i take small start and end number i get an error.
SELECT case when ranges=1 then ranges else ranges+1 end Start_Id, ranges+round((1001-1)/10) End_Id from (
[Code].....
I want this to be generic for any values here 1 is Start Number 1001 is end number and 10 is the bucket. I need these parameters dynamic and want's the same kind of results for any values.
I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.
Here is an example.
initial input : '5545|4124|12456' using the sql below to break the string into columns. select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual connect by regexp_substr(, '[^|]+', 1, level) is not null;
But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.
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.
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:
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 am trying to pass the parameters to query and I need to pass few parameters to query. Can I make it like like then I run it, then it come up and ask the parameter like this:
SQL> @text para1 XXXXX para2 XXXXX
And then the result of query will come up. I would like to put label for each parameter( likepara1).
