SQL & PL/SQL :: How To Make General Procedure Print Cursors

May 27, 2011

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;

View 3 Replies


ADVERTISEMENT

Forms :: Make Print Button To Display Information

Oct 4, 2012

how can i put button in a form to print the information i displayed in this form.

View 1 Replies View Related

Change Code To Make Exception Print When No Data Is Found?

Feb 28, 2011

I 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;

View 1 Replies View Related

SQL & PL/SQL :: Usage Of Cursors In Stored Procedure

Feb 22, 2012

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??

View 4 Replies View Related

SQL & PL/SQL :: Creating Procedure Which Will Process All The Cursors?

Apr 19, 2012

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.

View 21 Replies View Related

SQL & PL/SQL :: Retrieving Multiple Values In Stored Procedure Using Variables And Cursors

Oct 6, 2011

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.

View 7 Replies View Related

SQL & PL/SQL :: How To Make Procedure From Select Statement

Feb 8, 2011

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

View 3 Replies View Related

SQL & PL/SQL :: Improve Procedure Which Is Looping Through Query To Make Inserts

Oct 10, 2013

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]....

View 12 Replies View Related

PL/SQL :: Procedure To Make Minus Query As Output For 2 Tables

Nov 30, 2012

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..

View 11 Replies View Related

Application Express :: General Apex Single Standalone Installation

Apr 29, 2013

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,

View 2 Replies View Related

Forms :: How To Pass Excel General Value To Database Tables Date Field

Jun 17, 2013

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

View 8 Replies View Related

SQL & PL/SQL :: Print No Results If More Than 10 Records Else Print The Output

Mar 24, 2011

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

View 1 Replies View Related

Time Spent Using Cursors?

Jun 17, 2013

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?

View 1 Replies View Related

Declaring Cursors With A Variable

Jul 7, 2010

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?

View 4 Replies View Related

SQL & PL/SQL :: Alternative To Oracle Cursors

Aug 13, 2013

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 Related

SQL & PL/SQL :: Ref Cursors For Passing Parameters?

Mar 20, 2013

declare
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?

View 3 Replies View Related

SQL & PL/SQL :: Passing Multiple Parameters To Cursors?

Jun 18, 2013

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.

View 8 Replies View Related

SQL & PL/SQL :: How To Remove Odd Rows From A Table Without Using Cursors

Nov 16, 2011

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.

View 12 Replies View Related

SQL & PL/SQL :: How To Use Cursors / Loops And Proper Syntax

May 31, 2011

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].....

View 20 Replies View Related

SQL & PL/SQL :: Cursors To Load Data Into A Table?

Oct 3, 2011

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]....

View 34 Replies View Related

PL/SQL :: Return Cursors To Front End Program

Mar 14, 2013

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 Related

Cursors - Display Data Gathered From 3 Different Tables?

Apr 30, 2010

I 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???

View 1 Replies View Related

ORA 0100 - Maximum Open Cursors Exceeded?

Oct 23, 2006

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.

View 8 Replies View Related

SQL & PL/SQL :: Exceptions Does Not Raise In Cursors Declared By User?

Feb 17, 2012

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]...

View 13 Replies View Related

SQL & PL/SQL :: How To Loop Through Nested Cursors Based On Value From 1st Cursor

Jul 21, 2011

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]...

View 7 Replies View Related

PL/SQL :: Passing Char Variable In Cursors - Not Taking?

Apr 9, 2013

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]....

View 4 Replies View Related

Performance Tuning :: PLSQL And Related Cursors Generated In V$sql

Jan 26, 2012

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.

View 1 Replies View Related

SQL & PL/SQL :: Cursors To Update Data And Store It Back To Same Table

Sep 4, 2010

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.

View 12 Replies View Related

Server Administration :: Close All Open Cursors In Other Session?

Mar 23, 2010

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

View 9 Replies View Related

Server Administration :: ORA-01000 Maximum Open Cursors Exceeded

Apr 26, 2013

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?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved