how to print cursors? I mean like columnname : value. of couse curosr.columnname gives what I want. but I want genrally used way.
CREATE OR REPLACE PROCEDURE PROC_TEMP
IS
BEGIN
FOR C1 IN ( SELECT sbjct_id,sbjct_step
FROM RND.SBJCT_ORDR M) LOOP
DBMS_OUTPUT.PUT_LINE('c1 : '||c1);
END LOOP;
END;
how can i put button in a form to print the information i displayed in this form.
View 1 Replies View RelatedI have the following chunk of code, which could return no rows, one row or many rows. When it returns one or many rows the DBMS_OUTPUT.PUT_LINE prints out my values. When no data is found 'Hello' is not printed?
Suggest a way I can change the code to make the exception print when no data is found?
SET SERVEROUTPUT ON;
BEGIN
FOR prec IN ( select * from xxx.part_maint where drop_partition = 'Y' )
LOOP
DBMS_OUTPUT.PUT_LINE (prec.SCHEMA_NAME || ' ' || prec.OBJECT_NAME);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('hello');
END;
i have created a stored procedure with a cursor in order to perform a function where the annual_sal from the employee_annual_sal table is refered and checked. The empno for all the records which satisfies the condition mentioned inside the loop should be displayed in an variable. My code is below
create or replace PROCEDURE sp_test_cursor(out_empno OUT number)
IS
v_get_data number;
v_get_empno number;
cursor c1 is
select annual_salary
from employee_annual_sal;
[Code]...
What should i do to return mulitple values in a single variable??
My requirement is like as follows,
declare
v1str varchar2(100):='select empno,ename from emp';
v2str varchar2(100):='select empno,ename,sal from emp';
type t_array is varray(2) of varchar2(100);
[Code]....
So my problem is while executing the different sql statements by passing it to the procedure,how can the procedure would behave dynamically.It must be able to process all the sql statements.
The goal is to create a stored procedure that will retrieve multiple values from a table.
GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.
So I wrote the following procedure.
---------------------------------
create or replace
PROCEDURE emp_test(
c_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN c_cursor
FOR
SELECT emp_name, emp_doj, emp_role FROM emp_table ;
END;
---------------------------------
I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.
When I intend to run it by executing the script "Execute Procedure name ", I get errors.
In SQL Developer, I open new SQL file and key in
EXECUTE emp_test;
Highlight it and run the script, here is the list of errors that I get.
-------------------------------------------
Error starting at line 18 in command:
execute frm_lst
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'emp_test'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
--------------------------------------------
Issue 2:
Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work.
____________________________________________________
CREATE OR REPLACE PROCEDURE emp_test1
(e_name OUT emp_name%TYPE,
e_dob OUT Edob%TYPE)
IS
BEGIN
SELECT emp_nam, Edob
INTO e_name, e_dob
FROM emp_table
END emp_test1;
End;
______________________________________________________
Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.
i do have below query in oracle db just joining two views,
select docview.firstname as "First Name", docview.lastname as "Last Name", docview.mrn as "MRN", docview.physician as "Physician", docview.surgicalcong "Surgical Consent Missing", docview.admissionnotemissing "Admin Assessment Missing",
(case (docview.doctype) when 135 then 'Doctor Chart Checklist' else 'DS Chart Checklist' end) as doctypename,
docview.hpmissing "History & Physical Missing", docview.nursingassessment "Nursing Admission/Assessment",
[Code] ........
how can i make a procedure with below two steps,
1) load the result of above query to some temp table / or even a fixed table (which ever easier)
2) list all data from the temp table / fixed table created in first step
so in the final expecting result is
execute myprocnamehere -- will result same as running above query
I am trying to improve a procedure which is looping through a query to make inserts.
FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
[code]....
create a procedure so that I could get minus query of 2 table as a result.
I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.
so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.
select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)
create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..
Our goal is to create an light weight packaged application and provide to any customers who wants to install and use the tool.
Can an application with database be built on Apex and created as packaged installable, instead of hosting in any Application , middle and database server. A kind of quick, cost effective and light weight installation? If yes,
i've following code
declare
i INTEGER:=0;
flag boolean:= FALSE;
vcInsert:='INSERT INTO CRM_DELT_IMPORT (';
if length(i_vcColumn1) > 0 then
flag:=TRUE;
[code]....
i am fetching data from excel all data is of general type, here i am passing value is VTDATE varchar2(100) but actual database column filed is DATE , How can i pass '13-06-13 05:54:33' to database table.
if i put this condition
vcInsert:=vcInsert|| ','||''''||i_ldata(i).to_char(VTDATE,'DD-MM-RR HH12:MI:SS')
i get the error
to_char must be declare.
if i remove this line
i am getting this error ORA-01843: not a valid month
I have this SQL statement:
WITH data
AS (SELECT user_id,
jc_name,
[Code]....
I wish to do something like
if results > 10 print an error message (and no results)
if results < 10 print the results/output
I have two methods using pro*c to execute a query SQL and read the results. The query is a "select from" a view, this view is a union between two tables. The first method creates a cursor and fetches the results line by line. A second method creates a cursor and fetches the results in parts.
Method 1)
CODEEXEC SQL FETCH Cursor1 INTO ...
Method 2)
CODEEXEC SQL FOR :numLines FETCH Cursor1 USING DESCRIPTOR areaDescSQL;
I had executed this methods in my development enviroment, using Oracle 11g, Suse linux. As I expected, the method 2 spent much less time than the method 1. But, in the "customer enviroment", using another database, Oracle 11g and HP-UX, these methods spent almost the same time.
Is there some Oracle's configuration or parameter's settings that maybe explain that? What configuration can be differente between the two databases? What can I do to improve the time spent?
I'm currently looking for a way to declare a cursor in the 'declare' block using a previously defined variable that got its value from a query. For instance:
declare
my_company_id INTEGER := 'select c.company_id from company_table c where company_name='Wal-Mart';
cursor employees is
select e.employee_id from employees e where e.company_id = my_company_id;
Any way to do this?
an alternative to Oracle Cursors.My table has huge amount of data and is taking time in processing. I had thought of using BULK COLLECT but it can only be used to insert, delete or update data and will not allow me to select data.
View 33 Replies View Relateddeclare
type ref_cur is ref cursor;
r ref_cur;
enam emp%rowtype;
dno dept.deptno%type;
begin
dbms_output.put_line('The Employee details are');
open r for select deptno from dept;
loop
fetch r into dno;
[code]....
Error at line 1
ORA-06550: line 12, column 28:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
. ( % ;
if i need to use ref cursor to send parameters, is it possible? if yes how to use it?
How can we pass multiple parameters to cursors?
Ex: Cursor C_employees(C_empid number, C_cityname varchar2) is select emp_name, office_name from employee where employees where empid = c_empid and city = c_city_name;
I know we can pass one parameter to the cursor but I do not know how to pass multiple parameters.
I have a table table1 with 2 crore records.
select * from table1;
id code updateddatetime
------------------------------------
1 10001 2011-10-21 15:31:21.390
2 10001 2011-10-21 15:31:22.390
3 10001 2011-10-21 15:31:21.390
4 10001 2011-10-21 15:31:22.390
5 10002 2011-10-21 15:31:22.390
I want to delete records like id 2 which has odd updated time which is more than id 3 updated time.
is there any alternatives without using cursors as it taking so much time to process.
homework assignment using pl/sql based on 2 tables I have created below? I am not sure of how to use cursors, loops and proper syntax.
ASSIGNMENT:
1. Create a PL/SQL Procedure (cursor to loop through the records, check the LastName, then update the grade table
where id=id on grade table)
Rule:
A
‐ LastName ends with a character between A‐F
B
‐ LastName ends with a character between G‐K
C
‐ LastName ends with a character between L‐P
D
‐ LastName ends with a character between Q‐T
E
‐ LastName ends with a character between U‐Z
Create TABLE Registration (RegistrationID number(10), SectionID number(10), CourseID number(10),
SectionNumber varchar2(10),
StudentID number(10), FirstName varchar2(20),
LastName varchar2(20), CourseNumber varchar2(20), CourseName varchar(20));
[code].....
DB version: Oracle DB 10g Enterprise Edition 10.2.0.4.0
I have the following four tables:
tab_main- which lists main projects
tab_sub_main - which lists sub projects
tab_budget - amounts per projects/subprojects
tab_total - I want to load the data here
The table script with data is attached.
I want to load data into tab_total fields for prj_type= 'J' as follows:
1. accn_no from tab_main table.
2. fy from tab_budget table
3. fy_total_amt which is the sum(amt) from tab_budget table by accn_no and fy
4. all_FY_amt which is the sum(amt) from tab_budget table by accn_no
5. all the audit fields- date/user inserted/updated will come tab_budget table
how to create this procedure with cursors.
CREATE OR REPLACE PROCEDURE LOAD_DATA_INTO_TAB_TOTAL_PROC
IS
CURSOR C IS
select distinct m.accn_no, a.control_no,m.prj_type,
b.fy, b.amt, b.user_created, b.date_created, b.user_last_mod, b.date_last_mod
from tab_main m,
tab_sub_main a,
[code]....
CREATE TABLE tab_main
(
ACCN_NO NUMBER(7) NOT NULL,
PRJ_TYPE VARCHAR2(1 BYTE) NOT NULL
)
/
Insert into TAB_MAIN
(ACCN_NO, PRJ_TYPE)
[code]....
I am trying to write which will return cursors to front end program.The logic which I got stuck is while exiting the procedure.I am having some cursors in teh procedures which are read by the front end script.I have one condition which when satisfied I have to exit from the procedure.But the constraint I am facing is if I exit at that logic, oracle closes all the cursor and front end script is not able to read teh cursor and hence throws an error.Is there any way to exit the procedure without closing the REF cursors.
View 3 Replies View RelatedI am stuck somewhere in my project, situation is that we are trying to display data gathered from 3 different tables, we used cursors for this, here is the cursor declaration.
DECLARE
procedure DocInfoCur(myWhere IN varchar2) IS
init_dept_id number;
init_dept_name varchar2(40);
rcvg_dept_id number;
rcvg_dept_name varchar2(40);
CURSORCUR_DOCINFO IS
SELECT DISTINCT I.CTN, I.DIARYNUMBER, I.INITIATINGDATE, T.TYPENAME, I.INITIATINGDEPT,
I.DOCUMENTSUBJECT, I.DESTINATIONDEPT
FROM DOCUMENTINFO I, DOCUMENTTYPE T, DEPARTMENT D
[code]...
This displays all the records out there.
Now, I have a search form where I need to filter the same data with different parameters, I have compiled the entered data in one string, now i need to join that string at the end of where clause of my cursor, how can I do that, i have tried concatenating both but received compile error, is there a way out in the current scenario???
I think this is the scariest error I've encountered in oracle. After some thime of developing/debuggin I've always reached this error. This leaves me no choice but to restart the java app server, in that case I'll be affecting all the other applications deployed on it which is a very bad thing for me.
Is there anyway I could solve this, I've made it sure that I've handled all errors so that I could always close the connection. Simply put, for every termination case of my program I've placed a closing statement just to be sure that no connections are left open. But even after this, I still experience the ORA 0100 error.
Why exceptions does not raise in cursors declared by user like in the following program
create or replace function sal (p_id employees.department_id%Type) return number is
----sal1 employees.salary%type:=0;
cursor cx is
select * from employees where department_id=p_id;
begin
for i in cx
[code]...
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
[Code]...
i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is takinghere is the detailed program ... any body can tell why this is not taking and tell me how to pass the values through it..
declare
v_name char(6) ;
cursor c1(c_name char) is
select name, parent,child,status from relation
start with name='%'
[code]....
I wanted to know how to find the links between cursors that have been created in v$sql after compiling a PLSQL procedure.
For example, if i compile the following procedure :
create or replace
PROCEDURE PROCEDURE1
AS
BEGIN
insert into t values(1,1);
END PROCEDURE1;
, the 2 following cursors are created in v$sql :
select SQL_ID, SQL_TEXT from v$sql
where sql_id in ('71pj8t5nz1d80','2s567zb6684sh');
"SQL_ID""SQL_TEXT"
"2s567zb6684sh""BEGIN PROCEDURE1; END;"
"71pj8t5nz1d80""INSERT INTO T VALUES(1,1)"
Thus, i would like to know how to find that the cursor 71pj8t5nz1d80 is called/linked by the cursor 2s567zb6684sh.
This would be useful for interpreting some sql statistics in v$sqlstats in where such cursors appear and related sql statistics are accounted twice.
I'm using few cursors to update my data and store it back to the same table. But for some reason the cursor seems to be picking obsolete data.
cursor c1
is
select distinct roles
from table1
where flag = '1';
cursor c2
is
select distinct roles
from table1
where flag = '1';
begin
for i in c1
loop
here im updating the roles im picking to to a suffix and role.
update table1
set role = suffix_role
where 'some condition';
end loop;
commit; -- committing so changes are visible for my next cursor.
for j in c2
loop
here im deleting all the roles that are not part of my comparing table.
delete from table1
where role = 'something';
But in my debugging table i see that its deleting roles present as input for first cursor, whereas it should actually pick data with suffix_roles.
How to Close All open cursors in other session.
NAME TYPE VALUE
------------------------------------ ----------- ---------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 500
session_cached_cursors integer 50
Ex. as above we have 500 max cursor
seesion show 499 and now we need to flust all open cursors
My current database MAX OPEN CURSORS VALUE=300.
Applications "A","B","C" using same DB user (USER1) then connect to a database.
Last night I created dedicated DB user (USER2) then application "B" connect to DB using this new user.Starting traffic increase today morning "B" application receiving "ORA-01000: maximum open cursors exceeded" error from DB.When I change current DB user USER2 to USER1, application did not receiving above errors.
I don't understanding what happening in database?
