SQL & PL/SQL :: Insert Values Fetched From Object Type Into Empty Table?

May 4, 2012

I need to insert values fetched from an object type into an empty table.

-- >> table creation:

create table ASSET
(
ASSETID NUMBER(5),
ASSETTYPE VARCHAR2(20));

-- >> created an object_type

SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20),
2 v_ASSETTYPE varchar2(20));
3 /
Type created

--- >> create the package

SQL> create or replace package test_arr is
2 type nest_tab is table of obj1;
3 procedure insert_data(t_nest IN nest_tab);
4 end test_arr;
5 /

Package created

SQL> create or replace package body test_arr is
2 procedure insert_data(t_nest IN nest_tab) is
3 begin
4 for i in t_nest.first..t_nest.last loop
5 insert into asset(ASSETID,
6 ASSETTYPE) values (t_nest(i).v_ASSETID,

[code]....

-- >> a block to execute the above package:

SQL> declare
2 type ref_tab is table of obj1;
3 ref_tab1 ref_tab;
4 begin
5 ref_tab1 := ref_tab(1,'a');
6 test_arr.insert_data(ref_tab1);
7 end;
8 /

But I am getting the below error when executing the package:

ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 1:

[code]....

View 6 Replies


ADVERTISEMENT

PL/SQL :: Passing Values From Table Type To Oracle Object Type

Mar 8, 2013

I have created the below types and oracle objects.

create or replace type T_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
--
create or replace type T_EMA_NP_RANGE_LNPTICKET_REC as object
(
ticket number
)
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC

The following type is created in the Package specification

type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);

I have the following two procedures

Procedure getResponse(p_call_request_id IN number, p_resp_rec IN t_setdel_resp_rec,
p_range_ticket_tab IN t_range_icket_tab, p_endof_event IN varchar)

PROCEDURE ProcessResponse(p_call_request_id IN number, p_resp_rec IN t_resp_rec,
p_ticket_tab IN t_ticket_tab, p_endof_event IN varchar2)

The get Response procedure is a wrapper procedure exposed to Java to pass values. The Process Response procedure is a main procedure where all logics and business rules are handled.

The Problem is:

How can I pass the values from get Response procedure to Process Response procedure. So that rules and validations are applied. Please note the p_ticket_tab may have many ticket numbers corresponding to p_call_request_id.

Values E.g. :
p_call_request_id = 1
p_resp_rec (1234, 'Error found')
p_range_ticket_tab (1,2,3,4,5)
p_endof_event = 'Y'

View 7 Replies View Related

SQL & PL/SQL :: Insert All Rows Values Fetched By Cursor?

Aug 12, 2010

I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.

what parameter or code I should used ?

I have marked with color row as below.

DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Passing Values From Oracle Object Type To PLSQL Type

Mar 8, 2013

I have created the below types and oracle objects.

create or replace type T_EMA_NP_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)

create or replace type T_EMA_NP_RANGE_LNPTICKET_TAB AS TABLE OF T_EMA_NP_RANGE_LNPTICKET_REC
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC

The following types are created in the Package specification

type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
--
subtype t_ema_lnpticket is T186_IN_REQ_PARAMETER.T186_EMA_LNPTICKET%TYPE; -- Number
type t_ema_lnpticket_tab is table of t_ema_lnpticket index by binary_integer;

I have the following two procedures

PROCEDURE getEMAReturnResponse(
p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_np_setdel_resp_rec,
p_ema_range_lnpticket_tab IN t_ema_np_range_lnpticket_tab,
p_endof_event IN varchar)

PROCEDURE Return_Response(p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_resp_rec,
p_ema_lnpticket_tab IN t_ema_lnpticket_tab,
p_endof_event IN varchar2)

getEMAReturnResponse Procedure:

Accessed by Java application to pass the values. Should call the Return_Response procedure and pass the values received from Java.

Return_Response Procedure

The p_ema_lnpticket_tab is a sort of array that can have multiple values. Please see the example of values. Has all the business rules and validation that should be adhered.

Example of Vaules
p_in_call_request_id = 1
p_ema_resp_rec = 12345, 'Operation Failed'
p_ema_lnpticket_tab = (1,2,4,5)
p_endof_event = Y

View 2 Replies View Related

PL/SQL :: Type Attribute With Object Type Or Nested Table?

Mar 7, 2013

I have been creating lot many threads around the same problem, however i thought i knew but realized I do not know or else do not know how to. I have created object type with an attribute READINGVALUE NUMBER(21,6)...How can i use type attribute on this object while declaring variable.....can we use type attribute on NESTED TABLES, similar to the db tables?

example
CREATE TYPE READING AS OBJECT(READINGVALUE NUMBER(21,6));
CREATE TABLE INTERVALREADINGS OF TYPE READING;

