SQL & PL/SQL :: Append Collection Variable

Oct 11, 2012

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

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Table Variable Or Collection

Jul 11, 2012

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 Related

SQL & PL/SQL :: Using Collection Variable As OUT Parameter

Aug 8, 2012

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;
[code].....

how to use collection variables as OUT parameters in procedure.

View 1 Replies View Related

PL/SQL :: Using Collection Variable With OUT Parameter?

Aug 8, 2012

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.

View 1 Replies View Related

PL/SQL :: Open Ref Cursor Using Collection As Bind Variable

Feb 26, 2013

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

View 4 Replies View Related

SQL & PL/SQL :: Error In Reading Data From Collection Variable For Ref Cursor

Jun 19, 2013

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

View 4 Replies View Related

PL/SQL :: Related Collection Within A Collection

Sep 20, 2012

There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.

declare
type type_name is table of varchar2(10);
type type_name1 is table of type_name;
names type_name1:=type_name1(type_name('hello'));
begin
-----HOW TO PRINT A VALUE--------
-----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT
null;
end;

1) need to print the values of names(1)
2)Assign a value to names(2)

View 3 Replies View Related

PL/SQL :: How To Create One-column Collection From Another Multi-column Collection

Mar 22, 2013

I am describing a SQL statement to get it's column list:DECLARE

cur     NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;

[Code]....

Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?

View 4 Replies View Related

Append Columns From One Table To Another

Nov 14, 2011

Perhaps this is a common request : I have 2 tables:

Table A
-------
ID Value
1 a
2 b
3 c

Table B
-------
ID AnotherValue
1 x
2 y

I am hoping to append a column from Table B to Table A based on a simple sql join (e.g:

Table A

ID Value AnotherValue
1 a x
2 b y
3 c (null)

)

I would rather stay away from the standard update statement since it takes far to long and I'd prefer not to use create table as I don't want to duplicate any data...is this possible to do ? (e.g: just insert the columns into this table ?) - or if it's possible the performance overhead just wouldn't make it worth it ?

View 4 Replies View Related

PL/SQL :: Append Values If Repeating Same Name?

Jan 4, 2013

if the same name repeating it should to append with _1 and _2 until same name reached.

select 'fname' name from dual
union all
select 'lname' name from dual
union all
select 'email' name from dual
union all
select 'fname' name from dual
union all
select 'fname' name from dualmy output should be like below...

fname
lname
email
fname_1
fname_2

View 11 Replies View Related

PL/SQL :: Append In Control File

Feb 14, 2013

I am just looking to my control file that I have. I have managed to successfully get my control file to load data into the required tables but just for my learning I was trying to amend the when clause with an and condition but keep getting an error.

My code:
OPTIONS (DIRECT=TRUE, ERRORS=0, SKIP=1)
load data
infile 'A:/My Files/Feeds/20130211/data_summary_1_20131214.txt' "STR '
'"

APPEND
into table I_DATA_1
WHEN (RID <> 'RID')
fields terminated by "~|~" optionally enclosed by '"'
TRAILING NULLCOLS
(
 RID,
S_TIMESTAMP   timestamp 'DD-MON-RR HH:MI:SS.FF3 PM'
)

the above code works perfectly but if i change the following line:

WHEN (RID <> 'RID' AND 'S_TIMESTAMP' IS NOT NULL)than this gives me an error.

View 4 Replies View Related

SQL & PL/SQL :: CLOB Column Append

Feb 13, 2013

I have a requirement when I need to append and show the contents of a CLOB (rich text) column into the Open office report.

The use case is as below.
User(s) can enter the details into a CLOB column.
This data entered by each user need to be concatenated onto another CLOB column which will hold all the history of changes. Mainly i wanted to perform a concatenation of these two columns.

|
| User Entered | Stored |
| Data Stored in Clob Column 1| Data Stored in Clob Column 2 |
| v_clob_1 | v_clob_target |
-------------------------------------------------------------------------------
|"Text 1" and a image |"Text 1" and a image |
-------------------------------------------------------------------------------

Any method to achieve this case. I had tried the following method to achieve this. But no success.

