SQL & PL/SQL :: Checking Variable Number Of Columns?
Apr 12, 2010
My goal is this:I have a table which is being updated/changed by lay people with certain types of values. Constraints are given to them, but they need not conform to them as they update the table in excel.Now, I want to validate this table every-time before I use it. i.e. implement a script which can be run to verify is all the values are in the right format for further usage.
I have a variable number of columns (i.e. users can add further columns as their requirements change).From columns 3 to 'n' (depending on table given) the values should be 'Yes' or 'No'. How do I check this for a variable number of columns in PL/SQL?
View 11 Replies
ADVERTISEMENT
Oct 21, 2011
I've created a stored procedure which creates itself a view (a MV to be honest); the instructions to create this m.view are dinamically built insinde my procedure, so each time i run it, based on the different input parameters, i've got a different result (my output m.view can have three colums the first time, or ten the next time) how can I read my output view to put the data into file? I've tried with "select * bulk collect into my_array from my_ output_ view"...after declaring "my_array" as a varying array with the max number of colums I could ever have...but nothing: if the array dimension doesn't match the number of columns that i've on my view, i.e. i receive "ora-00947 not enough values" error.
Is there a method to dimension dinamucally the array to store my data? Or should I change the code to fetch some other way the data i need to put to a file?
View 2 Replies
View Related
Feb 24, 2010
I want to add a variable number of new columns to an existing table temp (with column provided).
Example:
NewColumnNo = 4
-> the columns shall be named rate_1, rate_2, rate_3 and rate_4
-> the values shall be = Column / NewColumnNo
The result shall be like this:
create table temp_res (prodid integer, rate_1 number, rate_2 number, rate_3 number, rate_4 number);
insert into temp_res values(1, 0.25, 0.5, 0.75, 1);
insert into temp_res values(2, 0.25, 0.5, 0.75, 1);
insert into temp_res values(3, 0.25, 0.5, 0.75, 1);
insert into temp_res values(4, 0.25, 0.5, 0.75, 1);
insert into temp_res values(5, 0.25, 0.5, 0.75, 1);
insert into temp_res values(6, 0.25, 0.5, 0.75, 1);
insert into temp_res values(7, 0.25, 0.5, 0.75, 1);
insert into temp_res values(8, 0.25, 0.5, 0.75, 1);
insert into temp_res values(9, 0.25, 0.5, 0.75, 1);
View 8 Replies
View Related
Feb 24, 2012
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.
View 11 Replies
View Related
Jun 3, 2013
i am working on the oracle 10g.currently i want to build one procedure,where i get a variable which contain character as well as number.my aim in the procedure is to replace all the character like ( a,b..z) in this variable with the number field.
example
suppose i have one variable say
var := 'MALT011000012345MTLCAST001SMT84'
now i want to replace each character say
A with value 23
B with value 56
L with value 99
9 i will get these value form another table).
i will have handle thousands of variable and they can start with any character and they contain character anywhere.
View 4 Replies
View Related
Mar 6, 2012
i want to store all rows of columns into single variable and then use in inside of SP
declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....
it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g
View 4 Replies
View Related
May 30, 2012
I have developed a form which is running fine most of the time,
but some time it gives error
unable to insert record and if we see detail it shows
ORA-01036: illegal variable name/number
error.jpg is attached. if there is some problem, it should not pass any entry and if there is no problem then why it gives error of illegal variable?
View 3 Replies
View Related
Jun 21, 2011
how to store total no of updated rows (number) in a variable after executing an updation query using script
View 2 Replies
View Related
Nov 19, 2011
the problem below:
I have a table AlertData below:
DeptIDMONTHCount
192010-041392
192010-051134
192010-061094
192010-071333
292010-042217
[Code]...
Within each DeptID group I need to calculate absolute change of 'Count' column between previous and current months and compare change value with threshold.
If ratio >= threshold N number of times I need to make a note of that event. Threshold = 0.1 N = 2 - alert needs to exceed threshold two consequtive times
Here is data processing algorithm:
1. Calculate change between month 2010-04 and 2010-05: abs((1134/1392 - 1))= 0.18;
2. check change value against threshold: 0.18 > 0.1
3. Threshold was exceeded, set alert_fired_cnt counter to = 1
4. Once alert fired it creates a baseline for comparison - I need to use Count from month 2010-04: We're now in month 2010-06: abs(1094 / 1392 - 1)=0.21
5. check change value against threshold: 0.21 > 0.1
6. Threshold was exceeded, increment alert_fired_cnt counter by 1 = 2
7. At this point alert exceede threshold two times, I need to set a alert_triggered flag = 1 and reset alert_fired_cnt = 0 for further calculations
8. We're in montn 2010-07: abs(1333/1294-1)=0.03
8. check change value against threshold: 0.03 < 0.1
9. Since threshold was not exceeded, keep alert_fired_cnt counter to = 0
Above algorithm needs to be run for all DeptID groups.
I load above data into an associative array and loop through elements. I am having trouble keeping computations within each DeptID group.
View 18 Replies
View Related
Apr 21, 2010
in retrieve column data in the cursor.My requirement is I created a table dynamically as I don't know how many fields will be there.And the table structure would be like this
Filed1 varchar2(10)
Filed2 varchar2(10)
-----------
-----------
Filedn-1 varchar2(10)
Filedn varchar2(10)
[code]...
As in the cur.filed value.
View 1 Replies
View Related
Apr 4, 2008
query to know number of columns in a table i.e.
if I want to know how many number of colums are present in a specific table then what would be the query.
View 1 Replies
View Related
Aug 13, 2008
in my oci applications,if i get a column of number that is in the scope of int,i can use value = *(int *)field.data; get the value,but if the column size is larger than 10,the code can't be available,how can i get the value.
View 6 Replies
View Related
Jul 12, 2012
I have a table with two columns Column1 and column2
Like such
create table testTable (column1 number(15), column2 number(15));
insert into testTable values (1,5);I need to find the numbers between column 1 and column 2 including the column 1's number and column's 2 number.
so my answer set should be
1,2,3,4,5
View 4 Replies
View Related
Jul 17, 2012
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.
View 4 Replies
View Related
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
Dec 13, 2011
how to update the middle of plenty rows in the middle of the columns
sample_data
id name state REGION LOC
1 v A.p 1 1
2 a
3 g K.A 0 3
4 y
5 i T.N 1 0
6 l M.P 0 1
7 c U.P
This is sample data,and i have this kind of large data and i need to fill the rows which are empty. In three columns state,region,loc with data like 0,web_intimation,1,
View 8 Replies
View Related
Nov 25, 2011
if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.
For example, the table A has 5 columns
(c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3)
and
r1 has values only for c1,c2
r2 has values only for c1,c2,c3,c4
r3 has values only for c1
so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".
View 4 Replies
View Related
Jun 6, 2012
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.
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....
View 7 Replies
View Related
May 4, 2010
The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....
View 8 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
Oct 8, 2013
I have a report with single row having large number of columns . I have to use a scroll bar to see all the columns. Is it possible to design report in below format(half columns on one side of page, half on other side ofpage :
Column1DataColumn11DataColumn2DataColumn12DataColumn3DataColumn13DataColumn4DataColumn14DataColumn5DataColumn15DataColumn6DataColumn16DataColumn7DataColumn17DataColumn8DataColumn18DataColumn9DataColumn19DataColumn10DataColumn20Data I am using Apex 4.2.3 version on oracle 11g xe.
View 2 Replies
View Related
Feb 1, 2012
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
(Select distinct c.contact_code
Following is the SQL query
Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......
View 1 Replies
View Related
Jun 7, 2011
We have a problem where a data load script has caused data in one of our tables to be incorrectly loaded. Instead of loading the parent row, it has loaded an offest that was assigned by the application.
The problems is, we have the following table:
=================================
ID RESULTSET PARENT
=================================
...
100 502 -1
101 502 0
102 502 1
103 502 2
104 502 3
105 503 -1
106 503 0
110 503 1
111 504 0
...
=================================
What's the best approach to use to transform the last column as follows:
=================================
ID RESULTSET PARENT
=================================
...
100 502 99
101 502 100
102 502 101
103 502 102
104 502 103
105 503 104
106 503 105
110 503 106
111 504 110
...
=================================
Note: ID column may not always be ordered sequentially
View 6 Replies
View Related
May 11, 2012
I have a table with three columns: terminal, place and batch. How can I check for missing batches?
select * from transactions;
terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825
View 5 Replies
View Related
Jan 22, 2013
Which is the best way to check whether a record exists.
DECLARE
l_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_count FROM emp WHERE empno=7839;
IF l_count=1 THEN
dbms_output.put_line('exists');
ELSE
dbms_output.put_line('not exists');
END IF;
END;
[Code]...
View 7 Replies
View Related
Dec 8, 2010
I want to ask for a password from the user so they can access their account, however I want the password to have constraints on it. For example I want the user input to between 6 and 12 characters in length containing at least one number and one letter.
View 2 Replies
View Related
Jun 11, 2012
How can check that which query is taking how much load or time in execution on server in oracle.I want the soluation as like that, run the query and get the detail in desc order and identify that this query is required to tune or kill the session.
View 1 Replies
View Related
Apr 22, 2013
declare
vnum number;
vname varchar2(50):='t1';
begin
begin
select count(*) into vnum from dba_tables where table_name=vname;
dbms_output.put_line('table count '||vnum);
exception
when others then
vnum := 0;
end;
begin
if vnum>0 then
execute immediate 'drop table '||vname;
dbms_output.put_line('table dropped');
end if;
exception
when others then
dbms_output.put_line('table does not exists');
end;
execute immediate 'create table '||vname ||' ( n number)';
dbms_output.put_line('table created');
end;
View 14 Replies
View Related
Jan 19, 2013
CREATE TABLE t2
(
id NUMBER,
ename2 VARCHAR2(20),
sal2 NUMBER,
job2 VARCHAR2(20),
conid NUMBER
[Code]...
My requirement is like when I am calling the procedure P1 with some values then it should check the table "t2".And table "t2" is linking with table "t3".
So what ever the column "verify" is there, it should check the incoming values against it. If matches success otherwise reject it.Later the incoming values is stored different tables.I am doing it in the above way by hard coding some value.
BEGIN
p1(1,'MILLER',500,'ADMIN'); --REJECT
p1(1,'MILLER',5000,'ADMIN'); --ACCEPT
P1(2,'MILLER',5000,'SALESMAN');--ACCEPT
END;
View 5 Replies
View Related
Sep 22, 2009
In my table ,data type of one among 10 columns is defined as varchar2(10).I need to check that column should accept only numeric value(0 to 99) or alphabetic value(a to z or A to Z) .It should not accept Alpha-numeric values.I tried like this
select c3 from demotab where to_number(c3) not between ascii('a') and ascii('z') ;
but I got error like 'Invalid Number'.how to implement this thro sql query.
View 3 Replies
View Related