View 5 Replies View Related

PL/SQL :: How To Access Type Object Variable Declared Inside Another Type Object

Mar 29, 2013

I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.

CREATE OR REPLACE TYPE typeObj1
AS OBJECT
   (
      SYSTEM_IDENTIFER                    VARCHAR2(50),
      PROCESS_TYPE                          VARCHAR2(50),
      abc                                            typeObj2
     
   )
/

[Code]...

/I have tried to access the type-object in where clause in following way

FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))

AND (ADTINF.ADDTN_RQST_TYP_VAL_DT  IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE(     ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM  IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE  IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )

In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.

I passed the values like this in proc execution.

T_T_A_I_V  :=  typeObj3('asdasd',NULL,NULL),
                       typeObj3('String654',NULL,NULL),
                       typeObj3('abcdef',NULL,NULL));                                    
T_T_A_I_I  :=  typeObj2('CampusCode',T_T_A_I_V),
                          typeObj2('PlanNumber',T_T_A_I_V);

What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct

View 4 Replies View Related

Server Utilities :: SQL Loader - For INSERT Option Table Must Be Empty

Mar 16, 2011

l am loading data i.e text file of huge size into oracle 9i db using sqlldr through command prompt, but my table contains already some data so while loading it shows error as

SQL*Loader-601: For INSERT option, table must be empty.

i cant truncate my table since present data is important.

View 3 Replies View Related

SQL & PL/SQL :: Index Of Table Type Object

Apr 4, 2012

I have a table type object loaded (tto) with data and am using it as :

SELECT tto.B, tto.C
FROM TABLE(tto)
WHERE tto.A = <some value>;

This is working fine. BUT, can I also select the index of each element too along with other fields of each element ?

View 16 Replies View Related

SQL & PL/SQL :: Create Object Then Use As Data Type Of New Table

Nov 11, 2012

I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...

create type UNIVERSITY as object (
U_RANK number(2),
U_SCHOOL varchar2(150),
U_COUNTRY varchar2(150),
U_SCORE number(3)
)

[code].....

DBMS_OUTPUT:
------------
Warnings: --->
W (1): Warning: execution completed with warning
<---
0 record(s) affected
[Executed: 11/11/2012 9:12:19 PM] [Execution: 31ms]

View 4 Replies View Related

SQL & PL/SQL :: Can Create Table Type Object By Using %ROWTYPE

Apr 20, 2012

Can we create TABLE type object by using %ROWTYPE in SQL.

I am bale to create PL/SQL table type object .But i am unable to create SQL type

SQL> declare
2 type table_emp is table of scott.emp%rowtype index by binary_integer;
3 employees table_emp;
4 begin
5 select * bulk collect into employees from scott.emp;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> create or replace type table_emp is table of scott.emp%rowtype index by binary_integer;
2 /

Warning: Type created with compilation errors

SQL> show errors
Errors for TYPE DBO.TABLE_EMP:
LINE/COL ERROR
-------- ----------------------------------------------------------
1/19 PLS-00355: use of pl/sql table not allowed in this context
0/0 PL/SQL: Compilation unit analysis terminated
SQL>

How can I create global table type object with %rowtype

View 3 Replies View Related

SQL & PL/SQL :: Inserting Object Type Into Table - Too Many Arguments

Mar 6, 2012

Ive just been trying to create an add member procedure to retrieve an object that is in another table. But before i get the ref to bring the toy across i wanted to make sure i could insert an object into the new table. I keep getting theres too many arguments, the lack of sqlplus code, the spool function isnt working.

