While reading data from collection variable using ref cursor . I am getting the below two errors.
PLS-00382:Expression is of wrong type
ORA-22905 Cannot access rows from a non-nested table item.
CREATE OR REPLACE PACKAGE APPS_GLOBAL.GIIOMEGAORDERLIST
AS
TYPE BU_LIST_TYPE IS TABLE OF VARCHAR(50);
TYPE OFFER_DETAIL IS RECORD
(
GII_BU VARCHAR(50),
GII_OFFER NUMBER,
[code]........
Is it possible to bind collection while opening a ref cursor. Find below the code that I am trying. My goal is to open cursor once using collection variable. Can it be done using DBMS_SQL ?
DECLARE
TYPE typ_emp_rec_in IS RECORD
(
deptno NUMBER,
sal NUMBER
[code]......
I have a procedure which will be called my front end team get the reports stored in the database as BLOB. These reports are mostly PDF files.
I need to read this BLOB field as a part of OUT PARAMETER REF CURSOR which also contains other two columns.
Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.
View 7 Replies View RelatedThe following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
[code].....
how to use collection variables as OUT parameters in procedure.
Declare
Cursor c1...;
Cursor c2...;
begin
open c1 ;
fecth c1 bulk collect into v1;
close c1;
[Code]...
Is there any way by which if condition gets true then v1 gets appended rather than being overwritten?
declare
type lst_deptno is table of dept.deptno%type index by binary_integer;
type lst_deptno_emp is table of emp.deptno%type index by binary_integer;
v_deptno lst_deptno;
v_deptno_emp lst_deptno_emp;
cursor c1 is select deptno from dept;
[Code]...
The following procedure stores the retrieved data in o_ref_primary_dept collection variable,suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
BEGIN
l_primary_dept :=
'SELECT emp_obj(empno,ename)'
|| ' FROM emp';
EXECUTE IMMEDIATE l_primary_dept BULK COLLECT INTO o_ref_primary_dept;
END;
how to use collection variables as OUT parameters in procedure.
What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?
View 1 Replies View RelatedI have an existing Procedure that uses a ref cursor as a parameter. The procedure currently simply selects data into the ref cursor using an OPEN FOR and then exits.
I have a need to extend the processing of the procedure. This will mean retrieving extra columns in the select and also removing some of the retrieved records.
I have the constraint that the definition of the REF CURSOR (which is based on a record type as follows) cannot be modified.
TYPE t_charge
IS
RECORD (
id number,
date_from invoice_detail.from_date%TYPE,
date_to invoice_detail.to_date%TYPE,
description invoice_detail.desc_text%TYPE,
amount invoice_detail.amount%TYPE
);
TYPE t_charge_cursor IS REF CURSOR RETURN t_charge;
The procedure does this
PROCEDURE get_bill_lines (
p_bill_id IN NUMBER,
bill_lines_list OUT t_charge_cursor )
IS
OPEN bill_lines_list FOR
SELECT id, from_date, to_date, desc_text, amount
from invoice_table
where id = p_bill_id;
I am thinking instead of selecting directly into the bill_lines_list parameter I will use a local REF CURSOR to select into a COLLECTION. I can then do some work on the collection, similar to below:
PROCEDURE get_bill_lines (
p_bill_id IN NUMBER,
bill_lines_list OUT t_charge_cursor )
TYPE t_local_charge
IS
RECORD (
[code].....
This all works fine, but the question is, how do I then put the collection into the bill_lines_list REF CURSOR where the '*** HERE ***' comment is so I can send the existing REF CURSOR structure back? (Remember I cannot change any definition that is referenced externally)
In PL/Sql, I create a new collection using TYPE. Then populate it with BULK COLLECT.The next step I need is to load data from the collection to pass as WHERE condition to a ref cursor.How I can do this?Every test I did fais. It seems to me that I cannot use some kind of SELECT to retrieve all data from the collection, but I might use a cursor to read row by row. (I'm on Oracle 11gR2 DB )
View 3 Replies View RelatedIs it possible to:
-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;
[Code]....
I've create two types:
create or replace type VAL_REC as object
(WAARDE number(20,5) ) ;
create or replace type VAL_TAB is VARRAY(200) of VALREC ;
create or replace type WSN_REC as object
( START_DATE date
, END_DATE date
, DATA VAL_TAB) ;
create or replace type WSN_TAB as table of WSN_REC ;
In a PL/SQL table collecton WSN_TAB is filled. Now I want to create a query (ref cursor) that gives the result:
START_DATE END_DATE WAARDE(1) WAARDE(2) WAARDE(3) etc.
2010-08-15 2010-08-16 20 20 40
2010-08-16 2010-08-17 23 45 40
My cursor went to the exception when its got the error " ORA-01403: no data found"
But my cursor need to run even though it got the error " ORA-01403: no data found"...This is my exception part
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (c1rec);
DBMS_OUTPUT.put_line (SQLCODE);
DBMS_OUTPUT.put_line (SQLERRM);
-- RETURN NULL;
-- code change added as part of GCA642 -- END
END get_geo_test1;
I am not able to parse this XML message.
with t as (select xmltype('sku_list xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<sku vk_unit="213-13085" model="M14573" />
<sku vk_unit="227-10036" model="M14573" />
<sku vk_unit="227-10037" model="M14573" />
<sku vk_unit="213-13086" model="M14573" />
[code].....
During the scheduled backup we observed that, backup got failed. Error is "ORA-01115: IO error reading block from file 11 (block # 118736)
".
unable to rectify it. Below are the part of log file -
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.MAIN [TABLE_DATA:"DW_REP"."DW_MOTOR_CLAIM_INFO_DTLS"]
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 8976
ORA-24795: Illegal ROLLBACK attempt made
[code]...
This is development server. Dont have RMAN configured.But when we connected to RMAN and issued block recovery, it started process and shows media recovery done. Since no RMAN backup was taken before, what it does here?
C:UsersAdministrator>RMAN
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 17 10:48:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> CONECT target /
connected to target database: MAGMADEV (DBID=2469200049)
[code]...
how to resolve this problem?
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 taking
here is the detailed program ... 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='%'
connect by prior parent=child
union
[Code]...
I am running this procedure but it will not compile. I get the error "PLS-00356: 'REC.XX' must name a table to which the user has access"
All of the query results from the cursor are correct.
create or replace procedure SWDCADMIN.Hard_Delete_Client( cltId IN number)
IS
cursor c1 IS
select
t1.table_name xx,
t1.owner || '.' || t1.TABLE_NAME uu,
[code]...
open cp_cursor for 'Select curtailprogramkey from curtailProgram where curtailprogramid like 'l_rec.curtailprogramid%'';
NOTE: l_rec.curtailprogramid is varible. what is wrong in the above statement?
I got error in oracle collection
CREATE OR REPLACE PACKAGE BODY pkg_das_stag_to_master
AS
PROCEDURE sp_load_dasstage_security
AS
cursor cur_stag is
select Asset_id,
ID_ISIN,
ID_SEDOL1,
ID_CUSIP,
[code].........
error:
66/35 PLS-00302: component 'ASSET_ID' must be declared
66/35 PLS-00302: component 'ASSET_ID' must be declared
66/18 PL/SQL: ORA-00904: "TYP_SECURITY_VAR"."ASSET_ID": invalid identifier
63/1 PL/SQL: SQL Statement ignored
i am creating an apex page where i have 2 regions. From the Top region stores all fields entered into the bottom region Text fields like first name and last name and address fields are in region 2(bottom).After region 2, i have a add person button.
Once i click add person, that person will get into top i.e region 1.
Now, Region 1 got person1 first name ,last name
person2, first name,last name
etc..
I am not able to display like p1_first_name,p1_laast_name as the list is not stable..it is growing and not showing the person who already got saved..I can retrieve them from DB using a cursor..But from cursor vairable how to get into page vairable..
In a package I have one procedure "setscores_ram_bulk" with collection varaiavles.And other two procedures are
"UpdateRelatedOrders" and "SetGeoFraudScore"
with out collections.I am calling collection procedure "setscores_ram_bulk" in non collection procedures
"UpdateRelatedOrders" and "SetGeoFraudScore".
I am getting the below error.PLS-00306:wrong number or types of arguments in call to 'SETSCORES_RAM_BULK' .I am sending my code.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.INTERNAL_SCORING_setscore_Bulk
IS
TYPE rec_setscores IS RECORD
(
pBUID score.buid%TYPE,
OrderNum score.order_num%TYPE,
ScoreType VARCHAR2(100),
p_Score score.velocity_score%TYPE
);
[code]....
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have a .xls file, which has few columns with Korean data. I have problem converting it into .csv. I converted it to UNICODE TEXT, which was TAB separated. I replaced tabs with commas. but still saved it as .txt file. Now I am using utl_file package to open and read.
utl_file.fopen_nchar(l_dir, l_filename , 'R',8000);
I outputted the first header line, and instead of Korean fieldnames, it displayed some different characters.. cannot even copy-paste..
I am uploading the converted .txt as I cannot upload original .xls file here...
I have one table with clob field. The data in this field will contain string having transaction record data. Now I want to read data from this clob filed and insert different record in other oracle table.
example ->
Data in clob field will be->
H|12|1233|fff|sss
L|1234|gggg|tttt|yyyyyy|rrrrr
L|1094|gggg|tttt|yyyyyy|rrrrr
L|1344|gggg|tttt|yyyyyy|rrrrr
L|1666|gggg|tttt|yyyyyy|rrrrr
L|188|gggg|tttt|yyyyyy|rrrrr
I have one master table and one detail table. I want to insert record -> H|12|1233|fff|sss in master table
and records->
L|1234|gggg|tttt|yyyyyy|rrrrr
L|1094|gggg|tttt|yyyyyy|rrrrr
L|1344|gggg|tttt|yyyyyy|rrrrr
L|1666|gggg|tttt|yyyyyy|rrrrr
L|188|gggg|tttt|yyyyyy|rrrrr
in detail table.
End of excercise will redult-> 1 record in header and 5 records in detail table.
I need to implement it in my project.
DB Used : Oracle 10g.
A table X : NUM, INST are column names
NUM ----- INST
1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1
-- Based upon requirement :
1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM
23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
Since I need to do something along the lines of inserting an xml document into the database for later to use.
Anyway, the problem I get with the below is
Quote:
Error at line 95
ORA-00984: column not allowed here
Script Terminated on line 95.
It's on the INSERT line
declare
xmlInput clob;
begin
xmlInput :=('<?xml version="1.0" encoding="windows-1256"?>
<XLAAPRPT>
[Code].....
Just explaining what I am trying to achieve:
1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)
2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012
3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)
4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.
5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point
6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.
PS:
1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.
2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)
Code :
===================================================================================================
create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2)
is
[Code].....
I need to read data from text file(located on application or db server or on some other server, however path is known to me.) and then append some data in it.
Data will be read and written on daily basis so i want to clear all data on date change.
how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.
View 3 Replies View RelatedIs it possible to read streaming continuous binary data from a web site using PL/SQL?
View 8 Replies View RelatedI need a way to ftp file to remote server by reading data from table. I searched a couple of sites which asked me to use Chris xutl_ftp package..but unfortunately the site is no accessible..
Here is the code
CREATE OR REPLACE PACKAGE UTL_FTP
AUTHID CURRENT_USER
AS
/**
* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (john_2885@yahoo.com)
[code].....