SQL & PL/SQL :: Using Function Inside Query And Without Cursor?
Mar 17, 2013
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
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
Am 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;
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.
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
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''
In 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; /
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').
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 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: .(*%&................
Below is the block which i am trying to test in scott schema. I dont want to substute IN clause values directly. So i have written cursor and have added in separate variable separeated by comma.But its not working.
declares varchar2(1000);s1 varchar2(1000);v number := 0;v1 varchar2(2000) := 'SCOTT';j number := 0;cursor hhis select ename from emp;beginselect count(*) into v from emp; for i in hh loops := s||''''||i.ename||''''; j := j+1;if j <> vthen s := s||',';end if;s1 := s1||s;s := null; end loop;dbms_output.put_line(S1); case when v1 in (s1) then dbms_output.put_line('Y'); else dbms_output.put_line('N'); end case;end;
Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual; but i get the following error: ORA-14551: cannot perform a DML operation inside a query.
The intention of the code is to perform an insert into my table based on passing in values via an object into Stored Procedure Apply_Users_Update
Package Definition create or replace PACKAGE OTMP_TCIS_RS AS --1 PROCEDURE Get_UserInfo PROCEDURE Get_UserInfo( o_OutCode OUT INT, i_language IN VARCHAR2); FUNCTION Get_UserInfo( i_language IN VARCHAR2) RETURN NUMBER; [code]....
select * from (select pa_request_id ,max(status) status ,max(approved_amount) approved_amount ,min(level_id) level_id ,max(req_amount) req_amount from target_aggregation_attendee group by pa_request_id) where status = 'Approved')
My Main Query:
select TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername, TARGET_EMPLOYEE.GE_ID as requesterGEID, TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName, TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg, TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg, TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
2. Table with the values for columns as given in table 1 ex:
col1, col2, col3, col4, col5, a , aa , 1 , x1 , p b , ab , 2 , x2 , q c , ac , 3 , x3 , r
I have to select values from table2.col1 do some processing and calculate values and store it in a table then do the same thing with col2 and so. This needs to be done for all the columns that appear in table1.For example in table 1 i have only three columns mentioned thus i have to process col1, col2 and col3 from table2. col4 and col5 will not be processed since they do not appear in the first table.
The problem is i have hundred columns in table 2 and the user can add up to hundred columns in table 1 as and when it is required.
I have created a cursor to first select column name from table 1 where variable is not null.For each value in cursor i put it in a local variable.
Second step is to select values from table2 where instead of column name i am using the local variable.But the problem is instead of choosing values from col1 the query returns the value as col1 (the value of local variable)
I am strugling hard to pass a cursor to my function as in parameter.here is my code
Function migrate_audits (sys_audit_ids SYS_REFCURSOR ) return number; Function migrate_audits (sys_audit_ids in sys_refcursor ) return number is v_return number; v_sys_audit_id number; begin LOOP FETCH sys_audit_ids INTO v_sys_audit_id; [code]....
passing cursor to a function is not possible in oracle? what other option I have to pass collection to the function ?
declare type osd_refone is ref cursor; osd_ref osd_refone; l_status number; [code]......
abc_reports in this pack "ab_report" it is the function it having the ref cursor as out parameter . when am executing the above anonymous block am getting the below error,so how can i print the out ref cursor data in my block.
ERROR at line 8: ORA-06550: line 8, column 12: PLS-00221: 'OSD_REF' is not a procedure or is undefined ORA-06550: line 8, column 3: PL/SQL: Statement ignored