DROP TABLE completed_toys;
/
CREATE OR REPLACE TYPE comp_toyobj AS OBJECT (
completed_id NUMBER(5),
request_id REF toy_obj,

[Code].....

Here is the error on the procedure call

Error report:
ORA-06550: line 4, column 9:
PLS-00306: wrong number or types of arguments in call to 'ADD_COMPLETED'
ORA-06550: line 4, column 9:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

View 11 Replies View Related

Server Utilities :: Processing Object Type SCHEMA_EXPORT / TABLE / INDEX

Apr 28, 2011

I am importing the dump.All the tables are getting imported but when it comes to the part as shown in the error it gets struck there and nothing is showed after.

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

The dumps for which I am doing the import is huge. Its around 46 GB.

select
sl.sid,
sl.serial#,
sl.sofar,
sl.totalwork
from
v$session_longops sl,
v$session s,
dba_datapump_sessions d
where
s.saddr = d.saddr
and s.sid = sl.sid
and s.serial# = sl.serial#
order by start_time;

It showing the lock for two sid's. Should I kill the process and import the dump again? I used below command to import dums

impdp sys/*****@dbname directory=dump_dir dumpfile=prod_dump_%U_200311 SCHEMAS=int_aaa int_bbb int_ccc int_ddd int_eee int_fff
int_ggg int_hhh int_iii int_jjj int_kkk int_lll

View 10 Replies View Related

SQL & PL/SQL :: Insert Data Into Object Relational Table

Dec 26, 2010

I had my previous table model as below.

CREATE TABLE Rel_Module (
Id NUMBER (6) PRIMARY KEY,
Type CHAR (7) NOT NULL,

[Code]...

How I can easily load data into this new REL_MODULE table either using SQL or PL/SQL (e.g., Procedures, Functions, and Triggers). I don't need to write so many INSERT statement.

View 20 Replies View Related

PL/SQL :: How To Call A Function Having OBJECT Type As Return Type

Mar 26, 2013

I've the following function returning OBJECT type. how to call this function

CREATE OR REPLACE TYPE GET_EMP_OBJ is object
   ( emp_name varchar2(50) ,
     mgr_id   number,
     dept_id  number
   );

[Code]...

The above function got created successfully. And i'm confused how to call this functions. I tried like below but didn't work

DECLARE
  t_emp_info_1  GET_EMP_OBJ ;
BEGIN
   t_emp_info_1 := get_emp(7566) ;
   for i in 1..t_emp_info_1.COUNT
      LOOP
         DBMS_OUTPUT.put_line ('Values are'||i.emp_name ) ;
     END LOOP;
END;  

View 7 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: Using Record Type And Object Type?

Aug 17, 2013

I want to return the output of this query using one OUT parameter from the procedure using RECORD type or OBJECT type.

SELECT empno,ename,sal FROM emp WHERE deptno=30;

Let us assume the query is returning 50 records.

I want to send those 50 records to OUT parameter using record type or object type.

View 20 Replies View Related

Precompilers, OCI & OCCI :: Unable To Insert XML Data Into Type Table?

Dec 16, 2008

I want to insert XML data into my ( Oracle 11G Release 1 ) XMLType table using OCCI. I'm getting

ORA-01461: can bind a LONG value only for insert into a LONG column

My XML data size is around 1.5 to 2MB. I have also tried using setMaxParamSize before calling the setString method. But, still I'm getting the same exception.

How to resolve this issue?

View 2 Replies View Related

Forms :: Loop Insert Empty Row?

Jul 3, 2010

there this is my insert loop but the problem is there is row added more than the normal count ..

DECLARE
var_record_count NUMBER;
var_p_request NUMBER;

[Code]....

View 4 Replies View Related

PL/SQL :: Insert Values Into EMP Table

Jul 26, 2013

I have a table EMP having columns User_Id, ENO,Org_ID, Dept_ID.Now I would like to insert values into this EMP table using below conditions.Insert into EMP

(user_seq.nextval,(select empno from employees where empno in(....(empnumbers),  (select org_id from organizations where  org_name=' XXXXXXXXXX'),  (select dept_id from DEPT where dname in ('MANAGER','ANALYST','SALESMAN') )) 

provide me the query for the above requirement.

View 40 Replies View Related

INSERT INTO Table (dateadded) VALUES (NOW())?

Apr 24, 2007

In MySQL I can insert the current timestamp by using MySQL's proprietary NOW() function.

Does Oracle have a similar function or method for adding a current timestamp with an INSERT?

View 1 Replies View Related

Forms :: Insert Values In A Table Through Ref

Aug 26, 2011

Table Structure:
Name of Table: info
Column: ID, Name, Cell,Cell1,Ref

i want to insert these values in a table through ref (key next item).
-----------------------------------------------------------
Key next Item Triger
DECLARE
A NUMBER;
B NUMBER;
BEGIN
SELECT :CELL,:CELL1 INTO A,B FROM DUAL;
[code].......
-------------------------------------------------------
but when i press enter(ref Key_next_item) it will return error:

FRM-40735: KEY_NEXT_ITEM RAISED UNHANLEDED EXCEPTION ERROR: ORA 40102

View 12 Replies View Related

Insert Values Into A Nested Table?

May 15, 2013

Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table:

CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT
(
PONo NUMBER,
CUST_ref REF Customer_objtyp,
OrderDate DATE,
ShipDate DATE,

[code]...

The LineItemList_ntab is a nested table.

Here is the create code for LineItemList_ntabtyp:

CREATE TYPE LineItem_objtyp AS OBJECT (
LineItemNo NUMBER,
Stock_ref REF StockItem_objtyp,
Quantity NUMBER,
Discount NUMBER
)

[code]...

In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.

Here is some of the code I have tried to insert values with:

INSERT INTO PurchaseOrder_objtab
SELECT 1008, REF(C),
SYSDATE, '12-MAY-1999',
LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004,
NULL

[code]...

The first insert statement above produces the following error:

SQL Error: ORA-00933: SQL command not properly ended

The second insert statement above produces the following error:

SQL Error: ORA-00936: missing expression

successfully insert values into the LineItemList_ntab nested table?

View 6 Replies View Related

SQL & PL/SQL :: Fetch Into Object Type?

Oct 2, 2011

what ios wrong in the following code

create or replace type testobj as object(col1 number);
create or replace type tabtest as table of testobj;
create or replace procedure proc(a out tabtest) is
cursor c is

[code]..

View 5 Replies View Related

PL/SQL :: Displaying Value Of Object Type

Aug 30, 2012

I have created one type as Object and I am trying to display the values available in object type for debugging purpose. To display the contents of object type.

View 4 Replies View Related

SQL & PL/SQL :: Varray Of Object Type

Jun 23, 2010

I want to pass varray of object as out parameter (more than 1 ), Tested the below code getting error.

Note : I want to get value from table and assign it to varray object and pass varray as out parameter.

Here below the code I tested.

create type emp_type as object (
emp_no number,
emp_name
);
create type emp is varray(10) of emp_type;
[code]........

Getting error while above code.

View 11 Replies View Related

SQL & PL/SQL :: Insert Into Oracle Table Of UNIX Script Values

Dec 24, 2012

I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table.

E.g. : insert into tbl_daily_status values(STAT,ENDTIME);

#!/bin/ksh
joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det"
for i in $joblist
do
STAT=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $6}'`
ENDTIME=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $4" " $5}'`
echo "$STAT"
echo "$ENDTIME"
done

View 2 Replies View Related

PL/SQL :: Insert Values Into A Nested Table Or Array With A Loop

Oct 8, 2013

How to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario: I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. I've posted somewhat of a pseudo coded example below  

If  (you_belong_in_table)  then
variable := store_me_in_varray      /* OR */
variable := array_type(sector.sector_name)
i := i + 1;
end if;

/* Now we output our varray or table */
start loop
output(sector names one by one)

end loop I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well. 

View 7 Replies View Related

SQL & PL/SQL :: Difference Between Object Type And Package

Oct 11, 2011

understand between Oracle pl/sql object type and package!

View 3 Replies View Related

PL/SQL :: Referencing Remote Object Type

Aug 5, 2013

How can I reference an Object Type created on a remote database?This is the escenario:   

In DATABASE A:   CREATE OR REPLACETYPE  USERA.EXPO_EXPEDIENTES_RECAUDOS OID 'DCADCB2EA2344DFAB1D205C03D708359' AS OBJECT (   exer_cd_expediente  VARCHAR2 (50),   exer_id_ident_expediente   VARCHAR2 (30),   exer_cd_sucursal   NUMBER (3),   exer_cd_ramo NUMBER (2),   exer_nu_poliza   NUMBER (7),   exer_nu_certificado  NUMBER (9),   exer_nu_contrato  NUMBER (7),   exer_cd_nacionalidad  VARCHAR2 (1),   exer_nu_cedula_rif   NUMBER (9),   exer_nm_titular   VARCHAR2 (70),   exer_st_expediente  VARCHAR2 (2),   exer_de_status_exp   VARCHAR2 (240),   exer_fe_status_exp   DATE,   exer_cd_productor   NUMBER (5),   exer_nm_productor  VARCHAR2 (60),   exer_cd_mail_productor  VARCHAR2 (50),   exer_in_habilitado   VARCHAR2 (1),   exer_in_permite_habilitar  VARCHAR2 (1),   exer_in_carga_consulta  VARCHAR2 (1),   exer_cd_ramo_aplicacion    VARCHAR2 (200),   exer_cd_producto  VARCHAR2 (6)  )/ In DATABASE B:   

After creating the public synonym and asigning the required privileges on the object in DATABASE A, I try to execute the following:  

DECLARE x  EXPO_ EXPEDIENTES_RECAUDOS;BEGIN null;END;   

But i got the following error:

ORA-06550: line 2, column 12:PLS-00331: illegal reference to

USERA. EXPC_ EXPEDIENTES_RECAUDOS@DATABASEA   

After investigating a little i found the following, but i dont know how to apply it, "The CREATE TYPE statement has an optional keyword OID, which associates a user-specified object identifier (OID) with the type definition. It should be used by anyone who creates an object type that will be used in more than one database."

View 4 Replies View Related

Forms :: Hierarchical Tree - Retrieve Values And Insert Into Table

Apr 19, 2010

My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.

I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.

View 2 Replies View Related







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