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!
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.
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.
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;
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):
***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?
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"
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
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
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
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
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.
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;
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.
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
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,
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?
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.
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.
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;
