SQL & PL/SQL :: Dynamic Cursor / Loop Will Accept Table Name From Parameter
Oct 11, 2013
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 am running rep_1, and before executing second rep_2 the user should enter department number, after accepting department no, the second rep_2 should be executed. But after accepting parameter the second rep_2 is not displaying any record.
<h4><font color="Blue"> CREATE OR REPLACE PROCEDURE SP_SSN_BY_PIN( V_REQUESTEDPIN IN VARCHAR2, V_SSN OUT CHAR) AS BEGIN SELECT
[code]....
As you can see the type of the SSN column is NUMBER(13,0). But I leftpad it with 0 and assign it to my output paramter V_SSN, whose type is CHAR. But I get 111196100099 instead of 0111196100099. I've tried TO_CHAR(LPAD(SSN,13,'0')) but still doesn't work. However, if I return the left padded SSN inside a SYS_REFCURSOR I get what I want.
I am having a table with 4 columns as mentioned below
For a particular prod the value greater less than 5 should be rounded to 5 and value greater than 5 should be rounded to 10. And the rounded quantity should be adjusted with in a product starting with order by of rank with in a prod else leave it
I have taken all the records in to a cursor. Once after rounding the request of 1st rank and adjusting the values of next rank is done. Trying to round the value for 2nd rank as done for 1st rank. Its not taking the recently updated value(i,e adjusted value in rounding of 1st rank).
This is because of using a cursor having a value which is of old value. Is there any way to handle such scenario's where cursor records gets dynamically updated when a table record is updated.
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3 ORA-00932: inconsistent datatypes: expected NUMBER got CURSER ORA-06512: at "SYS.DBMS_SQL", line 1830 ORA-06512: at "TW.PRINT_REF_CURSOR", line 28 ORA-06512: at line 9
Here is my code: set serveroutput on exec DBMS_OUTPUT.ENABLE(1000000); declare l_cursor sys_refcursor; begin [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 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 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 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
So, for lack of a better way to explain it, I need to do the following.
I need a EXECUTE IMMEDIATE to be able to use a CURSOR but its saying its not declared. Is their a way to pass the cursor so a Execute Immediate can use it...
I know thats just a small part... but it shows the end goal. I need to append a VARCHAR that has the COLUMN NAME I need from the CURSOR. CUR_DATA is the open cursor. V_DATA is just a VARCHAR with text in it.
Is it possible to define a cursor using dynamic sql. Reason being is, I want to fetch data from 4 diffrent tables but dont want 4 diffrent cursors for this purpose.
I have a procedure that takes an in parameter . Cursor is declared in this procedure. Again is there a way to use dynamic sql so that this cursor declared in procedure uses all 4 table one at a time i.e cursor c1 is select * from table_name(I want this table name to be updated every time with new table name).
