473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Oracle Cursor Problem

Hi All,

I have this strange problem. I am user of two different database (both are same version 9i). I created a procedure and within this procedure i used a cursor to select the values, this is working very fine.

When i copy this procedure in other database and try to run the procedure, the procedure runs successful, but the cursor does not populate the values, when i run the query written in the cursor, query returns the correct values.

Another interesting thing is that when i run the cursor not within procedure it runs successfully but when i try to use within procedure it does not work.

Another thing is that if i run the same procedure with cursor but omitting the where clause the procedure works fine again, but with where it does not populate the values in the cursor and cursor row count is = 0.

Anyone have the answer please.


CREATE OR REPLACE PROCEDURE proc_dps_check
as
PM_TABLE_NAMES VARCHAR2 (50);

CURSOR pmC IS SELECT LOWER(RTRIM(LTRIM(tname))) AS v_TABLENAME FROM dps_prc;-- WHERE LOWER(RTRIM(LTRIM(tname))) IN (SELECT LOWER(RTRIM(LTRIM(TABLE_NAME))) FROM ALL_TABLES WHERE OWNER = 'TEST');

BEGIN

FOR I IN PMC LOOP
Dbms_Output.PUT_LINE(I.v_TABLENAME);
--Code goes here...
--COMMIT;
END LOOP;
END;


Thanks.
Jun 11 '07 #1
2 3853
HI ALL,

I HAVE FOUND THE PROBLEM, AND IM RESOLVING IT. THANKS


Hi All,

I have this strange problem. I am user of two different database (both are same version 9i). I created a procedure and within this procedure i used a cursor to select the values, this is working very fine.

When i copy this procedure in other database and try to run the procedure, the procedure runs successful, but the cursor does not populate the values, when i run the query written in the cursor, query returns the correct values.

Another interesting thing is that when i run the cursor not within procedure it runs successfully but when i try to use within procedure it does not work.

Another thing is that if i run the same procedure with cursor but omitting the where clause the procedure works fine again, but with where it does not populate the values in the cursor and cursor row count is = 0.

Anyone have the answer please.


CREATE OR REPLACE PROCEDURE proc_dps_check
as
PM_TABLE_NAMES VARCHAR2 (50);

CURSOR pmC IS SELECT LOWER(RTRIM(LTRIM(tname))) AS v_TABLENAME FROM dps_prc;-- WHERE LOWER(RTRIM(LTRIM(tname))) IN (SELECT LOWER(RTRIM(LTRIM(TABLE_NAME))) FROM ALL_TABLES WHERE OWNER = 'TEST');

BEGIN

FOR I IN PMC LOOP
Dbms_Output.PUT_LINE(I.v_TABLENAME);
--Code goes here...
--COMMIT;
END LOOP;
END;


Thanks.
Jun 11 '07 #2
debasisdas
8,127 Expert 4TB
Hi
BilalGhazi
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow the posting guidelines in every new post/reply.
Jun 11 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

13
by: Chris Botha | last post by:
The machine is running XP Pro with all the latest service packs, etc. I must access an Oracle database so I installed the Oracle client stuff. I can query Oracle from a Windows app, no problem....
2
by: André Nobre | last post by:
I don't know if this is the right place to make this question, so, if isn't, let me know... I have an oracle package with some procedures, and i need to access one procedure using vb.net. The...
6
by: JV | last post by:
A ref cursor data type can obviously be returned as an output parameter of a stored procedure, but can an ASP.NET application call an oracle proc that uses a ref cursor as an input parameter? If...
14
by: peteh | last post by:
Hi All; We have many production jobs that "load from cursor" to a UDB/AIX 8.2 (with dpf) data warehouse from source tables residing Oracle 9i. Since Oracle dates are (roughly) equivalent to DB2...
0
debasisdas
by: debasisdas | last post by:
RESTRICTIONS ON CURSOR VARIABLES ================================= Currently, cursor variables are subject to the following restrictions: Cannot declare cursor variables in a package spec. ...
2
by: Vinod Sadanandan | last post by:
All, Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features . Precompilers:...
0
debasisdas
by: debasisdas | last post by:
SAMPLE CODE TO SHOW USE OF REFCURSOR ======================================= EXAMPLE #1 ---------------------- declare --declare the fer cursor. type my_ref_cur_typ is ref cursor; --declare...
0
debasisdas
by: debasisdas | last post by:
Cursor Variable Returning %ROWTYPE ----------------------------------------------------------- DECLARE TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; tmp_cv TmpCurTyp;TYPE EmpCurTyp IS REF...
6
by: t_rectenwald | last post by:
Hello, I attempting to execute an Oracle query, and write the results to a file in CSV format. To do so, I've done the following: import cx_Oracle db = cx_Oracle.connect('user/pass@DBSID')...
0
by: tickle | last post by:
Need to convert this PL/SQL script to Dynamic SQL Method 2 * copybook - celg02u3.sql SIR 24265 * * updates dt_deny for all rows in * * ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.