I am not an Oracle programmer but I have been given a task to produce a number of Text files from an Oracle table based on a selection from the table itself.The table consists of many records with a currency code and I need to extract the data into named files based on the currency code.My first idea was to use Cursors and try a select statement matching on the returned value of the cursor but no Joy I can't make it work - perhaps I need to take a new approach.
Current / latest attempt below
DECLARE
CURSOR tmp_splitter_cur IS
SELECT DISTINCT end_consumer_country
FROM tmp_splitter
WHERE 1 = 1;
currency_rec tmp_splitter_cur%ROWTYPE;
[code]....
I am an experienced SAS programmer jumping into PL/SQL for the first time and have already encountered a problem.Let's assume I have 7 records (shown below). (In reality, I have millions of records, but the concept's the same.) The Value field is only populated when it changes. Therefore, I am forced to "fill in the gaps" as I read the data file. It's fairly straightforward. I carry the value foward one record at a time, using it if the Value field in not populated. The ANSWER I want is also shown.
In reading through a PL/SQL book, I realized that only 2 chapters are relevant to what I do. My guess is this solution involves cursors; probably a Cursor FOR loop,
I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.
However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'...how to handle db_links using a cursor in a pl/sql block? The code is as follows:
DECLARE db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS SELECT DB_LINK from MESSAGING_PROD_LIST; [code]....
I am having a scenario where i have a ref cursor opening and fetching though dynamic sql query. And those values which i get from ref cursor i want to use them for other parameter cursor in for loop.
for example
PROCEDURE script ( p_account_no IN VARCHAR2, p_from_date IN DATE, p_to_date IN DATE, p_subledger_code IN VARCHAR2, p_posted IN VARCHAR2, v_alloc_unalloc IN OUT alloc_unalloc, -- ref cursor declared in package specification. [code]..........
declare cursor c_abc (v_eno in varchar2,v_ename in varchar2) is select empno,ename from emp where empno=v_eno and ename=v_ename; v_eno emp.empno%type; v_ename emp.ename%type; begin for re_ab in c_abc(1,'Dummy') loop dbms_output.put_line( to_char(re_ab.v_eno)||' - '|| re_ab.v_ename); end loop; end; /
I am getting error message ORA:06550 V_ENO must declared ..
set serveroutput on declare rec employees%rowtype; cur SYS_REFCURSOR; begin open cur for 'select * from employees where rownum<:a' using 4; for i in cur [code]....
It gave errors if we execute is as such, but worked when I commented out the for loop and instead de-commented the simple loop. Does that mean that FOR cannot be used to loop through the records of a ref cursor ?
I take a select into a cursor and process it record by record.I have to do sum based on a column and display row by row by using dbms_output.put_line .... So the sum has to happen based on a column. Based on the column value i need to display the cumulative sum as well.
Example:-
col1 col2 amount DL AADD 25 DL BBCC 10 DL BBRR 15
Sum value for DL ----- 50
TX ADED 20 TX EDWW 60
Sum value for TX ----- 80
All the above data should be displayed using DBMS_OUTPUT.PUT_LINE in a pl/sql code. I use cursor to take the values from the table but the problem i face is .... I am not able to display the sum based in the col1 values.
Since i use the cursor .. i took the col1 values in to a variable and checked every time old_variable = new_variable if yes then continue the sum else display the sum value.
once i get the above check satisfied i am loosing a new col1 row in the check. The next loop only run for the new col1 values -1( which is used in the check loop).So is there any better way to get the solution or is there a facility to store the previous loop values in a cursor ? so that i dont have to loose that one row of data.
I am not able to come up with proper loop so which can identify that the col1 has changed and you have to display the sum value.
I would like to exit from a cursor loop based on certain conditional checking. I am checking for a lot of different parameters and if they fail, I want to bypass it and fetch the next record in the cursor. I tried just putting an 'Exit' statement in the logic, but it fails. An example of my code is below:
For Row1 in cursor1 Loop If amount < 0 then balance := 0; Else
I'm running a PL/SQL with a For Loop cursor, but when trying to execute it doesn't run. It is as if there is no data, but I ran the cursor separately in a SQL Plus session and it runs perfectly. I'm enclosing the file with the procedure.
the problem we are facing is that we are having some problems with names in a filed which are not letters but strange symbols inserted by a webservice which by the way has been corrected, but we have the discrepancy on out DB, so I've decided to write a PL/SQL procedure to correct them.
Here is the example of an incorrect record.
4047254| STEVE; ROVINSON (THE INCORRECT SYMBOL IS CHR(32), I do not know why the forum doesn't show it)
So, if you look there's a symbol that is not interpreted by the DB and my original idea was to extract them and correct then with another procedure. I have originally wrote a procedure with the logic if there's a symbol that is not beween currect letters (from A to Z including semicolon (;)) extract them and that's it. So, the big question is, How I transfer the control from the second loop to the cursor loop in order to analice the next record.
here is the code which hasn't been completed yet.
DECLARE CURSOR get_nombre IS SELECT CNTA_NOM_ABRV FROM CUENTA;
SQL> show user USER is "ANDREY" SQL> SQL> SQL> SQL> --create the table:
[code]...
I insert rows into it:
SQL> --fill it with data: SQL> SQL> insert into a(key1 , key2) values (1 , 1); 1 row created. SQL> insert into a(key1 , key2) values (1 , 5);
[code]...
i want to perform a logic by which:for every distinct value of key1 - values of key2 will be checked in all records holding that particular key1 value, and update the key3 field to 'inactive' where the key2 value for that particular key1 is the highest in number.
i've found out that i could do it by an SQL statement:
update a set key3 = 'inactive' where key2 = ( select max(key2) from a a2 where a2.key1=a.key1 );
however I wanted to use the cursor to "load" the max key2 values FOR EACH distinct key1 value exists in the table,and do the same thing as the update statement above WITH A CURSOR,So tried and wrote the following:
SQL> create or replace procedure proc1 2 IS 3 4 5 var1 a.key1%type;
[code]...
unfortunately, it works only for one row, and i don't understand what's wrong, I executed, and checked what has changed:
SQL> exec proc1; PL/SQL procedure successfully completed. SQL> select * from a; KEY1 KEY2 KEY3 ---------- ---------- ---------- 1 1 active 1 5 incative 2 24 active 2 21 active
I loop through the 1st cursor (account_csr), while in the 1st csr loop, based on some conditions being true, I want to loop through a 2nd cursor (acctper_csr) but I only want to retrieve data/rows in the 2nd cursor where the account_id column in 1st cursor = account_id column in the 2nd cursor. This will enable me to pull all the account_periods for each account I loop through in the first cursor.
I have attempted several different ways and cannot make this work. Thought I could somehow define a variable and store the account_id from 1st cursor and use on the 'where' clause in the 2nd cursor definition. Have not been able to make this work successfully.
Following is the sample of my
--First cursor (accounts) CURSOR account_csr is SELECT * FROM s_dev_xref1.account A WHERE a.source = 1
Oracle Version: 11.2.0.2.0. I have two explicit cursors and I would like to choose at run time which one to run. Here is a simplified code snippet of what I am doing today:
DECLARE CURSOR Cursor_A IS SELECT * FROM EMP_A; CURSOR Cursor_B IS SELECT * FROM EMP_B; RUNA CHAR(1) := 'Y';
[code]....
I want to avoid maintaining the same long list of transformations. I also want to avoid, if possible, an explicit FETCH INTO, because there are hundreds of fields in both tables. I'm looking for something like this (and I know this doesnt work):
DECLARE CURSOR Cursor_A IS SELECT * FROM EMP_A; CURSOR Cursor_B IS SELECT * FROM EMP_B; RUNA CHAR(1) := 'Y'; CursorToRun IS REF CURSOR;
We have a fact table t1 in the warehouse which has above 6 million records.There is to be an update like this where t2 has aid+bid as composite primary key. column aid repeats in t1.There's performance problem and we'v been told to break this huge update into pieces with few commits in the middle.
update t1 set t1.aid = (select t2.aid from t2 where t1.bid = t2.bid )
I've tried cursor loop with 3 commits in the middle based on if condition that evaluates on every iteration.
I have a plsql block construct where i want to use for loop dynamically , the query which for cursor for for loop will accept the table name from parameter and join them to return the result. the resultant data will iterate in loop and do the execution.
i HAVE THE FOLLOWING CODE WRITTEN IN A *.pc FILE. I am trying to loop to fetch data from cursor. But the code exist after it fetches the first record. Let me know what is it the right way to fetch data from cursor?
EXEC SQL BEGIN DECLARE SECTION;
char str[64];
EXEC SQL END DECLARE SECTION;
/*cursor declarations*/ EXEC SQL DECLARE Get_SQLText_Cursor CURSOR FOR
i m using oracle 10g 10.2.0.2 version.i create a form and using check box on this form.when i click this check box then loop is using behind it.and current cursor is going to last record
i want if i click 4 record then cursor is still showing on 4 record mean i click which record after using loopmy current cursor is showing on that particular record
I can work with 'straight' query's etc., but now I want to make a query with a loop. I have made a simple one to demonstrate what I want but the real one is working is working by it self but I want to get it work with a loop.
This is the simpel version:
DECLARE
P_UID NUMBER; Max_UID NUMBER := 42220;
BEGIN
P_UID := 42210; LOOP select * from contract lc where lc.UIDCONTRACT = P_UID;
P_UID := P_UID + 1; EXIT WHEN P_UID > Max_UID; END LOOP; END;
The error I get is:
Line 10, column 9:
PLS-00428: an INTO clause is expected in this SELECT Statement.
So I know you can declare a variable and then CONTRACTID INTO v_contractID.
But if I have to put every field in a variable what is then the advantage of a loop. So I expect that I'm on the wrong road and not understand how this works.
Select name from names_list where name like ('A%')
The output returns a list of names beginning with 'A'...What I want is for the output to repeat that list of names multiple times e.g. if the only two names returned are 'Andrews' and 'Apple', I want to have the output show
I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below
CURSOR c_total_bookings IS SELECT personnel_id, surname, forenames, birth_date,
[code]...
What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.
I've been examining som old queries in an existing db due to more and more problems regarding performance. The sql is used as backend for a java/jboss web application with the possibility for users to enter data. With more and more data, there starting to come complaints about the performance.
I stumbled upon a select query with an embedded cursor similar to this :
select id, name ..., cursor(select id, sequence.... from table2), cursor(select id, name.... from table3) from table1 join table4 on (table1.id = table4.id) where .....
The javacode is a prepared statement with the actual sql as a string and the content of the cursors saved in conjunction with each row.
when i use sqldeveloper to show the explain plan without the cursors, the cost is 2428 when i use sqldeveloper to show the explain plan with just 1 of the cursors, the cost is ~165000
Is there a better way to do this instead of cursors ?
I am trying to insert 100K rows, I have written this proc using cursor. But it is saying anonymous block completed, and no rows are inserted. If I just run the select it returns the rows.Could if just insert into select would be fine or should I use cursor.
CREATE OR replace PROCEDURE Insert_data (l_from_date IN VARCHAR2, l_to_date IN VARCHAR2) IS lc_err_msg VARCHAR2 (2000); ln_count NUMBER := 0; CURSOR ins_d IS SELECT a.col1 AS url, b.col1 AS ref_url, COUNT (*) AS total_views [code]....
