SQL & PL/SQL :: Will NESTED_REC Variable Get Values Of First_rec

Apr 16, 2011

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?

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Swap Two Values Without Using Any Other Variable

Nov 29, 2011

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..

View 5 Replies View Related

Forms :: Session Variable Values Overwritten Between Transactions?

May 23, 2011

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?

View 5 Replies View Related

PL/SQL :: Dynamically Assign Values Of Any Column Of Row Type Variable

Nov 12, 2012

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?

View 7 Replies View Related

SQL & PL/SQL :: How To Get Left Padded Sequence Values In Variable To Insert It In Table For ID Creation

Feb 22, 2010

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]......

View 2 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

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 Related

Replacing Null Values Of Outer Join With Meaningful Values

Dec 3, 2010

I 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

View 3 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

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]...

View 9 Replies View Related

SQL & PL/SQL :: Ad Hoc MINUS - Compare Values In Code To Values In Table

Oct 28, 2013

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?

View 6 Replies View Related

PL/SQL :: How To Fetch Values From Two Columns Using Values From One Column

Jul 25, 2013

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 Related

PL/SQL :: ORA-00947 - Not Enough Values But Enough Values Are Being Returned

Dec 18, 2012

I 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]...

View 2 Replies View Related

SQL & PL/SQL :: How To Set Variable

Jun 12, 2012

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'

View 1 Replies View Related

Get Variable Declaration?

Apr 24, 2013

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 Related

SQL & PL/SQL :: Variable Losing Its Value

Jul 21, 2010

I 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]....

View 8 Replies View Related

SQL & PL/SQL :: How To Use Bind Variable

Jan 12, 2012

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;

View 2 Replies View Related

SQL & PL/SQL :: XML - Return New Row For Each Variable?

Oct 31, 2013

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.

View 2 Replies View Related

SQL & PL/SQL :: Table As Out Variable?

Mar 2, 2012

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.

View 12 Replies View Related

SQL & PL/SQL :: Assigning Value To A Variable?

Jun 14, 2011

Can I assign the outcome of a select query to a defined variable

like

var=select emp id from emp where empname='ddf'

View 5 Replies View Related

SQL & PL/SQL :: Variable In View

May 12, 2012

create view sample as

var varchar(100);
var:= select * from employee;

execute immediate var;

can we create like this ... my requirement is like this...

View 6 Replies View Related

SQL & PL/SQL :: Variable In The From Clause

May 8, 2013

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

View 22 Replies View Related

SQL & PL/SQL :: Value Too Large For Variable

Jan 21, 2011

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?

View 2 Replies View Related

SQL & PL/SQL :: Variable Value Is Not Being Recognize

Mar 27, 2013

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].....

View 8 Replies View Related

SQL & PL/SQL :: Variable Use In IN Condition?

Dec 5, 2010

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?

View 7 Replies View Related

SQL & PL/SQL :: Using Incremental Variable

Jan 20, 2011

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

View 3 Replies View Related

SQL & PL/SQL :: Variable Is NULL

May 21, 2013

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>

View 13 Replies View Related

SQL & PL/SQL :: In Clause With Variable

Jul 12, 2012

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

View 10 Replies View Related

SQL & PL/SQL :: Displaying Multiple Row Values As Multiple Column And Row Values

May 4, 2010

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

View 4 Replies View Related

How To Use Array Variable In (in Clause)

Jun 23, 2011

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";

View 4 Replies View Related

Declaring Cursors With A Variable

Jul 7, 2010

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?

View 4 Replies View Related

Passing Variable To Exp In UNIX?

Sep 20, 2013

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.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved