SQL & PL/SQL :: Variable Value Not Displaying Inside Loop
Jul 8, 2011
I have below peice of SQL,Im trying to form the P_attribute1 value by using the Loop index(i) but the P_attribute1 value which i assigned in declaration not displaying in the output.
DECLARE P_attribute1 VARCHAR2(100) := '110000027';
P_attribute VARCHAR2(100);
l_cnt NUMBER := 10;
v_buffer VARCHAR2(500);
v_buffer1 VARCHAR2(500);
BEGIN
FOR i IN 1 .. l_cnt
LOOP
v_buffer := v_buffer || '' || 'P_attribute' || i || '' || '!';
END LOOP;
dbms_output.put_line('v_buffer :' || v_buffer);
END;
v_buffer :P_attribute1!P_attribute2!P_attribute3!P_attribute4!P_attribute5!P_attribute6!P_attribute7!P_attribute8!P_attribute9!P_attribute10!
View 6 Replies
ADVERTISEMENT
Sep 11, 2012
I have a cursor returning some value.
for each value returned by the cursor i need to traverse through 31 rows(1 row per day * no of days in the month).
E.g. if cursor returns service_name as xyz then for xyz there can be 31 rows(service may not be used on some days)
I need to go to all of them and take some values and move them to a flat file. how should that be done?
Attached File(s)
Query.png ( 20.99K )
Number of downloads: 9
View 1 Replies
View Related
Jan 6, 2011
I have a dynamic query which i want to run till it return zero records.
I am using WHILE loop for that but it is giving compilation error:
The query is
execute immediate ' Delete from tbl_archive_trade_list
where deal_id in (
select deal_id from tbl_archive_trade_list where trade_id in (
select trade_id from ' || main_trade_group_table || ' where tradegroup_id in (
select tradegroup_id from ' || main_trade_group_table || ' a , tbl_archive_trade_list b
[Code]...
I want to run this Query in While loop till the above command return 0 records.
I tried giving the above statement inside WHILE loop but it is failing.
Without the WHILE loop the above statement works fine and executed properly.
View 5 Replies
View Related
Nov 3, 2011
i am trying to do something the following .. but I can't get the syntax correctly for the select statement inside the secondary_loop ...
EmailBodyHTML := '';
main_loop := '';
secondary_loop := '';
[Code]....
View 4 Replies
View Related
Mar 8, 2012
I have a select..bulk collect into clause which is inside a for loop as the query gets a parameter from the loop, Then, how to extend the collection rows for each iteration.
My collection is of a sql object type.
View 5 Replies
View Related
Nov 25, 2012
can we place insert statement in loop inside anonymous block?
CREATE TABLE DEP(DEPTID NUMBER(5) NOT NULL PRIMARY KEY,DNAME VARCHAR2(10),LOCID VARCHAR2(10));
DECLARE
I NUMBER(5);
BEGIN
I := 0;
LOOP
INSERT INTO DEP VALUES(&DEPTID,'&DNAME',&LOCID);
I := I+1;
EXIT WHEN I = 5;
END LOOP;
END;
View 6 Replies
View Related
Jul 1, 2013
I am trying to create an anonymous PL/SQL block to output privilege information for each of the users listed in DBA_USERS In a loop. This is my block so far (not finished):
declare
v_usr varchar2(30);
v_out_header varchar2(100);
[Code]....
The output is as follows:
***User-Role Privilege report***
-----------------------------------
username: ANDREY , profile: DEFAULT
SYSTEM privileges granted directly to the user(not through ROLE) :
no_data_found
A problem I am encountering is that for some users I have no direct privileges that are not granted through roles, And when I have the expression v_qry (which is basically "'select grantee ||'',''|| privilege from DBA_SYS_PRIVS where grantee not in (select role from dba_roles) and grantee ='||'''' ||v_usr||''''") not initialized with values because the select statement retrieved 0 results, I have the process interfered by the no_data_found error/exception.
Questions: how I can preferrably simply, avoid/overcome my problem? Some way to make the loop go on in spite of no data found? maybe something similar to NVL?
View 14 Replies
View Related
Mar 24, 2013
i want to put a variable inside a view. create or replace view loading as
SELECT H.ORGANIZATION_ID ORG_ID,H.HEADER_ID,H.REQUEST_NUMBER, H.DATE_REQUIRED ISSUE_DATE,H.ATTRIBUTE1,
H.ORGANIZATION_ID,L.INVENTORY_ITEM_ID,SS.SALES_ID,S.SECONDARY_INVENTORY_NAME SUB_INV_CODE,S.DESCRIPTION SALESPERSON,I.SEGMENT1 ITEM_CODE,
[code]....
i want this condition to be like and to_date(H.DATE_REQUIRED,'dd-mm-rrrr') = to_date('Vdate','dd-mm-rrrr') i want Vdate as a variable parameter.
View 2 Replies
View Related
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
Nov 24, 2010
I am calling a select query inside a procedure but i need to set environment variable 'set linesize 200' inside that procedure but i am not able to create the procedure due to some error. I am attaching the procedure query here with:
before the select query i need to insert this environment variable : "set linesize 200"
create or replace
procedure TABLESPACE_USAGE
is
l_mailhost VARCHAR2(64) := 'ip address';
l_from VARCHAR2(64) := 'email id';
l_subject VARCHAR2(64) := 'TABLESPACE_USAGE1';
l_to VARCHAR2(128) := 'email id';
[code]......
View 2 Replies
View Related
Jun 18, 2013
I have two procedure , from first procedure having some ref cursor output.
from second procedure I need to call first procedure and i need to process ref cursor output from first procedure so I decide to use bind variable to process ref cursor output but it showing error .
can I define bind variable inside the procedure , then how can I define it .
SQL> CREATE OR REPLACE PROCEDURE emp_by_job (
2 p_job VARCHAR2,
3 p_emp_refcur OUT SYS_REFCURSOR
4 )
5 IS
6 BEGIN
[code].....
View 10 Replies
View Related
Sep 11, 2012
I want to declare global variable inside package. get the correct query. how to assign value for that variable.
SQL> create or replace
2 PACKAGE new_pack
3 AS
4 g_id employee_details1.employee_id%type;
5 PROCEDURE emp_project(
6 st_date IN DATE,
7 Prj_id out VARCHAR2,
8 prj_name out VARCHAR2,
9 Prj_location out VARCHAR2);
10
11 END new_pack;
12 /
Package created.
SQL> CREATE OR REPLACE
2 PACKAGE body new_pack
3 AS
4 PROCEDURE emp_project(
5 st_date IN DATE,
6 Prj_id OUT VARCHAR2,
7 prj_name OUT VARCHAR2,
8 Prj_location OUT VARCHAR2)
[Code] ..........
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY NEW_PACK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/12 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar
View 10 Replies
View Related
Apr 22, 2010
All I want to do is run a procedure (DO_H_RUN) that returns a number into "v1" and then use that variable to return the result sets in three selects. This is all in TOAD. If I do not have a where clause, the code executes fine (allowing me to see the data in multiple Grid tabs)! But, I want to filter the rows.
I've tried doing a number of different things (finding all sorts of stuff on the Web) like declaring cursor variables and the like but after spinning on this for a couple of hours, I'm stuck.
Here's my attempt (the names have been changed to protect the innocent!):
declare
v1 Numeric;
BEGIN
DO_H_RUN('Me', v1);
--DBMS_OUTPUT.PUT_LINE(v1); --This line works okay!
select * from h_run where h_run_id=v1; --NO GO
[code]....
View 10 Replies
View Related
Sep 30, 2010
Where I am copying the data from one database to another I am getting this error.
ORA-01458: invalid length inside variable character string
This is applicable for only character string.
The source Database is
Oracle9i Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
The destination database is
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production
View 4 Replies
View Related
Mar 6, 2012
i want to store all rows of columns into single variable and then use in inside of SP
declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....
it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g
View 4 Replies
View Related
Apr 16, 2009
I want to insert data into a RAW column in a table. The below code snippet is used for the same
OCIRaw **l_rawPtr = new OCIRaw*[10];
for(int i=0;i<10;i++)
{l_rawPtr[i] = NULL; }
int max = 0;
for(int i=0;i<10;i++)
{
OCIRawAssignBytes(l_environment, l_error, l_char, strlen((const char*)l_char), &l_rawPtr[i])
[code].....
When the statement is getting executed, i am getting the following error. Error - ORA-01458: invalid length inside variable character string
View 3 Replies
View Related
Mar 29, 2013
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1
AS OBJECT
(
SYSTEM_IDENTIFER VARCHAR2(50),
PROCESS_TYPE VARCHAR2(50),
abc typeObj2
)
/
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
I passed the values like this in proc execution.
T_T_A_I_V := typeObj3('asdasd',NULL,NULL),
typeObj3('String654',NULL,NULL),
typeObj3('abcdef',NULL,NULL));
T_T_A_I_I := typeObj2('CampusCode',T_T_A_I_V),
typeObj2('PlanNumber',T_T_A_I_V);
What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct
View 4 Replies
View Related
Nov 19, 2012
CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;
can i use forall instead of 'for loop ..end loop'
View 10 Replies
View Related
Oct 10, 2013
My data is like this.
PERSON_IDEMPNOFULL_NAMEREGIONSEGMENTAmountFor_qtrFor_year237898270KirtikarWESTEnterprise100000Q1 2012237898720KirtikarWESTEnterprise310000Q12013237898720KirtikarWESTEnterprise400000Q22013237898720KirtikarWESTEnterprise2500000Q32013237898720KirtikarWESTEnterprise520000Q42014
Required Output:
PERSON_IDEMPNOFULL_NAMEREGIONSEGMENTQ1_AMTQ2_AMTQ3_AMTQ4_AMTYear237898720KirtikarWESTEnterprise31000040000025000005200002013-2014237898720Kirti
[code]......
View 11 Replies
View Related
Nov 29, 2012
I have a table in an Oracle db that has texts fields, int fields and a BLOB field. No issues with the table, data is correct and this works fine with crystal reports and sql developer as it does display the BLOB image properly.
We are migrating away from Crystal Reports to Cognos Report Studio 10.1. Cognos does not natively render this data type. So what can I do as a work around to make this work?
1. I thought that if I can get the DBA to put in the file location (Example: URL....) in an new field in the table; I can use HTML tag (<img src="insert URL here">) to render the image. I have tested this theory in another sql table in cognos and it works, but how do I find the location without going to the DBA's or do I really need to?
2. I thought maybe using java script or some other web code I can trick cognos to render the info...so far this has not worked.
View 1 Replies
View Related
Jun 18, 2007
I' m doing a query on multiple tables willing to get only top scorers from a certain round. Here's the relevant part of relation:
SOCCER_TEAM(TEAMID, NAME, CITY)
PLAYER (PLAYERID, NAME_SURNAME, DOB, TEAMID)
GAME_STATS(ROUNDID, GAMEID, TIME, PLAYERID, STATTYPE)
TIME is No between 1-90 representing the minute of the game
STATTYPE is IN('GOAL', 'OWN GOAL', 'RED', 'YELLOW')
Here's my sql code for the query:
SELECT ROUNDID, NAME_SURNAME, NAME, COUNT(STATTYPE)
FROM GAME_STATS, PLAYER, SOCCER_TEAM
WHERE PLAYER.PLAYERID IN (SELECT GAME_STATS.PLAYERID FROM GAME_STATS WHERE STATTYPE='GOAL' AND PLAYER.TEAMID = SOCCER_TEAM.TEAMID)
AND STATTYPE='GOAL'
AND GAME_STATS.PLAYERID = PLAYER.PLAYERID
GROUP BY ROUNDID, NAME_SURNAME, NAME
ORDER BY ROUNDID, COUNT(STATTYPE) DESC
This results in correctly displaying all scorers from all the rounds, yet I haven't been able to construct the HAVING clause to display ONLY the top scorers from each round (there can be multiple of them scoring equal top amount of goals and I need to show them all)
p.s. I have underlined primary keys, while foreign keys are in cursive, if it is of any relevance
View 5 Replies
View Related
Dec 27, 2010
I have a table which will the following type of data
"COL1""COL2"
1001"27-DEC-2010 02:00:00"
1002"27-DEC-2010 07:00:00"
1003"27-DEC-2010 09:00:00"
1004"27-DEC-2010 02:00:00"
1005"27-DEC-2010 12:00:00"
Here you can see that we have data for 27th Dec 2010 02,07,09 and 12 hours. I want a query which will show the full 24 hours data even if it doenst have any records. like the following,
COL1 COL2
0 2010122701
1001 2010122702
1004 2010122702
0 2010122703
0 2010122704
0 2010122705
0 2010122706
1002 2010122707
View 2 Replies
View Related
Sep 15, 2009
1-i have exported a database 9i with character set WE8MSWIN1252 which is her original character set;
2- i have created a new database 10G R2 with character set AL32UTF8;
3- i have imported the .dmp file to this new database with imp.exe 9i version configured with WE8MSWIN1252 character set;
-The problem is:
* when i use sqlplus, arabic data displays correctly but, in forms 10G, arabic data displays lick this ?E?? EC???IEC...... HOW CAN I CONFIGURE FORMS TO DISPLAY CORRECTLY ARABIC DATA?
View 3 Replies
View Related
Mar 18, 2013
writing the query, Output is shown below. facing problem in when converting columns into rows.
CREATE TABLE TESTTAB (IDNUM VARCHAR2(3),PLAN1 VARCHAR2(5),AMT1 NUMBER,PLAN2 VARCHAR2(5),AMT2 NUMBER,PLAN3 VARCHAR2(5),AMT3 NUMBER,PLAN4 VARCHAR2(5),AMT4 NUMBER,PLAN5 VARCHAR2(5),AMT5 NUMBER)
INSERT INTO TESTTAB VALUES('100','A01',9,'','','','','D04',300,'E05',900)
INSERT INTO TESTTAB VALUES('101','','','B02',56,'C03',24,'','','','')
INSERT INTO TESTTAB VALUES('102','A01',33,'','','C03',545,'','','E05',23)
[Code]...
My OUTPUT SHOULD BE LLIKE
IDNUM PLAN AMT
100 A01 9
100 D04 300
100 E05 900
101 B02 56
101 C03 24
102 A01 33
102 C03 545
102 E05 23
.
.
104 C03 44
View 5 Replies
View Related
Jan 20, 2013
In my explorer/menu form there is a image item, I want to display company logo in this image item therefore I am using read_ image_ file ('c:application LOGO.jpg','JPG','IMAGE53') in WHEN_NEW_FORM_INSTANCE trigger but image isn't showing.
View 3 Replies
View Related
Oct 17, 2004
I have problem in displaying icons in the push button during run time. Actually from few days i m trying to display icons at run time but with out any success.
i had made a simple push button and in its properties i had made the following changes
iconic=yes
icon filename=c:tempsave
the file save.GIF is saved under c:temp directory.
i had tried all the changes in registry.dat,orion file through the doc 203846.1 from metalink. Give me sample code step to step for showing a
push button with icon during run time. Also I tried the steps mentioned in the pdf document of
[URL].....
But it did not solve my problem. My icon file name is save.GIF , I wanted to know whether save.GIF file name convention is valid or not. Give the path from where I can get the icons used for web.
View 4 Replies
View Related
Mar 26, 2013
I just want to see single rows data as column.
select * from emp where rownum=1;
EMPNOENAMEJOBMGRHIREDATE SAL COMMDEPTNO
7369SMITHCLERK790217-DEC-80 00-00-00 800 20
How can it show as
column_name column_value
EMPNO 7369
ENAME SMITH
JOB CLERK and so on...
View 13 Replies
View Related
Feb 14, 2012
i got an error running my form. And when i re-run it again, the message at the bottom was nowhere to be found. How can i bring it back?
View 3 Replies
View Related
Aug 13, 2012
I'm trying to write procedures to make updating account owners and the like easier for a group of DBA's.
What I want to do, is create a procedure that displays the changes live.
e.g. If I changed the owner of 5 users from owner 100 to owner 200 it will display:
User test1 owner changed from 100 to 200
User test2 owner changed from 100 to 200
User test3 owner changed from 100 to 200
User test4 owner changed from 100 to 200
User test5 owner changed from 100 to 200
I can not get a loop to work to save my life. Here's what I have to update the account so far...
PROCEDURE UPDATE_OWNER (OWNER NUMBER, NEW_OWNER NUMBER) IS
BEGIN
UPDATE ACCOUNT_TRACKING
SET ACCOUNT_OWNER=NEW_OWNER WHERE ACCOUNT_OWNER = OWNER
AND ACCOUNT_TYPE !='P';
DBMS_OUTPUT.PUT_LINE ('Account Owner '||OWNER||' Changed to '||NEW_OWNER);
END UPDATE_OWNER;
View 4 Replies
View Related
Aug 2, 2010
I want to display time in reverse count down using oracle forms 10g.
View 3 Replies
View Related