v_merge clob;
v_clob_target clob;
v_clob_1 clob;

1)
DBMS_LOB.APPEND(v_merge, v_clob_target);
DBMS_LOB.APPEND(v_merge, v_clob_1);

2)
DBMS_LOB.APPEND(v_clob_target, v_clob_1);

3)
v_merge:= v_clob_target || ''|| v_clob_1;
v_clob_target := v_merge;

View 6 Replies View Related

Server Administration :: How To Append When Get Ddl

Aug 8, 2011

Have got ddl of index,but it is not append ';' end of each index,how to append ';' by auto.

Select
Dbms_Metadata.Get_Dependent_Ddl(Object_Type => 'INDEX',
Base_Object_Name => 'TB_HXL_USER')
From Dual

result:

[Code].....

View 2 Replies View Related

How To Append Timestamp To Log File In SQL*Plus

May 9, 2013

Version: 11.2.0.3
Platform : RHEL 5.8 (But I am looking for platform independant solution)

I want to append the timestamp to spooled log file name in SQL*Plus.The spooled log filename should look like

WMS_APP_23-March-2013.logI tried the following 3 methods found in the google. But none of them worked !

I tried this

col sysdt noprint new_value sysdt_var
SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL;
spool run_filename_&sysdt_var.Logas suggested in

[URL]

and this

spool filename with timestamp
col sysdt noprint new_value sysdt
SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL;
spool run_filename_&sysdt..Logas suggested in

[URL]

and this

column tm new_value file_time noprint
select to_char(sysdate, 'YYYYMMDD') tm from dual ;
prompt &file_time
spool logfile_id&file_time..logas suggested in

Creating a spool file with date/time appended to file name

None of the above worked in RHEL or MS DOS.

View 6 Replies View Related

Inserting Millions Of Rows Using Append?

Aug 6, 2011

INSERT /*+ APPEND */
INTO sales_fact
SELECT *
FROM customer_sales_fact_staging
ORDER BY time_wh_id;
COMMIT;

[URL]

Suppose in the above case

SELECT *
FROM customer_sales_fact_staging
ORDER BY time_wh_id;
returns a huge number say more than 10 million or 50 million

will the commit work(one single transaction)

View 1 Replies View Related

Can HINT Append Generate Error

Mar 1, 2007

I've got SQL code generated by an HP tool to make an update of itself. This code is not modifiable because was execute 'in background' from the tool updater.

The macro operations made by the updater (through sql code) are:

--create a copy of actual table and rename with '_old' suffix
--create new table, with indexes and constraints
--insert data into new table from old tables

Now, only in one table, when the data were inserted, an ORA-00001: unique constraint (ASSET_SVIL.CFG_CFGSECTIONCFGE) violated, appear.

To insert data is used an insert/select statement with an HINT /* APPEND */. We verify all the data existing in the old table (that is the data to 'migrate') but there aren't duplicate record!

BUT IF WE REMOVE THE HINT /* APPEND */, THE INSERT/SELECT STATEMENT WORKS WITHOUT GENERATE ERRORS!!

View 8 Replies View Related

SQL & PL/SQL :: How To Append Sign Of % And Adapt Output

Jan 24, 2012

I have following table: tab1

id configured_size current_size percentage
1 500 100 20
2 300 100 33
... ... ...

When I launch select * from tab1 I get :

id configured_size current_size percentage
1 500 100 20
2 300 100 33
... ... ...

My question is is there any way how to edit the abovementioned select to get something like :

id configured_size current_size percentage
java_eml 500 100 20%
java_hdl 300 100 33%
... ... ...

I want to append two things:

sign of % in row percentage
when id =1 then in output have java_eml
when id =2 then in output have java_hdl

The problem is I cant update the table (not sufficient rights) so is there any way how to adapt that output?

View 7 Replies View Related

Insert Using Append / Bulk Correct?

Aug 3, 2011

I want to know which is the most efficient insert method among the followiing

1)using the hint append
or
2)bulk collect. Preferably I'd like to know which method of inserting for say 2-5 millions rows is the best.

View 6 Replies View Related

SQL & PL/SQL :: Append Or Merge 2 Utl_files To One File?

