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


ADVERTISEMENT

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

WebService - How To Create Record Type And PL/SQL Table

Oct 7, 2010

I am working on a webservice call from ORacle.I have a button on my form application called verify.Wheni click on verify button , a pl.sql procedure should be invoked and that procedure will call .net webserive to validate the address , the result from the webserivce will be in xml.I have to extract the xml into some variables and return these varibles to Forms application..I am plannig to use pl/sql table to store the result from web service call.

here are the output values:

Customer_Id varchar2(20),
ErrorCode varchar2(30),
ErrorDesc varchar2(3000),
Fcount number,
FErrorCode -- this is array,-- if fcount >1 then these values will be repeted.
FErrorDesc -- this is array,
FStatusCode -- this is array,
FStatusDesc -- this is array,
Street varchar2(3000),
Street2 varchar2(3000),
Suite varchar2(20),
City varchar2(20),
State varchar2(2),
Zip_Code varchar2(10)

create record type and pl/sql table for these.I want to return pl/sql table as a out parameter to the form.

View 1 Replies View Related

SQL & PL/SQL :: How To Record / Nested Table Type Based On Row Returned By Week

May 10, 2011

Is there a way we could define a record or a nestedtable with a type based on weak refursor i.e

TYPE RC IS REF CURSOR;
C2 RC;
Type t is table of c2%rowtype;
Following is some more explanation of what I am trying to do.

I have a table T with column A and B. Column A is a primary key with number 1,2,3,4,5,6, Column B has diffrent sql stmts stored. i.e 'Select * from emp', Select count(1) from dept' and so on. So table will look like

1 Select * from emp
2 Select count(1) from dept

Now I want to select statements stored in table T one by one and execute them by using cursor. Problem arises as i need to fetch the cursor into some variable but the outcome of each statment is diffrent and oracle does not allow to use cursorname%rowtype for a weak ref cursor.

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

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 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 :: 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 :: Getting Data From Record Type

Jul 19, 2010

I have one requirement. We have a Package which consists of 2 Stored Procedures which has a RecordType output Parameter.

FUNCTION GET_NFE_INFO(O_status_code IN OUT NUMBER,
O_error_message IN OUT VARCHAR2,
O_message IN OUT "OBJ_FM_NFE_DOCHDR_REC",
I_fiscal_doc_id IN FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE)
return BOOLEAN is

I am working on a one Stored Procedure which would display the output from the above function as,

set serveroutput on size 9000;

DECLARE

L_stg_status FM_FISCAL_DOC_HEADER.STATUS%TYPE := 'NFE_P';
L_fiscal_doc_id FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE := 12325;
L_dummy VARCHAR2(1) := NULL;
L_status_code NUMBER(1) := 0;
L_error_message VARCHAR2(255) := NULL;
L_message "OBJ_FM_NFE_DOCHDR_REC" := NULL;

[Code]..

Here for each and every data value i am Printing it using DBMS_OUTPUT, L_message record has more than 100 columns. Is there a easy way of handling it instead of using DBMS_OUTPUT ?

View 20 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 :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies View Related

SQL & PL/SQL :: Converting XML Message To Record Type

Apr 8, 2010

We have a queue in which the message is coming from external system. The payload of the queue table is a PL/SQL record type. Once we get the message in the queue, we de-queue the message and read through the PL/SQL type collection and process the message.

From the below query, we are able to convert the PL/SQL collection message to XML message and see the data.

