I trying to write plsql anonymous block to return the name of all the employees who belongs to a specific department . I am writing below script for this and getting the error where as the same logic if i use only in a function instead of creating it within a procedure , i am not getting any error .
Script :
DECLARE
TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30);
FUNCTION getEmpArray (p_no IN NUMBER)
RETURN EMPARRAY
AS
[code].......
Error report:
ORA-06550: line 18, column 4:
PLS-00103: Encountered the symbol "end-of-file"
when expecting one of the following:
begin function package pragma procedure form
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
For my task I tried to write a package that has sys_refcursor in it.It compiled well.Now I want to see the results of it and wrote a anonymous block which gave errors.
I am trying to create a datablock based on a procedure , but im getting errors in compilation:
Errors are : 1) identifier 'HSM_WSH_DEL_UTIL.DEL_TBL' must be declared
2) PL/SQL ERROR 320 at line 7, column 27 the declaration of the type of this expression is incomplete or malformed
Heres my pkg spec and body for the data block:
CREATE OR REPLACE PACKAGE hsm_wsh_del_util IS TYPE del_record IS RECORD (delivery_id NUMBER); TYPE del_tbl IS TABLE OF del_record INDEX BY BINARY_INTEGER;
PROCEDURE do_query (p_del IN OUT del_tbl); END hsm_wsh_del_util; [Code] .....
We are working on migrating from 9.2.0.4 to 11.2 and we've set up a test machine so that we could test the install and the import (as well as test additional 11g features that we want to begin using).
So we created the database and created all of the tablespaces beforehand.
However, when we run the import, we get the errors like so:
Import: Release 11.2.0.1.0 - Production on Tue Oct 5 15:01:19 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V09.02.00 via conventional path
[code]....
First of all, the block size in our "newly" created tablespaces is 8192...and these are obviously trying to recreate the tablespaces with a block size of 2048.
1) Why is it not ignoring these create tablespace commands when those tablespaces already exist?
2) how in the world do we get around the block size issue? We've tried nearly everything we could find, but we've still not had any luck.
During the scheduled backup we observed that, backup got failed. Error is "ORA-01115: IO error reading block from file 11 (block # 118736) ". unable to rectify it. Below are the part of log file -
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.MAIN [TABLE_DATA:"DW_REP"."DW_MOTOR_CLAIM_INFO_DTLS"] ORA-24795: Illegal ROLLBACK attempt made ORA-06512: at "SYS.KUPW$WORKER", line 8976 ORA-24795: Illegal ROLLBACK attempt made
[code]...
This is development server. Dont have RMAN configured.But when we connected to RMAN and issued block recovery, it started process and shows media recovery done. Since no RMAN backup was taken before, what it does here?
C:UsersAdministrator>RMAN Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 17 10:48:40 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> CONECT target / connected to target database: MAGMADEV (DBID=2469200049)
I need to take the distinct values from VARRAY.. I have wrote following simple example. But it does not work. how to get the distinct value from VARRAY.
declare type t is varray(10) of varchar2(10); t1 t; type r is table of varchar2(10) index by binary_integer; r1 r; begin t1 := t('A','B','A','B','A','B','C'); select distinct * into r1 from table(select * from t1); END;
type t is varray(50) of number; t1 t; cursor c1(t3 t) is select name from temp2 where id in(t3(1),t3(2)); name1 varchar2(20); begin t1 :=t(501,384,3242,106); [code]......
my question varray size is 50, while creating the cursor i have to pass the 50 values like t3(1),t3(2).....t3(50). I have tried to use t3.first..t3.last in IN condition but throwing some error, any other method/hints to pass the values dynamically to cursor IN condition.
i have a doubt in plsql table and varray.. is this two things data will write on disc or it will perform through ly a memory level? if its in memory level i need a document about that.
BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for HPUX: Version 11.2.0.3.0 - Production
[code]...
SELECT job_request_id, CAST (COLLECT (USER_ID) AS SYS.OdcinumberList) user_ids FROM mytable GROUP BY job_request_id;
ORA-22814: attribute or element value is larger than specified in type
I'm trying to optimize an application running heavy updates/inserts/deletes, by having it using bind variables instead of "string queries".
The columns to be updated can vary (possibly from one column to all columns of a table), thus I have made som logic to build the query accordingly. My problem is now that I cant get the EXECUTE below to handle the VARRAY passed in the USING clause, it fails with "PLS-00457: expressions have to be of SQL types"
Environment:
create table table_x (a varchar2(10),b varchar2(10),c varchar2(10),d varchar2(10));
insert into table_x values('a','b','c','d'); commit;
Code, simplified with static number of columns:
declare type v is varray(10) of varchar2(20); v_values v:=v('A','B','c'); myupdate varchar2(2000); begin myupdate:='update table_x set a=:a, b=:b where c=:c'; dbms_output.put_line(myupdate); execute immediate myupdate using v_values; end;
I've understood that I cannot send TABLE type variables by USING, but this should be an VARRAY.
PLS-00386: type mismatch found at 'RECORD_VARRAY' between FETCH cursor and INTO variables
while executing the below code.
PROCEDURE MAIN_BULK_COLLECT(P_STARTDATE IN TIMESTAMP DEFAULT NULL, P_ENDDATE IN TIMESTAMP DEFAULT NULL, P_ROW_COUNT IN NUMBER DEFAULT 1000, O_RECORD_VARRAY OUT NOCOPY SSAM_VARRAY_TYPE, P_ERROR OUT VARCHAR2) AS [code]....
I'm able to run the program successfully using FOR LOOP instead of BULK COLLECT but wish to run using bulk collect.
I am trying to build an array that contains known column names, 63 columns in all.The idea is to search backwards from 63 down to say 10 to find the first non-blank column.
Then using a loop with the known number of lines print from 1 to last non-blank column.The problem is that it only shows the column names and not the values of the columns.So I am getting 63 lines every time, instead of 20, 30, 40 or however how many fields actually have something.
Here is my declare to_num number; field_name varchar2(15); [code]....
ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [89], [83], [], [], [], [], []
Is it Possible doing SQL LOAD into Varray table having two inner objects.
find the structure of the table and its types for your reference
CREATE OR REPLACE TYPE OB_TEST_INFO AS OBJECT ( AGE NUMBER ( 3 ), NAME VARCHAR2 ( 14 ) ); /
CREATE OR REPLACE TYPE OB_TEST_INFO_VARRAY AS VARRAY( 400 ) OF OB_TEST_INFO ; /
CREATE OR REPLACE TYPE OB_TEST_MAINTENANCE AS OBJECT ( BREAKOUT_TYPE NUMBER ( 1 ), EXISTING_STRIPS_FLAG NUMBER ( 1 ), OB_TEST_INFO OB_TEST_INFO_VARRAY ); /
I am using arrays on this procedure but would like to use a WHILE LOOP to fill the array. The first element uses a balance forward amount unlike the next 11 elements so I would think I have no other choice for the first element.
jde_account_balances is the table name that stores the amounts.
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].......
I want to move the data in the VARRAY column BT_DETAIL to another table. I have create a staging table BT_STG which contains a surrogate key column and the columns from the VARRAY. I am creating this staging table at run time.
I'm having a problem with the When-timer-Expired trigger.I am creating a timer on the On-Error trigger of the block. in the when-timer-expired :
message (11); exit_form(no_validate);
If i run the program program for the first time,message 11 doesn't show thus it doesn't fire the when-timer-expired trigger. But if i logout from application server and enter all over again,the when-timer-expired works !
i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example
Cheque amount in master block = 291
Invoice wise receiveable amount is as follows
Invoice No , receivable amount , Received amount
10, 196 , 0 20 , 95 , 0 30 , 54 , 0
Result should be as follows:
Invoice No , receivable amount , Received amount
10, 196, 196 20 , 95 , 95 30 , 54, 0
Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.
