DECLARE
TYPE first_rec_1 is RECORD( var1 varchar2(30) := 'why not');
first_rec first_rec_1;
TYPE SEC_REC IS RECORD( NESTED_REC FIRST_REC_1 := first_rec);
BEGIN
dbms_output.put_line( ' this is a test for checking nested records'||FIRST_REC.var1);
END;
In the above code what NESTED_REC FIRST_REC_1 := first_rec means. Will NESTED_REC variable get the values of first_rec?
how to swap two values without using any other variable?
example:
v_a varchar2(10):=INDIAN;
v_b varchar2(10):='PHOTON';
so,how to swap these two values..
There are 2 people on separate machines each executing a transaction through the same form, processed through a when-button-pressed trigger.The first session processed correctly. For the second user, the session seems to have picked up the non-PACKAGE variables of the first session in what was passed through to the data base. Values associated with the 2nd session's PACKAGE-based types appear to have passed through correctly.
Hence, the second user's transaction processed with a combination of values from the two sessions, with the second user's PACKAGE-based variables merged with the first user's non-PACKAGE variables. There is no use of context variables. There are some global values, but none of them are used in this trigger.The values in question, that appear to have passed from the first session to the second, are based on contextual LOV selection: after selecting a transaction type, users are prompted to select from a LoV specific to that type. Value property set "Validate from List=>Yes".
The 2nd session's PACKAGE-based values do not correlate to the non-PACKAGE values, leading us to conclude that the latter values somehow came through from the first session. We are running IAS 10g R2 on Oracle 10gR2 (10.2.0.3).Each user session is created as user logs into the application and hence logically there should not be any overlap between sessions of different users concurrently.
I did have a look at the code and predict nothin wrong with the source code since the system has been in use for a few years now and only occurred a couple of times in last few months. Also one more noticeable thing is that the issue is not reproducible. I would believe somethin goin wrong in the middle tear or with the session management. Are there any known issues in session management in the Forms server Or something?
need to dynamically assign value of each column of a row type variable:The example is like that:
Table "Student_list" is as follows:
---------
COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50)
[code]....
Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?
I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :
CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER
I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:
CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies View RelatedI have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies View RelatedI created a package with some types, and every was compiling fine. However, when I ran the new function, I got an error: ORA-21700: object does not exist or is marked for delete
After a little research, I realized that the types would have to be declared outside the package. As soon as I did that, I suddenly started getting the "not enough values" errors on all my types. I compared the number of columns being returned, and the number of columns in the type, and they match.Here is my type code:
CREATE OR REPLACE TYPE TSA_CUSTOM.Lost_Plan as object (
LP_Key number, -- The member key of the plan that is going away
LP_Type varchar2(20),
LP_Dept varchar2(12),
LP_SubDept varchar2(12),
LP_Class varchar2(12),
LP_VendorName varchar2(50)
[code]...
How do I set variables at the top of my code? I want to set the date as the variable.
Select * from employees where employee_dt > '2011-01-01'
it is possible to undeclared a variable if so how dont worry am with you to solve any problem lets we can do it
View 1 Replies View RelatedI have written an explicit cursor (procedure given below) and the issue I have is, when the cursor runs the sql statement
(CURSOR csr_address is
SELECT rtrn_id,
entp_abn,prog_program_cd,
sched_nbr,schd_version_yr,
litm_line_item_nbr, revise_val_text
FROM RETURN_LINE_ITEMS
WHERE sched_nbr = '000'
AND prog_program_cd = '01' AND litm_line_item_nbr in ('016','023')
AND rtrn_id = v1_rtrn_id;)
against a particular return id, it fetches 2 rows; one for line item 016 and the other one for line item 023 where in the litm_line_item_nbr for 016 is 016 and for litm_line_item_nbr for 023 is 023. Once that's done, (I have used a For loop cursor), it loops through as follows:
FOR country_rec in csr_address LOOP
v_line_item_16 := country_rec.litm_line_item_nbr;
if v_line_item_16 = '016' then
v_line_item_16 :='016'
end if;
[code]....
I have the below cursor 1 which is working already.For my requirement i want to use bind variable like second cursor.But its telling Bind Variable "p_col_list" is NOT DECLARED.
How to use bind variable Here.
Cursor1:
DECLARE
emp_cv sys_refcursor;
iid NUMBER := 1;
i_sql varchar2(100);
p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd';
BEGIN
i_sql := 'select '''||REPLACE(p_col_list, ',', ''',''')||''' from dual '||CHR(10) ;
dbms_output.put_line(i_sql);
OPEN emp_cv FOR i_sql ;
END;
Cursor2:
DECLARE
emp_cv sys_refcursor;
iid NUMBER := 1;
i_sql varchar2(100);
p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd';
BEGIN
i_sql := 'select '''||REPLACE(:p_col_list, ',', ''',''')||''' from dual '||CHR(10) ;
dbms_output.put_line(i_sql);
OPEN emp_cv FOR i_sql using p_col_list;
END;
I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:
<APPLICANT app_no="1">
<APPLSUMMARY>
<MAIN W="ZZ" X="{ND}"/>
<COUNTS Z="3" AB="0" BB="3" CB="0" DB="3" EB="3" FB="3" GB="0"/>
</APPLSUMMARY>
</APPLICANT>
I would like to be able to return a new row for each variable, For example:
VARIABLE | VALUE
----------------
W | ZZ
X | {ND}
Z | 3
And so on.
I have plsql table as out parameter in one function.From another function i called that function ,its executing perfectly but while returning to called place its saying a character to numeric conversion error.I checked all the l
Note: i can not able post all that functions here.
Can I assign the outcome of a select query to a defined variable
like
var=select emp id from emp where empname='ddf'
create view sample as
var varchar(100);
var:= select * from employee;
execute immediate var;
can we create like this ... my requirement is like this...
Is there a way to have a variable in the FROM clause or another way to have the table name as a variable? I'm trying to not repeat the same query three times with only the table name being different.
---the tables
create table org_a (emp_id number(5) not null, name varchar2(20));
create table org_b (emp_id number(5) not null, name varchar2(20));
create table org_c (emp_id number(5) not null, name varchar2(20));
---the records
INSERT ALL
INTO org_a (emp_id, name) VALUES (00001, 'MISTER WHITE')
INTO org_a (emp_id, name) VALUES (00002, 'MISTER ORANGE')
INTO org_b (emp_id, name) VALUES (00003, 'MISTER PINK')
INTO org_b (emp_id, name) VALUES (00004, 'MISTER BROWN')
INTO org_c (emp_id, name) VALUES (00005, 'MISTER BLUE')
INTO org_c (emp_id, name) VALUES (00006, 'MISTER BLOND')
SELECT * FROM dual;
---verify inserts
SELECT * FROM org_a
UNION ALL
SELECT * FROM org_b
UNION ALL
SELECT * FROM org_c;
---i want the table name to be dependent on a variable. eventually,
---i intend to link v_org to a form with radio buttons (values: 1, 2, 3)
---to keep this simple, i'll just assign 1 to v_org
DECLARE
v_org number(1) := 1;
v_table varchar2(5);
BEGIN
v_table :=
CASE v_org WHEN 1 THEN 'org_a'
WHEN 2 THEN 'org_b'
WHEN 3 THEN 'org_c'
END;
SELECT * FROM v_table;
END;
--this is what i receive
SQL> /
SELECT * FROM v_table;
*
ERROR at line 10:
ORA-06550: line 10, column 17:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored
I HAVE DECLARED A VARIABLE
VAR1 VARCHAR2(20000);
BUT STILL WHEN I ASSIGN SOME STRINGS TO THAT VARIABLE I GET "VALUE TOO LARGE" MESSAGE. WHAT SHOULD I DO?
reading the value from csv file and store into a variable and then use that variable in select statement in
filter. I have tried the following ways but it is now working.
File content is
first, second, third, forth
Y, N, 001|002, abc
N, Y, 003|004|005|006, xyz
n, Y, 007|008|009, mno
[code].....
I had a procedure in which there will be multiple update and select statements Eg: Update table T1 set Column1='X' where Column2 in ('A','B','C') All the update/select queries will have same set of values in the in condition ('A','B','C') will remain same. But the tables and columns will vary.So, I would like to declare a variable/array which holds the values in the in condition
var1 := {'A','B','C'}
and use in my statement like :-
Update table T1 set Column1='X' where Column2 in var1. Is there any way to acheive this?
I have a stocking program , i need to use opening balance + debit-credit
In the beginning of my cursor then what ever the result i need to but it in a variable to used for the next record; i try many time but i failed
my statement look like this
opening balance 1000
receive used balance
-----------------------------------------------
0 50 950
0 100 850
100 0 1850
I can't figure out why my variables are not filled up?
TEST CASE
CREATE TABLE LIST_STEP_LINK
(
FAL_SCHEDULE_STEP_ID NUMBER(12) ,
FAL_SCHEDULE_PLAN_ID NUMBER(12) ,
SCS_STEP_NUMBER NUMBER(9) ,
[Code]....
Result:
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> DECLARE
2 VAR_SCS_LONG_DESCR FAL_LIST_STEP_LINK.SCS_LONG_DESCR%type;
3 VAR_SCS_FREE_DESCR FAL_LIST_STEP_LINK.SCS_FREE_DESCR%type;
[Code]....
PL/SQL procedure successfully completed.
SQL>
how to achive this one.
declare
v1 varchar2(100);
begin
v1:='10,20';
[Code]....
Error at line 1 ORA-01722: invalid number ORA-06512: at line 9
find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
select * from tablename where column in (array_varaiable);
this is my question How can i use all the elements of array in the "in clause";
I'm currently looking for a way to declare a cursor in the 'declare' block using a previously defined variable that got its value from a query. For instance:
declare
my_company_id INTEGER := 'select c.company_id from company_table c where company_name='Wal-Mart';
cursor employees is
select e.employee_id from employees e where e.company_id = my_company_id;
Any way to do this?
I want to pass a variable in unix to oracle exp command
I have a file with owner and password
I did
export user=$(head -$riga $file | tail -1)
echo $user
let riga+=1
export psw=$(head -$riga $file | tail -1)
echo $psw
exp $user/$psw file=exp_$current.dmp
but this doesn't work.