Does Column V$SQL.CHILD_NUMBER Related On Number Of Current Cursor Only
Jan 11, 2013
Does the column V$SQL.CHILD_NUMBER related on number of current cursor only ? V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text enteredI see in V$SQL big numbers in V$SQL.CHILD_NUMBER ( HASH_VALUE and PLAN_ HASH_VALUE is in my quewry fixed ) , am I wrong that this column displayed not summary count of "objects"(in terminology of Steeve. URL....
View 0 Replies
ADVERTISEMENT
Jun 30, 2011
set serveroutput on
declare
type curs_type is ref cursor;
rec employees%ROWTYPE;
curs curs_type;
[code]...
It gives the error "identifier in CURRENT OF clause is not a cursor name" even though the identifier "curs" is a cursor name
View 3 Replies
View Related
Jul 18, 2011
In my form line level block contains 100 records.i will check the check box for line number 96 and 97. Then i will press save(I have written some logic here) button it will generate one number for selected check boxes. After generating this number cursor(control) should be on same line number 96 or 97.
View 10 Replies
View Related
Feb 25, 2010
I want to get current mouse cursor position dynamically,what should i do.
View 3 Replies
View Related
Feb 19, 2013
I have a report created in Reports6i. I have two fields at the bottom
1.Page Total
2.Voucher Total
I do not want to print the Page Total if the total number of pages = 1.
How can I achieve this?
View 1 Replies
View Related
Sep 30, 2013
I need to copy some text value in to a multi-line text item on the current cursor position.
View 8 Replies
View Related
Dec 7, 2010
i m using oracle 10g 10.2.0.2 version.i create a form and using check box on this form.when i click this check box then loop is using behind it.and current cursor is going to last record
i want if i click 4 record then cursor is still showing on 4 record mean i click which record after using loopmy current cursor is showing on that particular record
how it is possible
View 1 Replies
View Related
Jan 1, 2013
Looks like I am at the end of my wits here but how to get the "DAY" based on current/given territory from a number between 1 and 7? Oracle has functions to extract day of the week but I can't find the other way round.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> alter session set nls_territory=AMERICA;
Session altered.
SQL> select to_char (sysdate, 'D') from dual;
T
-
3
SQL> alter session set nls_territory=GERMANY;
Session altered.
SQL> select to_char (sysdate, 'D') from dual;
T
-
2
I need a way to know that number "3" on database with NLS_TERRITORY = AMERICA means a "Tuesday" or number "2" on a database with NLS_TERRITORY=GERMANY means "Tuesday" as well.
View 9 Replies
View Related
Dec 22, 2011
I need to update the current column with sum of the previous column values. Following are the creation scripts
DROP TABLE TEST_LOG;
CREATE TABLE TEST_LOG
(
[Code]....
Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.
I tried by using the following query
UPDATE TEST_LOG T SET SUM_PRE_COLS =
( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0)
FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);
View 5 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
Dec 15, 2011
I have to procedure that computes number of project, and average working hours of employees where employee id is passed as a parameter to the procedure. If the average working hours is less than 10 then employee's salary remain the same, otherwise check if number of project is less than 4 then 5% of salary, else 10% of salary is added to the salary.
my tables are:
CREATE TABLE employee(
empid number(5),
empname varchar(20),
address varchar(20),
no_of_dependents number(5),
deptno number(5),
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(empid),
CONSTRAINT EMPLOYEE_FKEY FOREIGN KEY(deptno) REFERENCES department(deptno));
[code]...
the problem is i have to use only 2 cursors - 1 for select and 1 for update.
View 27 Replies
View Related
Sep 28, 2012
my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?
View 6 Replies
View Related
Apr 1, 2013
I want the query to generate the sequenctial number from 1 for the new financial year in some query.
View 6 Replies
View Related
Jul 12, 2013
we use oracle 11g. 11.2. I have a situation where in a pl-sql package, there is a modification needs to be done . Every User is associated with destination codes. For eg, user1==>0101,0104 There is a destination_number and country field in the table1.Each of the destination number is associated with a country code and this combination is unique. for eg:
dest_code country_code 0101 CHINA 0202 UK 0101 Mexico 0104 Mexico
For example, if user1 logs in to the system, then pl sql logic will look if that dest_code is in a table1,If the dest_code is present in the table1, then that dest_code number wont be added to the dest_code list formed for that user. For example,if user1 got dest_codes 0101,0104 associated with him.IF 0101 is in exception table then 0101 and 0104wont be used to form dest_code list. If NOT, a dest_code list will formed like 0101||0104. I am able to form the logic like I have a cursor that loops for each dest_code.For each dest_code , it will get that dest_code into v_dest_code_no number type variable. open cursor fetch dest_code no into v_dest_code v_dummy number:=0;v_dest_code_list number:=0; LOOP v_dest_code_list:=v_dest_code ||v_dest_code ; v_dest_code :=0; END LOOP: How to make sure that for each dest_code set, if one of them is in table1, then the next number also will not be added to the list.
View 1 Replies
View Related
Sep 13, 2012
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.
View 12 Replies
View Related
Oct 15, 2013
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')
500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498
View 1 Replies
View Related
Feb 11, 2013
We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report
<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012
[Code]....
The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.
Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)
ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.
So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.
View 0 Replies
View Related
May 30, 2012
What is the difference between the following . In my schema all are giving the same results with some different format
SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;
SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM
View 1 Replies
View Related
Sep 22, 2010
I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.
View 5 Replies
View Related
Oct 4, 2008
Create a table with 100 records.Then write a BULK COLLECT Cursor that can reduce the number of context switches by using a bulk fetch to query records in sets or all at once.
View 1 Replies
View Related
Mar 21, 2013
I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.
eg
id description
78664 Pumps Alarm from CAMS RTU154
In the above example this column has 5 word so i want data in 5 rows like below
78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154
This column data can be varied from 1 to any number of words.
View 11 Replies
View Related
Jul 23, 2013
is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:
select ass.column20, ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'
If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO
View 7 Replies
View Related
Dec 25, 2012
I have a partitioned table with ~50 million rows that was setup with a number(10) instead of a date column. All the data in the table is ALWATS in this format YYYYMMDD
CREATE TABLE T1.monthly
(
SEQ_NUM NUMBER(10) NOT NULL,
DAY_DK NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
[code]........
some sample data
SEQ_NUM DAY_DK
---------- ----------
990 20121225
991 20121225
992 20121225
993 20121225
994 20121225
995 20121225
996 20121225
997 20121225
998 20121225
999 20121225
When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.
desc t1.mth; ### my temorary table
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQ_NUM NUMBER(10)
DAY_DK NUMBER(10)
Than when I try to alter my temp table "mth". I get an error table must be empty to change column types.
alter table n546830.mth modify (DAY_DK date);
Next I tried making my temporary table "mth" a date column. When I an the exchange partition command I get the following error:
alter table t1.monthly exchange partition DEC_2012
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Method I can use to convert a number(10) to date column and keep the information in a table. Note, I don't care about HH:MM:SS as I never had that information to begin with and would be happy to set that part of the date column to all zeroes "00:00:00"
View 12 Replies
View Related
Feb 20, 2013
i know the procedure "DBMS_SQL.DESCRIBE_COLUMNS" and the example,the example uses the "dbms_sql.open_cursor",not the ref cursor. so how to get the information of ref cursor by using describe_columns? or how to get the information of ref cursor by other?
View 4 Replies
View Related
Sep 1, 2012
So, for lack of a better way to explain it, I need to do the following.
I need a EXECUTE IMMEDIATE to be able to use a CURSOR but its saying its not declared. Is their a way to pass the cursor so a Execute Immediate can use it...
V_SQL := 'REPLACE(V_DATA,'TEST',CUR_DATA.'||V_FIELD||');';
EXECUTE IMMEDIATE (V_SQL);
I know thats just a small part... but it shows the end goal. I need to append a VARCHAR that has the COLUMN NAME I need from the CURSOR. CUR_DATA is the open cursor. V_DATA is just a VARCHAR with text in it.
View 6 Replies
View Related
Jul 25, 2013
I have the following piece of
CREATE OR REPLACE PROCEDURE COMP_RECORDS
IS
l_query VARCHAR2 (10000) := '';
CURSOR TBL1
IS
SELECT TABLE_NAME, COLUMN_NAME FROM COLS_TO_COMP WHERE TABLE_NAME='ACE_HIST';
TBL1_REC APP.COLS_TO_COMP%rowtype;
[Code]..
However I am getting an ORA-00923 exception with message as "FROM keyword not found where expected". know if I can/cannot use a cursor to fetch column names for a table?
View 9 Replies
View Related
Apr 15, 2013
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
View 2 Replies
View Related
Aug 8, 2012
How can we pass column index or column name in cursor for loop.
View 3 Replies
View Related
Sep 21, 2010
i have a proc that is taking p_serial_number refsursor as in parameter. the structure of p_serial_number is required to be
mfg_prod_cdchar (3 byte)
mfg_prod_seq_no char (6 byte)
How do I need to define this ref cursor ? and when I use it in my Procedure how do I fetch the column values ?
View 6 Replies
View Related
Apr 29, 2010
i want to produce a function who return XmlType element if she checks that a parameter (that i passed to here is contained in a row in a defined table)
for example
create table sec_form(
form_code VARCHAR2(8)
, sec_code VARCHAR2(5)
) ;
insert into sec_form values ('CSA','section1');
insert into sec_form values ('CSA','section2');
insert into sec_form values ('CSA','section3');
then i got my function
create or replace function checkIfSectionEnabled (elem XmlType,name_section varchar2)
return XmlType
is v XmlType;
CURSOR myCursor IS select * from sec_form where form_code = 'CSA';
begin
open myCursor;
loop
[code]....
now i'm gonna call the function (elem XmlType,name_section varchar2) and pass here "section1" for example ; and she must return me my xmltype element if she find "section1" finds section1 present;
when i try t compile i got:
Error(18,13): PL/SQL: Statement ignored
Error(18,26): PLS-00225: subprogram or cursor 'MYCURSOR' reference is out of scope
View 3 Replies
View Related
