Get Variable Declaration?
Apr 24, 2013it 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
ADVERTISEMENT
SQL & PL/SQL :: Variable Declaration In RAC Oracle 10g
Sep 29, 2010We are facing a new type of issue , the issue is the variable declaration and assigning a value for it is not properly used in the procedure of a package.
for ex:
create or replace procedure test
is
v_type VARCAHR2(5) := 'XX';
v_awb varchar2(5) := 'AL';
BEGIN
Insert into tt(col1,col2,col3,col4)
select v_type,v_awb,col3,col4 from xxx;
commit;
END;
Above is the sample procedure used ,here i am declaring a variable v_type and v_awb and assigning a value for it and inserting them into a table,but the concern is the values declared is always inserting a NULL value instead of the assigned value ,but in our local we could not reproduce it. The issue is occurring in RAC oracle machine,and the version is below
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Application Express :: 4.2 Translate (Function And Global Variable Declaration) Data?
May 1, 2013i 'm using APEX 4.2.1.00.08 and i 'm wondering if there is a way to translate "Function and Global Variable Declaration" textarea. Every other script textarea is available for translation, but not this.
I know that this is code is loaded on header but may contains important alert messages of global functions.
Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement
Aug 10, 2011HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
SQL & PL/SQL :: Parameterized Cursor Declaration
Apr 20, 2012I have two different tables with the same structure.
Depending on the parameter value to the procedure I need to process any one of the table .
declare
Query_string varchar2(500);
input_string varchar2(10) := 'E';
begin
Query_string := 'select * from ';
[Code]...
My intensition is like above snippet .But it's not working fine . If the input string 'E' then we need to process emp table , other wise archieve
SQL & PL/SQL :: Cursor Declaration Inside Block?
Jun 30, 2011I can't understand the following cursor declaration (inside the DECLARE of a PL/SQL block)
CURSOR c_emps IS
SELECT emp_large_ot(empno, ename, job, mgr,hiredate, sal, comm, deptno) FROM emp_large;
emp_large_ot is an object type created as
CREATE TYPE emp_large_ot AS OBJECT
( empno NUMBER
, ename VARCHAR2(10)
, job VARCHAR2(9)
[code]...
and emp_large is similar to the standard emp table
SQL & PL/SQL :: Hide Declaration Or Declare It Inside The Body Of Package
Mar 11, 2010I have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...
So I declared all these in the header
Like this
CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS
type table_name_i is varray(100) of VARCHAR2(30);
c_add constant number := 1;
c_del constant number := 2;
c_select_frm_tmp constant number := 1;
[code]......
Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?
PLS-00320 / Declaration Of Type Of Expression Is Incomplete Or Malformed
Jul 16, 2013I have this Error in PL /SQL procedure ORA-06550: line 6, column 12:PLS-00320: the declaration of the type of this expression is incomplete or malformedORA-06550: line 6, column 12:PL/SQL: Item ignoredwhere this is my procedure
CREATE OR REPLACE PROCEDURE MOAMALAT."IO_EMP_REP" (P_FROMDATE IN NUMBER,P_TODATE IN NUMBER,P_EMPID IN NUMBER,RCT1 OUT GLOBALPKG.RCT1)ASBEGINOPEN RCT1 FOR SELECT COUNT (I.CORRESPONDENCENUMBER) cont,EMP.FULLNAME empname,D.DEPARTMENTNAME deptnameFROM MOAMALAT.IO_INCOMING i,MOAMALAT.IO_EMPLOYEES emp,MOAMALAT.IO_DEPARTMENTS dWHERE I.RECEIVEDBY = (SELECT EM.USERIDFROM MOAMALAT.IO_EMPLOYEES emWHERE EM.EMPLOYEEID = P_EMPID)AND I.RECEIVEDBY LIKE EMP.USERID--and EMP.DEPARTMENTID=1900AND I.RECEIVEDBYDEPARTMENTID = D.DEPARTMENTIDAND I.CORRESPONDENCEDATE BETWEEN
[code]....
SQL & PL/SQL :: How To Set Variable
Jun 12, 2012How 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'
SQL & PL/SQL :: Variable Losing Its Value
Jul 21, 2010I 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]....
SQL & PL/SQL :: How To Use Bind Variable
Jan 12, 2012I 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;
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.
SQL & PL/SQL :: Table As Out Variable?
Mar 2, 2012I 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.
SQL & PL/SQL :: Assigning Value To A Variable?
Jun 14, 2011Can I assign the outcome of a select query to a defined variable
like
var=select emp id from emp where empname='ddf'
SQL & PL/SQL :: Variable In View
May 12, 2012create view sample as
var varchar(100);
var:= select * from employee;
execute immediate var;
can we create like this ... my requirement is like this...
SQL & PL/SQL :: Variable In The From Clause
May 8, 2013Is 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
SQL & PL/SQL :: Value Too Large For Variable
Jan 21, 2011I 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?
SQL & PL/SQL :: Variable Value Is Not Being Recognize
Mar 27, 2013reading 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].....
SQL & PL/SQL :: Variable Use In IN Condition?
Dec 5, 2010I 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?
SQL & PL/SQL :: Using Incremental Variable
Jan 20, 2011I 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
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>
SQL & PL/SQL :: In Clause With Variable
Jul 12, 2012how 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
How To Use Array Variable In (in Clause)
Jun 23, 2011select * from tablename where column in (array_varaiable);
this is my question How can i use all the elements of array in the "in clause";
Declaring Cursors With A Variable
Jul 7, 2010I'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?
Passing Variable To Exp In UNIX?
Sep 20, 2013I 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.
How To Concatenate A String With Variable
Mar 5, 2008I 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.
SQL & PL/SQL :: Table Variable Or Collection
Jul 11, 2012Is 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 RelatedSQL & PL/SQL :: Have Multiple Variable In For Loop
Apr 5, 2010Is 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.
SQL & PL/SQL :: Variable In Select Statement
Jul 5, 2011I'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'
SQL & PL/SQL :: Using Collection Variable As OUT Parameter
Aug 8, 2012The 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.