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


ADVERTISEMENT

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

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

How To Concatenate A String With Variable

Mar 5, 2008

I am trying to replace the string_name in the following execute immediate statement with a concatenated

string_||v_variable.

execute immediate
'select
table_name
from
user_tables
where table_name = :a'
into v_table
using 'string_name';

I tried different declaration and concatenations but nothing worked so far.

View 2 Replies View Related

SQL & PL/SQL :: Table Variable Or Collection

Jul 11, 2012

Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.

View 7 Replies View Related

SQL & PL/SQL :: Have Multiple Variable In For Loop

Apr 5, 2010

Is it possible to have multiple variables in a for loop. For expample:

FOR i AND j in 1..10

I was wanting to increase one of the variable more than the other.

View 8 Replies View Related

SQL & PL/SQL :: Variable In Select Statement

Jul 5, 2011

I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:

SELECT
Location,
LEVEL,

FROM
S_ORG_EXT

where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'

in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.

this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.

FROM
S_ORG_EXT

where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'

View 7 Replies View Related

SQL & PL/SQL :: Using Collection Variable As OUT Parameter

Aug 8, 2012

The following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.

CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
[code].....

how to use collection variables as OUT parameters in procedure.

View 1 Replies View Related

SQL & PL/SQL :: Date Variable / IF Statement?

May 26, 2010

am new to oracle based coding and am having a heck of a time trying to figure this out. Basically i need to declare 2 date variables, Begin date and End Date.

I then need an if statement that says if begin date = 0 or is null then set it to the first day of the previous month. and if end date = 0 or is null then set it to the last day of the previous month.

So for today 5/26/2010 i would need

begin date = 4/1/2010
end date = 4/30/2010

I have searched and tried but so far to no avail.

View 10 Replies View Related

SQL & PL/SQL :: Initialize Variable With Whole Array

Dec 3, 2012

I'm having some hard times with attempts to initialize a set of values, Of a specific column from a table into a variable containing it as an array(like a single column table).

I've created & populated table TEST_TAB:

SQL>
SQL> CREATE TABLE "TEST_TAB"
2 ( "KEY1" NUMBER(10));

Table created.

SQL>
SQL> insert into "TEST_TAB" values ('1');

1 row created.

SQL> insert into "TEST_TAB" values ('2');

1 row created.

SQL> insert into "TEST_TAB" values ('3');

1 row created.

SQL> insert into "TEST_TAB" values ('4');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>

Then I am trying to compile a function that gets a parameter - a number. The function should check whether this number exists in this list(return 1), or not(return 0).

I want to initialize key1 column of table TEST_TAB *into* v_my_array(this will be my list), just to see that I can have a set of values in this variable. Then I will want to see if the number is in that list, and return 1 or 0 accordingly.

I get the following error compiling, and can't seem to understand what's wrong:

SQL> create or replace function is_num_in_list (p_number number)
2 return number
3 is
4 TYPE varray_type IS TABLE OF NUMBER(10) INDEX BY PLS_INTEGER;
5 v_my_array varray_type;
6 begin
7 select key1 into v_my_array from TEST_TAB;
8 --if the number provided exists in the list, then 1(TRUE)
9 if p_number in v_my_array then
10 return(1);
11 else
12 --if the number provided *IS NOT* in the list, then 0(FALSE)
13 return(0);
14 end if;
15 end;
16 /

Warning: Function created with compilation errors.

SQL> show error
Errors for FUNCTION IS_NUM_IN_LIST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/16 PLS-00103: Encountered the symbol "V_MY_ARRAY" when expecting one
of the following:
(
SQL>

View 2 Replies View Related

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

SQL & PL/SQL :: Bind Variable OLD Not Declared

Nov 28, 2011

I am facing the same problem: SP2-0552: Bind variable "OLD" not declared. When my script create_trigger.sql is executed,there is no error but when i execute it inside a pl/sql block it get above error...In the trigger we are using if conditions

if(:new.sumthing=1)and (:old.sumthing=0)the
do this..

View 1 Replies View Related

SQL & PL/SQL :: Formatting A Variable In Report?

Jun 1, 2011

I'm creating a report and wrapping the output line.

These are the variables I'm using

lv_actn_line_1 VARCHAR2(60);
lv_actn_line_2 VARCHAR2(60);
lv_actn_line_3 VARCHAR2(60);
lv_actn_line VARCHAR2 (192);
lv_rpt_ln_19 VARCHAR2(102);

I assign variable

lv_actn_line:=lv_actn_line_1||' '||lv_actn_line_2||' '||lv_actn_line_3;

And want to use it in output as below.

lv_rpt_ln_19 := edge_blank ||lv_actn_line;
osfile.put_line(v_fileid,lv_rpt_ln_19);
line_cnt := line_cnt+1;

How can I format the output so that lv_actn_line will wrap at character 102?

View 10 Replies View Related

SQL & PL/SQL :: Syntax To Convert Variable

Jan 23, 2011

I got a variale which passing by other problem to pl/sql colBuf="USER,NAME,LOC,DATE"

need to make it like this

tempcolBuf=||'^B'||bfary(i).user||'^B'||bfary(i).n ame||'^B'||bfary(i).loc||'^B'||bfary(i).date||'^B'

any command can use? or loop? get the solution by syntax...

View 14 Replies View Related

SQL & PL/SQL :: Substitution Variable In Sqlplus

Jul 20, 2011

Here is a simple sqlplus line command with & substitution run time variable.

set verify off;
SQL> SELECT * FROM emp where sal >&SAL;
Enter value for sal:

Now as you see, oracle asks to Enter Value for substitution variable in line just below the SQL>. I wonder if there is one or more line could be left there so to improve the readability. So it should look like:

set verify off;
SQL> SELECT * FROM emp where sal >&SAL;

Enter value for sal:

View 2 Replies View Related







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