Jun 25, 2010

Am extracting data from a database and putting them in 2 different utl_files. In the end i want to append or merge the 2 utl_files to one file.

View 10 Replies View Related

How To Copy Set Of Rows N Times And Append Them With Different Value For A Column

May 27, 2011

Im trying to replicate a set of rows multiple times to create large volume. I am trying by For Loop, but got confused how to pass the parameters in any cursor i declare.

View 8 Replies View Related

SQL & PL/SQL :: Concat Or Append In Long-raw Type Column?

Feb 4, 2012

Im having mail content column of type long Raw in a table.i just want concat or append a value in that column when i tried it shows error "illegal use of long type".how to append value to it. value will be string type

View 15 Replies View Related

SQL & PL/SQL :: Append More Rows In Single Clob Column

Apr 9, 2013

I have a query which returns nearly 20k rows, as per the requiremnet we need to append all these rows in specific format and insert into single clob column.in the below procedure test_clob.textt is clob field.

CREATE OR REPLACE PROCEDURE pro_test
v_mas_seq NUMBER (9);
v_gov_total NUMBER (20, 2);
v_emp_total NUMBER (20, 2);
v_text_exp CLOB;
v_pageaccess VARCHAR2 (15);
v_dto NUMBER (7) := 4011486;
v_batchno NUMBER (20) :=
[code]....

View 15 Replies View Related

SQL & PL/SQL :: No Entries In Materialized View Log For Insert Append?

Aug 9, 2010

I've created a materialized view log on table with the following statement:

CREATE MATERIALIZED VIEW LOG ON table_a
WITH ROWID, SEQUENCE (column_a, column_b, column_c)
INCLUDING NEW VALUES;

The insert is done with the following statement:

INSERT
/*+ APPEND PARALLEL("table_a") */
INTO
"table_a"
("column_a",
"column_b",
"column_c",
"column_d_sum")
(select
column_a",
"column_b",
"column_c",
"column_d_sum"
from table_B)

But the Log is empty when the insert is finished. When I insert rows without the APPEND hint, rows are created in the log table. So, doesn't the log record bulk loads?

View 6 Replies View Related

SQL & PL/SQL :: How To Append Data Into Macro-enabled Excel File

Aug 18, 2010

My requirement is to export Oracle table's data into an already existing excel file with Macros (.xlsm) using a procedure. I am able to write/append the data into the simple .xls file. But I am searching whether any way of appending into .xlsm file. "how can we append the data into a Macro-enabled excel file?".

View 3 Replies View Related

SQL & PL/SQL :: How To Append Data To Existing CLOB Field In Oracle Table

Sep 13, 2010

code to append data of 32k to exisitng CLOB field in an oracle table..

View 1 Replies View Related

Server Utilities :: Append Tables Content To Existing Tables?

Nov 9, 2010

problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.

we have found something called table_exist_action=append in impdp.

View 2 Replies View Related

SQL & PL/SQL :: Collection Over Cursor?

Jun 30, 2010

What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?

View 1 Replies View Related

SQL & PL/SQL :: Collection Of Records

Sep 29, 2010

I have been doing some code in collection for testing. I have been doing the below but getting the error.

SQL> desc t_mine;
Name Null? Type
----------------------------------------------------- --------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME;
3 v_elapsed PLS_INTEGER;
4 type allobjects_record is record
5 (owner varchar2(1000)
[code].......

ERROR at line 15:

ORA-06550: line 15, column 15:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 15, column 3:
PL/SQL: SQL Statement ignored

I followed the below link

[URL].......

View 5 Replies View Related

SQL & PL/SQL :: Various Collection Types Available In Oracle 9i?

Jun 15, 2010

What are the various collection types available in Oracle 9i.

Mention the differences between them.

View 4 Replies View Related

SQL & PL/SQL :: How To Remove Duplicates From Collection

Mar 24, 2011

remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.

Object creation

create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');

create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));
create or replace type testingtype_tab as table of testingtype_obj;

Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;

If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error

ORA-22950: cannot ORDER objects without MAP or ORDER method

View 4 Replies View Related







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