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

PL/SQL :: How To Pass Input Parameter For Nested Table

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

SQL & PL/SQL :: How To Pass Table As Parameter To Procedure

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

SQL & PL/SQL :: Pass Table As Parameter To Procedure?

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

SQL & PL/SQL :: Pass Object (or) Table As Argument To Remote Procedure?

Jul 18, 2012

Does it possible to pass object (or) table as an argument to a remote procedure?

View 2 Replies View Related

ODP.NET :: Nested Cursor From Stored Procedure

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

SQL & PL/SQL :: Can We Pass REF_CURSOR To Externel Procedure

Jun 13, 2013

Can we pass REF_CURSOR to external procedure by DB_LINK..

View 2 Replies View Related

Pass Array To Oracle Stored Procedure?

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

SQL & PL/SQL :: Pass More Characters To In Mode Parameter Using Procedure

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

SQL & PL/SQL :: How To Pass Shell Script Values To Procedure

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

PL/SQL :: How To Pass Changeable Actual Parameters Into Procedure

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

DBMS_JOB.SUBMIT Can Pass Dynamic Argument Procedure

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

SQL & PL/SQL :: How To Pass In Dynamic Variable For Calling Store Procedure

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

SQL & PL/SQL :: Error With Execute Immediate To Pass Db_link To Stored Procedure?

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

SQL & PL/SQL :: How To Pass Type Object As A Parameter To Stored Procedure

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

DBMS_JOB - Pass Date As Parameter To Another Stored Procedure?

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

SQL & PL/SQL :: Collections Fetch - Pass List As Input Parameters To Procedure

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

Client Tools :: How To Pass Ambersand (&) As Input Parameter To A Stored Procedure

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

Truncate / Drop Partitions In Table Having Nested Table Columns

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

SQL & PL/SQL :: Using Nested Table

Jan 9, 2013

a material related to collections with Examples and also how to declare table type values.

View 9 Replies View Related

SQL & PL/SQL :: Nested Table Is Same As Multi Value Table In JBASE

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

SQL & PL/SQL :: What Is The Advantage Of Nested Table

May 17, 2011

what is the advantage of Nested table ?

View 1 Replies View Related

PL/SQL :: Multidimensional Nested Table

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

Remove Constraints On Nested Table?

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

Materialized View On Nested Table?

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

Tuning Select With Nested Table?

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

SQL & PL/SQL :: Sorting Collection In Nested Table

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

SQL & PL/SQL :: Sorting Nested Table / Collection

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

SQL & PL/SQL :: How To Compare Data In Nested Table

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

SQL & PL/SQL :: Using EXTEND In Nested Table Types?

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







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