SQL & PL/SQL :: PL-00382 Expression Is Of Wrong Type

Apr 12, 2013

I am using pipelined functions. I've written a few with no problem this one seems to be giving an error when I am using techniques that appear very similar to ones that work.

I am doing this all in a package;

The type definition is;

TYPE SUSPECT_LINKAGES_FAC_RECORD IS RECORD
(
PATIENT_ID TUMOR.TUMOR_PATIENT_ID%TYPE,
CENTRAL_SEQ TUMOR.TUMOR_CENTRAL_SEQ%TYPE,
MP_REVIEW_FLAG NUMBER(1),
FACILITY_FLAG NUMBER
);

The variable definition is;

OUT_REC SUSPECT_LINKAGES_FAC_RECORD;

The line with the error is;

PIPE ROW(OUT_REC);

This is the entire function;

FUNCTION GET_SUSPECT_LINKAGE_FAC_FLAGS RETURN SUSPECT_LINKAGES_TABLE PIPELINED AS
CURSOR CURS_SUSPECT_LINKAGES IS
SELECT * FROM TABLE(TUMOR_UTILITIES.GET_SUSPECT_LINKAGE_FLAGS())
order by 1,2,3 DESC;
TEMP_REC SUSPECT_LINKAGES_RECORD;
MATCH_COUNT NUMBER;
OUT_REC SUSPECT_LINKAGES_FAC_RECORD;

[code].....

I get "PL-00382 expression is of wrong type" on both pipe row (out_rec); lines.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: PLS-00382 - Expression Is Of Wrong Type

Mar 19, 2013

i got this error 'PLS-00382: expression is of wrong type'
--declaration

l_recipe_detail_tbl apps.gmd_recipe_detail.recipe_detail_tbl;
begin
ln_recipe_id := NULL;

[Code].....

View 1 Replies View Related

SQL & PL/SQL :: FORALL / PLS-00382 Expression Is Of Wrong Type

May 23, 2012

I am running some FORALL...UPDATE statements in a dynamic anonymous blocks and I am seeing this intermittently

ORA-06550: line 10, column 28:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 17:

[Code].....

View 9 Replies View Related

SQL & PL/SQL :: Conditions Of Clause IF / PLS-00382 Expression Is Of Wrong Type

Jun 4, 2010

I putted in a table "conditions" some rules (if conditions) and I want to read and execute those conditions in another table "list_parameters" in pl/sql procedure.

conditions :
ID||||||||||||||RULE
1-----------(param1 = F)
2-----------(param2 is null)
.....

list_parameters :
id_task|||||param1|||||param2|||||param3|||||param4
--x-----------F---------Z----------NULL-------NULL
--y----------- ---------A----------K-----------L
.........

How can I use the conditions of clause IF from table "conditions"? Is it possible to do:

CURSOR cur_rules IS select * from conditions;
BEGIN
FOR c1_cur in cur_rules

[Code]....

View 12 Replies View Related

PL/SQL :: PLS-00306 - Wrong Number Or Type Of Argument In Call To MULTISET_UNION_ALL

Sep 7, 2012

I'm trying to use MULTISET UNION to append several collections into a single recordset.The select queries shown for the CURSOR are simple beta versions of the real queries which have about 350 fields to each of them. I will be appending data from 15 of these queries which each will with no more than 20 records per query into a new single recordset (not sure if 'recordset' is the correct phrase or term for what I mean, it could be ARRAY, OBJECT, COLLECTION or something else all together).

Here is text of the ERROR message:

ORA-06550: line 63, column 1:
PLS-00306: wrong number or type of argument in call to 'MULTISET_UNION_ALL'

ORA-06550: line 62, column 1:
PL/SQL: Statement ignored

ORA-06550: line 71, column 36:
PLS-00487: Invalid reference to variable 'VARCHAR2'

ORA-06550: line 71, column 5:
PL/SQL: Statement ignored

I think the error has to do with some defect in my declare statements.

DECLARE
  MyTID varchar2(10);

  CURSOR Cursor_rst1 IS
      SELECT d_owner_internal_id,
             d_internal_id,
             d_tid,
             d_entity_name,
             d_form_seq
        FROM rtns.itas_rtn_ct_1120_cor tblRecords
       WHERE d_form_seq = '2710' --Tax Year =2003
         AND D_TID = MyTID;
[code]....

View 3 Replies View Related

PLS-00320 / Declaration Of Type Of Expression Is Incomplete Or Malformed

Jul 16, 2013

I have this Error in PL /SQL procedure  ORA-06550: line 6, column 12:PLS-00320: the declaration of the type of this expression is incomplete or malformedORA-06550: line 6, column 12:PL/SQL: Item ignoredwhere this is my procedure

