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

Loop Inside Cursor

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

SQL & PL/SQL :: Execute Immediate Inside A WHILE Loop

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

SQL & PL/SQL :: Query Inside For Loop

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

SQL & PL/SQL :: Bulk Collect Inside A Loop?

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

SQL & PL/SQL :: Place Insert Statement In Loop Inside Anonymous Block?

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

SQL & PL/SQL :: No Data Found Stops The Loop Action Inside Block?

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

SQL & PL/SQL :: Using Variable Inside A View

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

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 :: How To Set Environment Variable Inside Procedure

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

SQL & PL/SQL :: Bind Variable Inside Procedure?

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

SQL & PL/SQL :: Declaring Global Variable Inside Package?

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

Client Tools :: Passing Variable And Displaying Select Data In TOAD?

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

ORA-01458 - Invalid Length Inside Variable Character String

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

SQL & PL/SQL :: Store All Rows Of Columns Into Single Variable / Use In Inside Of Stored Procedure

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

Precompilers, OCI & OCCI :: ORA-01458 / Invalid Length Inside Variable Character String

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

PL/SQL :: How To Access Type Object Variable Declared Inside Another Type Object

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

PL/SQL :: Possible To Use Forall Instead Of For Loop - End Loop

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

PL/SQL :: Displaying Records In SQL

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

Displaying BLOBs In Cognos

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

Displaying Only Max Value Of The Count In Query

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

SQL & PL/SQL :: Displaying 24 Hours Data

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

Forms :: Displaying Arabic In 10g

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

SQL & PL/SQL :: Displaying Column As Rows

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

Forms :: Image Is Not Displaying

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

Forms :: Displaying Icons In Web

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

SQL & PL/SQL :: Displaying A Single Row Into Column?

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

Forms :: Message Is Not Displaying?

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

SQL & PL/SQL :: Updating Accounts And Displaying Changes?

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

Forms :: Displaying Time In 10g?

Aug 2, 2010

I want to display time in reverse count down using oracle forms 10g.

View 3 Replies View Related







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