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?
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?
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.
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.
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.
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
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.
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 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.
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,
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".
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.
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.
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 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.
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
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.
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;
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.
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.
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;
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;
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.