CREATE OR REPLACE PROCEDURE MOAMALAT."IO_EMP_REP" (P_FROMDATE IN NUMBER,P_TODATE IN NUMBER,P_EMPID IN NUMBER,RCT1 OUT GLOBALPKG.RCT1)ASBEGINOPEN RCT1 FOR SELECT COUNT (I.CORRESPONDENCENUMBER) cont,EMP.FULLNAME empname,D.DEPARTMENTNAME deptnameFROM MOAMALAT.IO_INCOMING i,MOAMALAT.IO_EMPLOYEES emp,MOAMALAT.IO_DEPARTMENTS dWHERE I.RECEIVEDBY = (SELECT EM.USERIDFROM MOAMALAT.IO_EMPLOYEES emWHERE EM.EMPLOYEEID = P_EMPID)AND I.RECEIVEDBY LIKE EMP.USERID--and EMP.DEPARTMENTID=1900AND I.RECEIVEDBYDEPARTMENTID = D.DEPARTMENTIDAND I.CORRESPONDENCEDATE BETWEEN

[code]....

View 4 Replies View Related

Application Express :: Performance Depending On Condition Expression Type

May 24, 2013

I was just wondering what people's thoughts and experiences are regarding performance of conditional expressions.

Tony blogs about it here

[URL}.........

And gives the example

1) Value of item/column in Expression 1 = Expression 2Expression 1: P1_JOB

Expression 2: SALESMAN

2) PL/SQL Expression

Expression 1: :P1_JOB = ‘SALESMAN’>

Recently I've been trying to eke every shortcut I can with an application and noticed this can be a contributing factor when done dozens of times within a page render.

View 1 Replies View Related

SQL & PL/SQL :: Expression Must Have Same Datatype As Corresponding Expression

Dec 22, 2010

ved>create table test900 ( a number, b number);

Table created.

ved>insert into test900 values( 9,'');

1 row created.

ved>insert into test900 values( 10,null );

1 row created.

ved>select * from test900;

A B
---------- ----------
9
10
ved>select nvl(length(b),0) from test900;
[code]....

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression..Why the above sql ( case 2 ) gives error?

View 9 Replies View Related

SQL & PL/SQL :: Backward Accessing Super Type Attributes From Sub-type Body In Oracle Collection?

Jan 2, 2013

I have 3 user defined collection types. I am trying to access the type3's attribute in type1 body (like backward accessing).

Ex. My Collection Types Structure (something like master detail structure)

