I have code inside function
.....
cursor cur1 is
select *
from sarchkler
where sarchkler_appl_no = in_appl_no
begin
select max(saradap_appl_no) into in_appl_no from saradap;
for rec1 in cur1 loop
......
my question I get variable for cursor after cursor declaration
To display highest marks,least marks,average marks,total marks of the student name entered.
desc stud;
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
NAME VARCHAR2(20)
M1 NUMBER
M2 NUMBER
How do I do that using PL/SQL and without Cursor.
I was wondering if it's possible to use the cursor as a parameter for a function. Something like this is what I'm trying to do:
set serverouput on
declare
cursor csv_file
[Code]....
I have a cursor returning some value.
for each value returned by the cursor i need to traverse through 31 rows(1 row per day * no of days in the month).
E.g. if cursor returns service_name as xyz then for xyz there can be 31 rows(service may not be used on some days)
I need to go to all of them and take some values and move them to a flat file. how should that be done?
Attached File(s)
Query.png ( 20.99K )
Number of downloads: 9
im calling an api inside a cursor.. for the first loop in a cursor , the api works well. however, for the rest of the loops the api doesn't give any error, but it doesn't insert any row to the table.
if i called the same api for every single value in a cursor seperately the api would insert all rows.
I can't understand the following cursor declaration (inside the DECLARE of a PL/SQL block)
CURSOR c_emps IS
SELECT emp_large_ot(empno, ename, job, mgr,hiredate, sal, comm, deptno) FROM emp_large;
emp_large_ot is an object type created as
CREATE TYPE emp_large_ot AS OBJECT
( empno NUMBER
, ename VARCHAR2(10)
, job VARCHAR2(9)
[code]...
and emp_large is similar to the standard emp table
I am writing a cursor and inside that cursor I am checking record exists or not and based on that I am doing my operation.But I am getting error that i can not use exception inside cursor see the below sample code (syntax may not be correct)
sample code------------------
cur c1
is select * from T1;
open c1
loop
fetch c1 into cur_id;
select name into var_name from t2 where id = cur_id;
exception
when no_data_found then
continue with next cursor value
end
update t3 set name = var_name where t3.id = cur_id;
commit;
end loop;
end;
I have a DML Statement inside a procedure and i use a cursor variable to get the values checked as below . I have attached my procedure not completely but the declaration part and the DML statement part.
The issue is my procedure is not inserting the records at all. It selects the values and then inserts accoringly but its not selecting because of the cursor reference R_LOC.LOCATION_GID.
when i hard code the value in the DML statemnt for the R_LOC.LOCATION_GID, the rows are inserted as expected. So i guess the way the procedure executes the value is not correct.
Modifying my select part which uses cursor variable R_LOC.LOCATION_GID under Insert statement.
select d.servprov_gid, d.depot_gid, replace(d.appointment_time,'':'') appmt_time, d.'||v_day_to_use||' DayUsed
from tesco_fresh_templates t, tesco_fresh_templates_d d, location_refnum r
where t.set_id= d.set_id
and d.depot_gid=r.location_gid
AND D.SERVPROV_GID=''R_LOC.LOCATION_GID''
and r.location_refnum_qual_gid=''TESCO.IVS SCHEDULING''
and (r.location_refnum_value=''YES'' or r.location_refnum_value=''Y'')
and t.default_set=''Y''
I am trying to create a view of a query that i would be using regularly...the query contains a function call in it...can i use it..
If yes when i try to do it . It gives out an oRa-01031:in sufficient privileges.
I have question.Using procedure inside the function ?can I get better performance?
View 8 Replies View RelatedIn the follow code example, is it possible to save the seeds that I generated into a table when I call this table function without expliciting doinginsert into <some_table>select select * from table(pkg_seed.getSeed(200)); I try the automonous_transaction clause but it does not work.
--drop package pkg_seed--drop type seed_tab CREATE or replace TYPE seed_rec AS OBJECT( id number,seed number); CREATE or replace TYPE seed_tab AS TABLE OF seed_rec; CREATE or replace PACKAGE pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED;END pkg_seed;/ CREATE or replace PACKAGE BODY pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED IS cursor cur_seed(vmaxrow number) is select rownum id, floor(dbms_random.value(1,1000) ) seed from dual connect by level <= vmaxrow; l_seed cur_seed%rowtype; BEGIN open cur_seed(maxrow); LOOP FETCH cur_seed into l_seed; pipe row(seed_rec(l_seed.id,l_seed.seed)); END LOOP; RETURN; -- the function returns a single result END getSeed;END pkg_seed;/ select * from table(pkg_seed.getSeed(200));
i am trying to create table inside function where in after creating table when am trying to access the table with select statement oracle is throwing error 'Table/view doesnot exist -00942', below is the code snippet
create or replace function example (mkey in varchar2) return varchar2
is
g_key varchar2(100);
l_tbl_ntext exception;
pragma exception_init(l_tbl_ntext , -942);
begin
begin
execute immediate 'select * from example1';
exception
when l_tbl_ntext then
null;
end;
execute immediate 'create table example1(skey varchar2, g_key varchar2) storage(buffer_pool, keep)';
end example;
/
select * from example;
DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;
MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').
I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?
View 4 Replies View RelatedAm calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error
SQL Error : ORA-14551: cannot perform a DML operation inside a query
Below Is the
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS
BEGIN
INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);
RETURN 1;
[code].....
I am encountering error in this code.
WHILE EXISTS ( SELECT * FROM tblOrgChart WHERE fxOrgID = v_chrTempKeyDept )
LOOP
v_intDept := CAST(v_chrTempKeyDept AS NUMBER) + 1 ;
v_chrTempKeyDept := LPAD('',3 - LENGTH(CAST(v_intDept AS VARCHAR2)),'0') || CAST(v_intDept AS VARCHAR2) ;
END LOOP;
Error: PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only
CREATE OR REPLACE PACKAGE test_package IS
TYPE refcur IS REF CURSOR;
END test_package;
CREATE OR REPLACE PROCEDURE get_info(o_cursor OUT test_package.refcur)
AS
BEGIN
OPEN o_cursor FOR
SELECT * FROM emp;
END get_info;
What is the advantage of using refcursor variable as OUT parameter in procedure. Instead of that why cannot we use variables or TYPE variables. use ref cursor as OUT parameter in procedure.
I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :
1. The number of assignments created from it (after a given constant assign date)
2. The number of assignments created from it that have been approved.
3. The number of unique users who have either created or approved an assignment from it
Test data :
create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int,
approver_id int, assign_date timestamp, approved_yn varchar2(10));
insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');
[code]....
Assignment :
id r_paper_id assigner_id assignee_id approver_id assign_date approved_yn
-----------------------------------------------------------------------------------------------------------
11 100 200 100 23-10-12 12:00:00.000000000 AMY
22 200 100 200 22-10-12 12:00:00.000000000 AMN
32 100 200 101 24-10-12 12:00:00.000000000 AMY
[code]....
Research_paper:
id name
----------
1A
2B
Expected result :
r_paper_id created approved unique_users
-----------------------------------------------
1 3 2 4
2 3 2 3
I wrote the following query for that :
SELECT rp.id r_paper_id,
COUNT(*) created,
COUNT(
CASE
WHEN a.approved_yn = 'Y'
[code]....
But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
where my mistake is?
I have the procedure with out parameter is ref cursor.
l_sql VARCHAR2(32767);
BEGIN
l_sql := 'select query with appending procedure IN aparameters';
OPEN rc_rpt FOR l_sql;
Here procedure IN parameter is a string with comma separated value which is appended in the dynamic query IN clause.So some time the size exceeded more then 32767 and getting error.If i am using normal parametrized cursor this issue is not there,but i want to return only ref cursor for some java purpose.My oracle version is 10g.
I have a table Student with two columns Rno and Name and i write following PL-Sql, it is working fine, my question is that how can i pass the parameter to cursor in the following query, e.g. if i pass the roll no. 501 then it should display only the particular Name.
declare
��� cursor st_name is
����������� select rno,name from student;
����������� studentnm st_name%ROWTYPE;
begin
�� open st_name;
[code]....
Calling function
select PACK.MAIN('blah') from dual
generates:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245
Unfortunately the Body is not accessible to see.The spec of the function is:
FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:
var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar
But this generates: ORA-01008: not all variables bound
Create a PL/SQL block that declares a cursor called DATE_CUR. Pass a parameter of DATE data type to the cursor and print the details of all the employees who have joined after that date.
DEFINE P_HIREDATE = 08-MAR-00
Test the PL/SQL block for the following hire dates: 08-MAR-00, 25-JUN-97, 28-SEP-98, 07-FEB-99.
I don't know how to pass parameters. So far I have this:
SET serveroutput ON;
DECLARE
p_HIREDATE DATE := 08-MAR-00;
CURSOR c_DATE_CUR(P_HIREDATE DATE) IS
BEGIN
OPEN c_DATE_CUR();
create table eml(num numner(10),email varchar2(100));
begin
for i in 1..20 loop
insert into eml values(i,'email.'||i||'@mmm.com');
end loop;
end;
I just want to know what is the difference between below two pl/sql block, in one block i am passing the p_email_id as paramter to cursor.Both block are working fine and giving the same result.first thing , i want to know how the p_email_id is passing to cursor get_emil_info without paramter.and which is good to use, i mean without paramter or with paramter.
--without paramterised cursor
declare
v_cnt number;
p_email_id number;
TYPE email_tab is TABLE of NUMBER index by binary_integer ;
eml_id email_tab ;
CURSOR get_eml_id is select num from eml;
[code]...
--with paramterised cursor
declare
p_email_id number;
TYPE email_tab is TABLE of NUMBER index by binary_integer ;
eml_id email_tab ;
CURSOR get_eml_id is select num from eml;
[[code]...
I need to know how to pass the ref cursor as INOUT parameter to a procedure. I have the following procedure and I need to execute it.
PROCEDURE get_site_setup_detail (
p_study_id IN SITES.study_id%TYPE,
p_proj_act_date IN VARCHAR2,
[code]....
i have a proc that is taking p_serial_number refsursor as in parameter. the structure of p_serial_number is required to be
mfg_prod_cdchar (3 byte)
mfg_prod_seq_no char (6 byte)
How do I need to define this ref cursor ? and when I use it in my Procedure how do I fetch the column values ?
How do i execute and display results of a procedure declared with ref cursor OUT parameter.
I am using SQL Developer and Oracle 10.2.
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.
DECLARE
--initialize variables here
v_date varchar2(10);
v_rebuild_index varchar2(250);
v_sql VARCHAR2(250);
p_table_name varchar2(250) := 'DS_ABSENCE';
p_source_table varchar2(30) := 'STG_ABSENCE';
p_source_owner varchar2(30) := 'STG_SAP';
v_for_sql varchar2(1000);
[code]....
if function having retrun statement, why we need to use out or inout parameters.
View 16 Replies View Related