SQL & PL/SQL :: Can Pass Nested Table To Procedure
Nov 12, 2011
declare
cursor c is
select employee_id from employees;
type nst_type is table of employees.employee_id%type;
emp nst_type;
begin
open C;
loop
exit when C%notfound;
fetch c bulk collect into emp;
end loop;
close c;
end;
Above is the sample code, and now i want to pass 'emp' as an input parameter to the procedure.
How can I do that, as emp is of nst_type type and I do not know how my procedure will recognize the datatype...
View 4 Replies
ADVERTISEMENT
May 10, 2013
I have a doubt how to pass input parameter for nested table which is declared as input parameter in procedure.
CREATE TYPE t_example AS OBJECT(msg_text VARCHAR2(100), bundle_msg_text VARCHAR2(100), version NUMBER(10))
/
create type t_msg_details ia table of t_example
/CREATE TABLE table_nested_sample
(msg_codes NUMBER(10),
language_id NUMBER(10),
[Code]...
How to call this procedure I want to insert data like this
Msg_codes Language_id Language_name msg_text_details
101 1 ENG "msg_test" "Bundle_msg_text" 1
"msg_test_1" "Bundle_msg_text_2" 2
View 5 Replies
View Related
Jun 2, 2010
I am trying to pass a PL/SQL table as a parameter to a procedure and then using that table, update the records, but I am getting an error like:
ORA-06550: line 30, column 10:
PLS-00306: wrong number or types of arguments in call to 'UPDATE_STATUS'
Find the code below:
CREATE TABLE test_pl(empno VARCHAR2(20), empname VARCHAR2(40), empsts VARCHAR2(10));
INSERT INTO test_pl
VALUES ('0001', 'A', 'Y');
INSERT INTO test_pl
VALUES ('0002', 'B', 'N');
INSERT INTO test_pl
VALUES ('0003', 'C', 'Y');
INSERT INTO test_pl
VALUES ('0004', 'D', 'Y');
[code]....
View 4 Replies
View Related
Sep 1, 2010
I want to pass a table as a parameter to a procedure.
As an example:
TYPE my_tab IS TABLE OF my_rec INDEX BY BINARY_INTEGER;
However, I want to give this parameter a default of null... is this possible?
procedure myproc(p_param1 in varchar2, p_tab in my_tab default null)
View 11 Replies
View Related
Jul 18, 2012
Does it possible to pass object (or) table as an argument to a remote procedure?
View 2 Replies
View Related
Jul 31, 2012
I'm trying to fetch data from Ref Cursor OUT parameter filling by stored procedure. Using latest version of ODP.NET provider (11.2.0.3.0).
My stored procedure:
TYPE cursor_type IS REF CURSOR;
PROCEDURE test_proc (p_recordset out cursor_type) AS
BEGIN
OPEN p_recordset FOR
SELECT 1,2, CURSOR (SELECT 3,4 FROM dual)
FROM dual
END;
END test_proc;
If i'm removing "*CURSOR (SELECT 3,4 FROM dual)*" from the procedure, all works just fine. But when the nested cursor exists in procedure, i'm getting the following exception:
System.NullReferenceException occurred
Message=Object reference not set to an instance of an object.
Source=Oracle.DataAccess
StackTrace:
at Oracle.DataAccess.Client.OracleDataReader.GetOraDbTypeEx(Int32 i)
[code]........
My c# code:
using (OracleConnection conn = new OracleConnection(connString))
*{*
OracleCommand command = new OracleCommand();
command.Connection = conn;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "test_proc";
[code].......
If i'm changing procedure signature from out parameter, to return value, code throws the same exception.
View 4 Replies
View Related
Jun 13, 2013
Can we pass REF_CURSOR to external procedure by DB_LINK..
View 2 Replies
View Related
Oct 3, 2010
I am passing a string from vb.net through oracle store procedure. String consists of varchar values for eg '123456;I|N,2424424;O|A'. But how can i call these string values into array using oracle stored procedure? After getting these values into array , i wanted to loop through these values.
View 1 Replies
View Related
Feb 26, 2012
I am trying to pass many characters to the in mode parameter using procedure , but i am getting the below error.
ORA-06550:
PLS-00172: string literal too long
CREATE TABLE USR_DETAILS
(
LOAD_ID NUMBER NOT NULL,
LOAD_DATE DATE,
USER_VALUE VARCHAR2(4000 BYTE),
USERID VARCHAR2(4000 BYTE)
)
insert into user_details values('1','2/10/2011','PROD1','USER1');
insert into user_details values('2','2/10/2011','PROD2','USER2');
[code]......
I have written the code as below...
Procedure concept :
Here i will seperate the strings(input parameters) using comma(,) symbol . Here users may pass more than 5000 characters to single in mode parameter, so i have tried with clob datatype but no luck.
CREATE OR REPLACE procedure user_details_proc (user_value1 IN varchar2,
user_value2 IN varchar2,
user_value3 IN varchar2,
user_value4 IN varchar2,
user_value5 IN varchar2,
userid IN varchar2
) as
[code].......
View 2 Replies
View Related
May 30, 2012
I have one procedure , which calls the DBMS_scheduler.run_job , which calls the Shell Script. now based on the shell execution i need to return the message lines to procedure.
View 1 Replies
View Related
Mar 29, 2013
I am writing a procedure for the front-end. The end-users need to insert multiple rows of data into history tables in the database (11G). My problem is: the multiple actually parameters is not a fix amount, this time, the amount could be 5, next time, it could be 12. I currently used one string and pass the actual parameter (P_id, number) as '2, 4, 5, 7, 8', the procedure was executed successfully, but cannot insert any data into history table.
See my procedure below (the base table has clob data, I have to consider insert ... select *), I tried to use to_number (CONTACT_MSG_ID), it doesn't work well:
PROCEDURE ARCHIVE_XREF_CONT_EMAIL(P_ID IN VARCHAR2) IS
BEGIN
INSERT INTO TRC_XREF_CONT_EMAIL_MSGS_HIST
SELECT *
FROM TRC_XREF_CONT_EMAIL_MSGS
[code].......
View 10 Replies
View Related
Aug 10, 2010
'Can we pass a dynamic collection variable to the procedure that is called from dbms_job.submit'
I have a package my_package with:
1. record type: ocv_rec
2. collection type: varr_ocv_rec varray(100) of ocv_rec
3. record type: ext_id_rec
4. collection type: varr_ext_id_rec table of ext_id_rec index by pls_integer
5. procedure: analyze_error_152_ll
6. procedure: get_ext_ids_152(
v_db_ssa_dtl in varr_ocv_rec,
ext_id_type in varchar2,
ext_ids out varr_ext_id_rec)
Now here is where the issue resides:Within the definition of analyze_error_152_ll (in body of my_package), I call get_ext_ids_152 using dbms_job.submit
procedure analyze_error_152_ll
is
vJob varchar2(400);
vJobNum binary_integer;
v_db_ssa_dtl2 varr_ocv_rec := varr_ocv_rec();
ext_ids varr_ext_id_rec;
ext_id_type varchar2(5) := '(1)';
/*OTHER DECLARATONS*/
[code]......
View 7 Replies
View Related
Apr 9, 2012
I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.
For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".
DECLARE
bef_val CUST%ROWTYPE;
aft_val CUST_TEMP%ROWTYPE;
BEGIN
SELECT * INTO bef_val FROM CUST WHERE name = 'ABC';
SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC';
[code]....
For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?
View 8 Replies
View Related
Oct 19, 2011
I have the following error when I try to pass a Procedure parameter carrying db_link name to a stored procedure:
SQL> CREATE OR REPLACE PROCEDURE AFESD.P_DM_VCONTRACT_ITEM (CON_CONNECTION VARCHAR2)
2 IS
3 T_CONNECTION VARCHAR2(50);
4 T_SQL VARCHAR2(500);
5 BEGIN
6 T_CONNECTION := 'X_DM_TEST@' || CON_CONNECTION;
[code]....
The insert statement succeeds when I try to run DBMS_OUTPUT.put_line output.
View 4 Replies
View Related
May 9, 2012
I am fairly new to oracle, here's what I am doing.
Create or replace type csc_info as object( source_code varchar2(10),
Docno varchar2(10),
Key_value_1 varchar2(10),
Key_value_2 varchar2(10));
[Code]....
I need to test the procedure how do I pass the inputs for the type object csc_info?
View 2 Replies
View Related
Mar 28, 2012
I have created a stored procedure that checks if a file exists and gets a date from the file if it exists. The date is then used as a paramter. See below:
CODEcreate or replace
PROCEDURE "P_Load_Stamp" AS
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;
[code]...
The above codes works perfectly and I scheduled it using SQLPLUS as follows:
CODEvariable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
[code]...
My problem is that I need to pass the date from the above procedure as a parameter to another stored procedure. So I modified my code as follows (the parts in red):
CODEcreate or replace
PROCEDURE "P_Load_Stamp" (vCTIDATE OUT varchar2) AS
v_exists BOOLEAN;
v_length NUMBER;
[code]...
Now it doesn't strike me as a rights issue since I created it in the schem schema. What could I be doing wrong here?
View 1 Replies
View Related
May 11, 2012
I have a record type and table of records as follows
type rec is record
(
empid employee.empid%type,
ename employee.ename%type
);
type tab_rec is table of rec;
Suppose data from employee table is fetched into this collection
Can you pls clarify if we can refer to all the rows of empid in the above collection ?
something like tab_rec.empid without using the subscript for referring to the nth row
My requirement isto pass this list as input parameters to a procedure(PL/SQL).
View 3 Replies
View Related
Oct 26, 2010
i have a stored procedure whose input parameter is a varchar2 datatype.i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.my problem is when there is a input string with & (ambersand) then its not working.
even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.look at the sample code below which i wrote for testing purpose:
procedure is:
create or replace procedure testproc(p_in in varchar2)
is
begin
null;
end;
i pass parameter as given below:
begin
testproc('abc & def');
end;
if i run above script, it asks me to input some string value as it sees & in the string. attached is the image that shows up in TOAD. if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.
begin
testproc('abc &'||'def');
end;
View 9 Replies
View Related
Sep 7, 2012
I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
View 1 Replies
View Related
Jan 9, 2013
a material related to collections with Examples and also how to declare table type values.
View 9 Replies
View Related
Aug 29, 2012
Is Oracle will support Multi value storage ? In what way we can use Nested table? In real time application where we can use nested table . What is the usage of nested table in real time application.
View 2 Replies
View Related
May 17, 2011
what is the advantage of Nested table ?
View 1 Replies
View Related
Sep 10, 2013
I am studing Multidimensional Nested table and have the below code:
DECLARE TYPE table_type1 IS TABLE OF INTEGER; TYPE table_type2 IS TABLE OF TABLE_TYPE1;
table_tab1 table_type1 := table_type1();
table_tab2 table_type2 := table_type2(table_tab1);
BEGIN FOR i IN 1 .. 2 LOOP table_tab2.extend;
table_tab2(i) := table_type1();
[Code]...
exception when others then dbms_output.put_line(sqlerrm);END; This code is working fine as of now.But,If i comment below code(table_tab2 is also extended latter):
table_tab2.extend;
table_tab2(i) := table_type1();
then it gives me error 'Subscription Beyond count'. I would like to know why i need to extend table_tab2 twice?!
View 7 Replies
View Related
Jul 11, 2011
I want to truncate table partition but I'm getting error:
CODEORA-02266: unique/primary keys in table referenced by enabled foreign keys
because of table has a nested table. Currently this column is not in use so I could drop it, but I want to avoid it (table is huge). Is there another posibility to do truncate partitions in this table? ALTER TABLE ... SET UNUSED doesn't resolve the problem.
View 2 Replies
View Related
Dec 2, 2010
I try to do this:
CREATE MATERIALIZED VIEW MV_NESTED_DATA
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING NO INDEX
REFRESH COMPLETE
ON DEMAND
START WITH ROUND(SYSDATE)
NEXT ROUND(SYSDATE) + 1
WITH ROWID
AS
select NESTED_TABLE_FIELD from MY_TABLE@Y_DB_LINK;
where NESTED_TABLE_FIELD is a nested table stored as T_NESTED_TABLE
And I get the error: ORA-12014: table 'T_NESTED_TABLE' does not contain a primary key constraint
Why should it if I try to create a MV with "WITH ROWID" refresh option and not "WITH PRIMARY KEY" one?
View 4 Replies
View Related
Feb 18, 2011
I have created table with nested table:
CODECREATE OR REPLACE TYPE ADDR_T AS OBJECT (
ADDR1 VARCHAR2 (50),
ADDR2 VARCHAR2 (50)
);
CREATE OR REPLACE TYPE t_ADDr AS TABLE OF ADDR_T;
[code]....
I have added some records and created index on ID column. I want to get result of CODEselect id, p.addr1,p.addr2 from nested_table n,table(n.COL1) p where id=1
Explain plan for that is:
CODESELECT STATEMENT ALL_ROWSCost: 8 Bytes: 231 Cardinality: 3
4 HASH JOIN Cost: 8 Bytes: 231 Cardinality: 3
2 TABLE ACCESS BY INDEX ROWID TABLE SYS.NESTED_TABLE Cost: 2 Bytes: 13 Cardinality: 1
1 INDEX RANGE SCAN INDEX SYS.FDSFAS Cost: 1 Cardinality: 1
3 TABLE ACCESS FULL TABLE (NESTED) SYS.COL1_TAB Cost: 5 Bytes: 163,840 Cardinality: 2,560
How to avoid full table scan on nested table? Cardinality is sum of all records in nested column in all rows in main table, why?
View 2 Replies
View Related
Sep 11, 2012
I'm trying to sort a collection in a nested table in PL/SQL so these value can be used later for a display and for export to Excel. The sort is failing.
-- PLS-00642: local collection not allowed in SQL statements
-- PL/SQl: ORA-00902: invalid datatype
The error message are also noted below in the code on the line that fails.
A quick overview of this code- Using a nested table MyNestedTable the values from several select queries are combined into MyRecordsetZero using CURSOR, and MULTISET UNION. I'm trying to either sort MyRecordsetZero or populate MyRecordsetSorted with the sorted values for futher use.
IMPORTANT: The code is running in an enviroment that does not have permission to create.
Versions:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Developer: 8.0.0.1480
DECLARE
-- Declare the variable MyTID to be used by select statements and
-- set its data type and max character count
MyTID varchar2(10);
[code]....
View 13 Replies
View Related
Nov 10, 2011
In test.pks file I declared the following type
TYPE tab_tests is table of NUMBER(15);
In test.pkb, I have the following procedure
PROCEDURE report (
i_cid IN NUMBER
)
IS
test1 tab_tests := tab_tests();
test2 tab_tests;
BEGIN
-- I populate test1 with the data and it works fine. But when I tried to make a sorted nested table with the following command.
I got this error 'ORA-00902: invalid datatype',
a nested table than the following, like a built in Oracle collection method?
execute immediate 'select cast( multiset (select * from table(test1) order by 1) as tab_tests) INTO test2 FROM dual';
END;
View 16 Replies
View Related
Mar 12, 2012
create type nesttype as table of clob;
create table emp
(empno number,
ename varchar2(1000),
language_known nesttype
)
I want to check whether language is already there in database or not.
i have written the below query
select * from emp where language_known =nesttype('english','hindi');
i am getting the below error
SQL Error: ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type
22901. 00000 - "cannot compare nested table or VARRAY or LOB attributes of an object type"
*Cause: Comparison of nested table or VARRAY or LOB attributes of an
object type was attempted in the absence of a MAP or ORDER
method.
*Action: define a MAP or ORDER method for the object type.
How to compare data in nested table
View 16 Replies
View Related
Mar 4, 2013
need to use Extend() in nested tables in Oracle? What could be the problem if I do not use this method in my code?
I have a nested collection type (TABLE OF VARCHAR2(32)) declared in my package. My stored procedure takes the TABLE type as input and inserts that data into a database table.
I see that my code works fine without using EXTEND method.
View 10 Replies
View Related