create type type1 as object
(
attr1 varchar2(10),
attr2 varchar2(10),
member procedure function1

[code]...

so, in the type1 body i have to get or assign the value either to type2's attribute or type3's attribute. I have search all the in internet but i haven't find anything such. how to find the reverse method of accessing the super type's attribute(s) in sub type's body.

View 3 Replies View Related

Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type

Oct 17, 2011

Is there some functions to convert the long type field data to varchar2 type?

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

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

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 :: 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 :: Converting Record Type In Table Type

Jul 12, 2010

how to convert a record type to a table type?

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

XML DB :: Populate Collection Type From XML Type

Mar 5, 2013

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE     11.1.0.7.0     Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

I'm new to this XML DB thing and also this is my first post.

I'm trying to populate collection type from XMLType. I was able to populate a table from XMLType but any way to populate the collection type. Here is the description of my problem:

Object Type:
CREATE OR REPLACE TYPE DOC_ROWTYPE AS OBJECT
(
     REFERENCENUMBER VARCHAR2(255),
     REQID NUMBER(12),

[Code]....

Collection Type:

CREATE OR REPLACE TYPE DOC_TABLETYPE IS TABLE OF DOC_ROWTYPE;

I have a physical table which is created when I registered a schema.

A table (Temp_Result) got created with column SYS_NC_ROWINFO$ which is of XMLType.

As you can see this is only a temporary table which will store the response XML which I want to finally get it to collection type.

XML to parse:
<code>
<TFSResponse>
<TFS>
<refNumber>12345</refNumber>
<reqId>123</reqId>

[Code]...

So each object in the collection is one TFS tag. how to implement this?

View 9 Replies View Related

Format Of Dates Goes Wrong

Jan 26, 2013

I have database jobs that upload the data in my applications.My problem is while copying the record of one application to another, the format of dates goes wrongFor example:

The date in one column is 01-JAN-1941 but in the other record while copying ot goes as 01-JAN-2041.

View 4 Replies View Related

SQL & PL/SQL :: How To Find Out Wrong Value From Table

Jan 13, 2011

I've written the code (see below) and after run I get an error:

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

I don't know how to find out the wrong value from table. How to find it out?

DECLARE
TYPE rowids IS TABLE OF ROWID;
r1 rowids;
type t_varchar is table of varchar2(50);
n1 t_varchar ;

cursor c1 is select e.rowid rid,msisdn_displayed
from the_table
where contract_id is not null;
BEGIN
OPEN c1;
[code].........

View 3 Replies View Related

Datafile In Wrong Location

Jun 18, 2013

My production DB has a couple of datafiles that were created in the wrong place, plus they are tiny - 100mb each.  What is best way to get rid of them?

View 3 Replies View Related

SQL & PL/SQL :: Wrong Result For Query With Like And %?

Jul 21, 2012

I have a strange problem with query with like and %.

When I run this script:

ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
INSERT INTO TEST1 VALUES ('gsdk');

[code]....

I get this:

K1
ŁFa
ła
Śab <- WRONG
Śrrrb <- WRONG
4 rows selected

When i change datatype in column to varchar2 this code work correct.

The execution plan:

PLAN_TABLE_OUTPUT
SQL_ID d3d64aupz4bb5, child number 2
select * from TEST1 where k1 like N'Ł%'
Plan hash value: 4122059633
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 2 (100)
* 1 TABLE ACCESS FULL TEST1 1 82 2 (0) 00:00:01

[code]....

View 7 Replies View Related

Sysdate Showing Wrong Timezone?

May 24, 2012

I have one issue My server is in france and it is in french timezone but when I query for sysdate it returns US time.

In '/etc/sysconfig/clock/'
Zone= europe/paris
UTC= true

echo $TZ variable is returning nothing.

sysdate = us time
systimestamp= us time
current_timestamp = french time
current_date = french time
dbtimezone= europe/warsove, sessiontimezone=+2.00( which is also europe timezone offset)
tz_offset(dbtimezone)=+2.00, tz_offset(sessiontimezone)= +2.00 i.e europe
os timezone= europe/paris.

This command "./emctl config agent getTZ" is also returning timezone as europe/paris

Also in "emd.properties" file "agentTZRegion" parameter is set to europe/paris

Oracle version= 11.2.0

Now I don't understand why this sysdate and systimestamp is returning "US time zone" while everything else is returning french time zone.

View 9 Replies View Related

SQL & PL/SQL :: Export Data To CSV - Wrong Diacritics?

Jun 20, 2013

I would like to need export data to csv file, but I got problems with diacritics.The simply PLSQL looks like:

declare
f utl_file.file_type;
cursor c1 is Select ACTIVITY_SUB_TYPE
from the_table;
begin

[code]...

After run of plsql the record in the csv file looks like "Vypršanie skuš.lehoty kontakt"So there is a problem with that diacritics.

View 7 Replies View Related

Globalization :: Wrong Result For Query With Like And %?

Jun 28, 2012

I have a strange problem with query with like and %.

When I run this script:

ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- SELECT * FROM NLS_SESSION_PARAMETERS;
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));

[code]....

When i change datatype to varchar2 this code work correct.

The execution plan:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d3d64aupz4bb5, child number 2
-------------------------------------
select * from TEST1 where k1 like N'Ł%'

[code]....

Note - dynamic sampling used for this statement (level=2)

View 2 Replies View Related

Application Express :: Layout OK In 4.1 All Wrong In 4.2?

Nov 16, 2012

I have a show/hide region of type HTML with empty region source which I use to show/hide a subregion (tabular form)because I don't like the layout of a show/hide region when it is shownthis worked without a problem in APEX 4.1.1

however in 4.2 the tabular form is just a couple of pixels widehave been playing around with grids but can't seem to find the right combination of settingsthe layout is messed up

I put the app on apex.oracle.com :
workspace : xonixrs
login/password : demo/demo

View 2 Replies View Related

DBMS_JOB Running At Wrong Time

Jan 7, 2013

We have a bunch of jobs scheduled using DBMS_JOB (yes, I know I should be using DBMS_SCHEDULER, but we haven't migrated there yet). We are running Oracle 11.2.0.3 on Windows Server 2003 x64.

For example, we have a job that is supposed to run every Wednesday at 20:00. The Interval we have set up is "NEXT_ DAY (TRUNC(SYSDATE), 'WEDNESDAY')+20/24". This has been working as intended. Today (Monday), however, the job kicked off at 11:52. It was the wrong day and the wrong time.

I don't see anything weird in my alert log. Where else should I check to figure out why this job ran today?

JOB     LAST_DATE     LAST_SEC     NEXT_DATE     NEXT_SEC     INTERVAL     WHAT
293     1/7/2013 11:52:46 AM     11:52:46     1/9/2013 8:00:00 PM     20:00:00     NEXT_DAY(TRUNC(SYSDATE), 'WEDNESDAY')+20/24     ACQUISITIONS.WORKLOAD_STATUS_UPDATE_NOTIF;

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

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







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