SELECT dbms_xmlgen.getxml
('SELECT USER_DATA
FROM <Queue_table> X
WHERE X.USER_DATA.SALE_ORDER.P_HEADER_REC.ORIG_SYS_DOCUMENT_REF=800501298')
FROM dual;

The new requirement is the message would come in a XML message in the queue. So my question is, is there any way through which the XML message can be converted to the PL/SQL record structure directly (it would be the opposite operation of the above query).

View 4 Replies View Related

SQL & PL/SQL :: Select All The Elements Of A Record Type

May 24, 2011

Is there any way that I can check what are the elements present in a pl sql record type by querying in table?

For example if I want to check what are elements present in oe_order_pub.header_rec_type and I don't want to open the package, then in which table I should query? Is it possible?

View 2 Replies View Related

SQL & PL/SQL :: Nested Loop Using Record Type?

Jul 29, 2013

I am using a record type to print some column in a same line.

Eg: I want to create index on some composite key columns. But i dont know how many columns are there. So want to use a loop which will count the number of column and then create the index like:

CREATE INDEX PRODUCT.XIF1AGMNT_PROD ON PRODUCT.AGMNT_PROD(LOAN_ID,LOAN_PROD_STRT_DT) TABLESPACE PRODUCT_INDEX;

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 :: Dynamic Procedure Name With Record Type I/p Parameter?

Jun 22, 2012

I have a problem with passing procedure name dynamically with record type i/p parameter..I'm not attaching any insert/create table statements, as I'm unsure of forming the sql statement dynamically..

CREATE OR REPLACE PACKAGE med_order_pkg AS
TYPE deid_med_order_typ IS RECORD(....)
L_deid_med_order_typ deid_med_order_typ;
PROCEDURE RULE_MASTER_PRC (P_IN_RULE IN deid_med_order_typ);
END;

[code]....

From the above, I need to execute a procedure with record type as i/p parameter..V_SQL should form the statement & execute another procedure which comes into the variable V_MSG_PROC .I'm having difficuly in forming the statement...(I did it by hard-coding the procedure with parameter in the next line which is commented out & it works...So how can I modify V_SQL in the above statement?

View 3 Replies View Related

SQL & PL/SQL :: Create Record Type With Rowtype And One Field

Oct 11, 2012

using as template this table:

create table t1 (c1 number,c2 number);
CREATE OR REPLACE
TYPE REC IS RECORD (
R1 T1%ROWTYPE,
R2 NUMBER
);

I'm trying to create one RECORD type with all the columns from table T1 + one new field R2. But gives me an error.

The point to use T1%ROWTYPE and not to hardcode the columns from T1, is due to if we add a new column to T1, is created when is executed again RECORD definition and not to add the column.

View 3 Replies View Related

SQL & PL/SQL :: Declaring Record Type In Package Spec?

Mar 29, 2013

I need to declare a record type in a package spec and used that define record type as a parameter in a procedure.

A procedure will call this package passing a record type . e.g xxtest.tmpprc(employee_rec employee_type);

(TYPE record_type_name IS RECORD (column_name1 datatype, column_name2 datatype, ...);
CREATE OR REPLACE package xxtest as
PROCEDURE tmpprc(trecordType IN VARCHAR2);
END;

View 16 Replies View Related

SQL & PL/SQL :: Functional Difference Between Type %rowtype And Record

May 17, 2010

What is functional diffrence between type, %rowtype and record.

View 3 Replies View Related

SQL & PL/SQL :: Executing Dynamic Procedure With Record Type

Jul 26, 2012

I'm not attaching any tables / data..etc...I just want to know how to pass the record type to a procedure (which are actually obtained from a table) -- see ** below where I'm getting an error..Need to pass the whole record type "l_shl_order_msg"

CREATE OR REPLACE PROCEDURE CM_BUILD_MSG_PRC (P_IN_BLD_MSG_CURSOR IN SYS_REFCURSOR,
P_OUT_BLD_MSG_CURSOR OUT SYS_REFCURSOR)
IS
l_shl_order_msg CRAE_INTERFACE.GLB_VAR_PKG.deid_SHELL_order_typ;
V_MSG_SHELL_NAME VARCHAR2(1000);
V_MESG_TEXT_SEGMENT VARCHAR2(1000);
V_TEXT VARCHAR2(1000);
V_MSG_TEXT VARCHAR2(4000);
V_MSG_FINAL_TEXT VARCHAR2(4000);
V_MSG_PROC VARCHAR2(1000);
V_SQL VARCHAR2(4000);
V_CNT NUMBER;
L_STATUS VARCHAR2(100);
L_REASON VARCHAR2(1000);
[code]...

I get an error saying that "wrong number or types of arguments in call to ||"..Not sure how to pass record type dynamically...

View 10 Replies View Related

SQL & PL/SQL :: How To Access The Record Type Elements Of Varray

Jun 11, 2013

i have created one varray whose elements are of record type. Now how can i access those record type elements?

structure of table t1:
select * from t1;

IDDESCRIPTION

1a
2b
3c

select * from t2;

ID1DESCRIPTION1

4aa
5bb
1cc

declare
type r1 is record (id t1.id%type);
type r2 is record (id1 t2.id1%type);
type r3 is record (id1 r1, id2 r2);
type var1 is varray(20) of r3;
[code].......

View 13 Replies View Related

Overload PL / SQL Procedure Based On Different Record Type With Same Structure

Apr 8, 2004

I have two cursors like

cursor_A IS select * from table_a where condition_A;
cursor_B IS select * from table_a where condition_B;
record_A is a recorded of cursor_A%ROWTYPE
record_B is a recorded of cursor_B%ROWTYPE

I define a procedure like pro_A(record_in cursor_A%ROWTYPE) can I overload this procedure by defining pro_B(record_in cursor_B%ROWTYPE)?

If I can't, Can I call pro_A by passing record_B as the parameter to it?

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Calling Function With Type Record Parameter?

Jul 23, 2010

I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.

My code (sample):
-----------------
DECLARE
type r_parameters is record
(cd_query cons_query_param.cd_query%type,
cd_usuario cons_query_user.cd_usuario%type,
nr_param cons_query_param.nr_param%type,
vl_param varchar2(2000),

[code].....

View 5 Replies View Related

SQL & PL/SQL :: Getting Error When Record Type Is Used / PLS-00302 / Component (SAL) Must Be Declared

Sep 3, 2013

Getting error when using record type as in parameter.

PLS-00306: wrong number or types of arguments in call to 'SAL_UPDATE_PROC'
PLS-00302: component 'ENAME' must be declared
PLS-00302: component 'SAL' must be declared
CREATE OR REPLACE PACKAGE emp_details_proc

[code]....

I am not getting any error.

View 16 Replies View Related

Get Table Name / Constraint Name / Constraint Type With Join Processes In String Type

Dec 25, 2007

i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)

View 1 Replies View Related

SQL & PL/SQL :: Query With %type?

Sep 18, 2011

I have a query in this code?

declare
old_name varchar2(20) not null:='stephen';
new_name old_name%type;
begin
dbms_output.put_line(new_name);
end;

will new_name variable will inherit the datatype only or the default value as well?The output of this code will be stephen or not?

View 7 Replies View Related

SQL & PL/SQL :: Using N In Query Type

Aug 23, 2011

what is the use of using 'n' in a query type as mentioned below:

select * from scott.emp a where n =

View 11 Replies View Related

How To Get Type / Length And Flags By Query

Feb 19, 2007

how to get the type, length, flags in oracle by query..??

View 1 Replies View Related







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