SQL & PL/SQL :: Inserting Result Set Of Query Into Corresponding SQL Table Type Variable?

Aug 28, 2012

I am unable to insert the result set of query into corresponding SQL Table type variable where as same functionality can be accomplished by PL/SQL table type variable. Can't we access the same by using SQL type variable?

Ex:

Step 1:

SQL Object type , Table type Objects creation :
drop type sql_emp_tab_type ;
drop type sql_emp_type ;
create or replace type sql_emp_type as object
(
empno number,
ename varchar2(20),

[code]...

Step 2: Accessing the table type object from PL/SQL block

SQL>
SQL> declare
2
3 tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 begin
6
7 tab_type_var.extend(10);

[code]...

Step 3: Instead of SQL Table type , if we define the corresponding PL/SQL table type variable

SQL> declare
2
3 --tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 type pl_sql_emp_type is record
6 (
7 empno number,

[code]...

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Inserting Result Set Of Query Into Another Table

Jul 24, 2012

CREATE TABLE tbl_emp
(
name VARCHAR2(20),
price NUMBER,

[Code]...

NAME PRICE DATAENTRD ROWRANK

aaa 9999 24.07.2012 05:56:00 1

aaa 10000 24.07.2012 05:55:58 2

I want to insert this result into another table, how can I do it??

Quote:TABLE

CREATE TABLE tbl_emp_result_set
(
name VARCHAR2(20),
price NUMBER,
dataEntrd date,
rowrank number
)

View 8 Replies View Related

Inserting Table Data Into A Variable

Sep 3, 2012

I am trying to insert a column into a variable from a trigger.

Here is the code that i have:

CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
FOR EACH ROW
DECLARE
v_key varchar2(10);
BEGIN
select ID into v_key from buyer;
insert into message_log_table (table_name, message_comments)
values
('Buyer', 'Buyer '||v_key||' has been updated');
end;
/

When I run the above I get the following compiler error:

[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier

Since ID is defined in my BUYER table I do not understand what the error means.

Here is my create table statement:

CREATE TABLE BUYER
(
ID VARCHAR(50) NOT NULL PRIMARY KEY,
FNAME VARCHAR(50) NOT NULL,
LNAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP_CODE NUMBER(5) NOT NULL
);

View 1 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

PL/SQL :: Declare Variable As Row Type For A Table?

Jul 19, 2012

Can I declear a variable in PLSQL as the row type for a table, who's name is unknown during compile time, but will be determined when the PLSQL is runnning. The code is like following:

Procedure operTable( tableName IN VARCHAR2)
IS
TYPE ty_Row IS tableName%ROWTYPE
v_Row ty_Row;
CURSOR v_quey_cur

[code]...

View 5 Replies View Related

PL/SQL :: ORA-03113 When Inserting CLOB Value Casted As XML From SELECT Query Into Table?

Aug 15, 2013

I have a table that contains a CLOB column with pseudo-XML in it. I want to keep this data in an XMLType column so that I can leverage some of Oracle's built-in XML features to parse it more easily.

 The source table is defined as: CREATE TABLE "TSS_SRM_CBEBRE_LOGS_V" ( "INCIDENT_ID" NUMBER, "EVENT_TYPE" VARCHAR2(100 BYTE) NOT NULL ENABLE, "EVENT_KEY" VARCHAR2(100 BYTE), "CREATION_DATE" TIMESTAMP (6) NOT NULL ENABLE, "CREATED_BY" VARCHAR2(100 BYTE) NOT NULL ENABLE, "LOG_MSG" CLOB); 

The target (for testing this problem) table is defined as: CREATE TABLE "TESTME" ( "LOG_MSG" "XMLTYPE")  My query is: insert /*+ APPEND */ into testme ("LOG_MSG")select XMLTYPE.createXML("LOG_MSG") as LOG_MSG from "TSS_SRM_CBEBRE_LOGS_V" b; In SQL*Developer, my error is: Error report:SQL Error: No more data to read from socket In SQL*PLUS and Toad, my error is: ORA-03113: end-of-file on communication channelProcess ID: 13903Session ID: 414 Serial number: 32739

View 6 Replies View Related

SQL & PL/SQL :: Query Where Condition Has 4 Rows In Table But Result Is Zero Row

Oct 29, 2012

There are 4 rows in table with stat_flag 'Updated Record' and stat_date with todays date.

stat date has date & time both, for that reason just trying to format with yyyy.mm.dd

I am getting zero rows as result.

where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')

View 4 Replies View Related

SQL & PL/SQL :: Show Result Of Query Just Like Pivot Table?

Jul 20, 2011

me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),

[code]...

I want that my query should look like the format as attached in the xls sheet.

View 1 Replies View Related

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

PL/SQL :: How To Use Value Taken In Variable As Table Name In Query

Jul 22, 2013

I am fetching a value in a variable as: 

<select application_short_name into l_appl_nm from fnd_application where application _id=:p_appl_id> 

Now I need to use the value fetched in variable "l_appl_nm" as a table partition name in next query. 

View 8 Replies View Related

SQL & PL/SQL :: Table Type Of RECORD In A Query?

Apr 2, 2012

I am in the need of using a table type object in SQL query.

I have a package which has a spec in which I have declared :

TYPE TESTREC IS RECORD
(
RE_ID NUMBER(9),
RATING_TARIFF_NAME VARCHAR2(40),
);
TYPE TESTTABTYPE IS TABLE OF TESTREC;
TTR TESTTABTYPE;

In one of the package procedures I am collecting data into the above indicated table type object (TTR):

SELECT
RE_ID,
RATING_TARIFF_NAME
BULK COLLECT INTO TTR
FROM TESTPACKTAB;

This works fine. The values get collected into TTR and am able to print them too.

But when I :

SELECT
AA.RATING_TARIFF_NAME
INTO v_name
FROM
TABLE( TTR ) AA ;

in the same procedure immediately after the collection I get the error while running the procedure :

ORA-21700 : Object does not exist or is marked for delete.

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

SQL & PL/SQL :: Stop Inserting Data Into A Table Before It Inserting Using Trigger?

Jul 26, 2011

shall we stop inserting data into a table before it inserting using Trigger?

View 3 Replies View Related

Client Tools :: Inserting Data With Substitution Variable?

May 24, 2012

I want to insert the value with '&'. But this is treated as substitution variable by oracle.So how can it is possible to do like this

INSERT INTO DEPT (DEPTNO, LOC, DNAME)
VALUES (50, '&NEW YORK', 'SALES');

View 3 Replies View Related

SQL & PL/SQL :: Creating PLSQL Collection Table Type With Dynamic Query?

Jul 12, 2013

How can I create PLSQL table type dynamically?

I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.

View 2 Replies View Related

SQL & PL/SQL :: How To Execute Type Variable Procedure

Apr 26, 2013

I have created the following procedure. Since I am using this first time I don't know how to execute this.

CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package
IS
TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;
PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots );
END;
/

[Code]...

View 2 Replies View Related

SQL & PL/SQL :: How To Determine The Data Type Of A Variable

Sep 15, 2010

Code to determine the data type of a variable in oracle 10g.

View 5 Replies View Related

PL/SQL :: Get Distinct Value From Array Type Of Variable?

May 14, 2013

i have written one procedure which is giving an error PL/SQL:ORA-00902: invalid data type, how to get the distinct value.

there is one table xyz and this table having so many attributes and file_data one of the attribute which is having CLOB data type. I have to find out distinct value in each record of file_data.

suppose for example

TABLE XYZ

col1 col2 file_data
A     B      <CLOB VALUE>------------------------ this CLOB VALUE containing duplicate record
V     X      <CLOB VALUE>------------------------- this CLOB VALUE also containing duplicate record

create or replace procedure test_dealer_upload
IS
t_out_file UTL_FILE.file_type;
t_buffer VARCHAR2(32767);

[Code].....

View 3 Replies View Related

SQL & PL/SQL :: How To Declare A Variable Which Reference Level Type

Aug 5, 2010

I have a problem :

In package head I declare a variable , which reference 'level' type , but I don't know how to declare the variable , because I don't know what type of 'level'.

View 9 Replies View Related

SQL & PL/SQL :: Want To Define Dynamic Type For A Variable In Oracle 10g

Jul 17, 2013

I m trying to give table name as parameter to this function. It is compiling properly.But when an anonymous block is created to call this table value, it has to be fetched into a variable of Rowtype of this inputted table. So I am not able to create any ROWTYPE variable for this table dynamically.

//Function
create or replace function instant_tabula(tabula in varchar) return sys_refcursor
as
cur sys_refcursor;
str varchar2(20000);
begin
str := 'select * from ' || tabula;
open cur for str;
return cur;
end;
[code]....

View 7 Replies View Related

Forms :: How To Parse Paramlist Type Variable

Mar 22, 2010

how to parse paramlist type variable to get names of the fields and their values in the paramlist?

View 1 Replies View Related

PL/SQL Program Units - Global Variable Specification / Type

Jul 31, 2012

This assignment allows you to pick your own subject in which you Code a Package Header that contains the following:

Global Variable Specification
Type
Four (4) Procedure Specifications
Two (2) Function Specifications

Submit your code in a series of Notepad or Wordpad documents. Include the code for the assignment and the output that is realized from running the code. Note: You are responsible for creating the database on which the project will run. This includes the responsibility for loading the database with sufficient data.Grading for this assignment will be based on answer quality, logic/organization of the project to include the compilation, and execution of the project code.

View 2 Replies View Related

PL/SQL :: Dynamically Assign Values Of Any Column Of Row Type Variable

Nov 12, 2012

need to dynamically assign value of each column of a row type variable:The example is like that:

Table "Student_list" is as follows:
---------
COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50)
[code]....

Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?

View 7 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

SQL & PL/SQL :: ORA-02303 - Cannot Drop Or Replace A Type With Type Or Table Dependents

Feb 1, 2012

i am trying to run a script in which a command tries to create or replace a type.

i get this error:

ORA-02303: cannot drop or replace a type with type or table dependents

SQL>
SQL> --create a test user:
SQL>
SQL> create user tuser identified by tuser

[Code]....

Table created.

SQL>
SQL> --then change the type:
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
create or replace type t1 as object (obj_type number(15))
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

SQL>
SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table:
SQL>
SQL> drop type t1 FORCE
2 /

Type dropped.

SQL>
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)

SQL>
SQL>
SQL>
SQL> --if i re-create it - my table is still corrupted:
SQL>
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /

Type created.

SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)

SQL>

--if i re-create it - my table is still corrupted:

create or replace type t1 as object (obj_type number(15))
/
desc dpntnt_table
[/code]

1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?

2. I understand that this type is already assigned to some object, but i can't seem to find out which one.

how do i find out which object is depending on the type i want to create or replace?

View 4 Replies View Related

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 :: Cannot Drop Or Replace A Type With Type Or Table Dependents

May 12, 2011

When i tried to drop a type using below command , i received errors

DROP TYPE JAM_ACAS_MSG_TYPE

ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents

View 32 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

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

SQL & PL/SQL :: Converting Record Type In Table Type

Jul 12, 2010

how to convert a record type to a table type?

View 4 Replies View Related







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