SQL & PL/SQL :: Pass Table As Input Parameter In Stored Procedures?

Feb 22, 2011

i need to pass table as input parameter in stored procedures. during the run time, i am getting error

CREATE OR replace TYPE emp_type IS OBJECT (
id NUMBER(4),
ename VARCHAR2(10));
CREATE OR replace TYPE emp_type_tab
IS TABLE OF EMP_TYPE;
CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB)
AS
BEGIN
FOR i IN 1.. in_emp_type.COUNT LOOP
dbms_output.Put_line(in_emp_type.Id(i));
END LOOP;
END;
/

View 10 Replies


ADVERTISEMENT

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

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 :: Pass Multiple Values As Single Input Parameter Into Pipeline Function

Dec 23, 2012

My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":

with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)

View 2 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 :: Removal Of Time In Input Parameter In Stored Procedure?

May 15, 2011

I'm creating a stored procedure wherein I'm the field wherein I will use for date input parameter is having a datetime format.

The format that we need for user's to input is 'DD-MON-YYYY' (without the minutes and seconds.)

how should I proceed with the creation of stored procedure for this?

View 2 Replies View Related

Input Parameter From Arraylist To Stored Procedure In Oracle?

Nov 7, 2013

i have created a package & stored procedure which is working fine when i am passing single value to my package or stored procedure. But what approach i should take if i have to pass multiple values (ArrayList) like (from eg empid like I1001,I1002,I1003,I1004,...) in my input parameter . I am using C# & Oracle

CREATE OR REPLACE PACKAGE PKG_x AS type t_cursor is ref cursor;
procedure Proc(cur_x out t_cursor,
param_emp in varchar2 DEFAULT NULL);
END PACKAGE PKG_x;

[Code].....

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

PL/SQL :: How To Pass Value From A Table Into Parameter Of A Function

Nov 8, 2012

I have written as function :

create FUNCTION Cov(p_str1_x IN VARCHAR2,p_str2_x IN VARCHAR2, p_str1_y IN VARCHAR2,p_str2_y IN VARCHAR2) RETURN NUMBER is
TYPE ty_varchar_assoc IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
v_x ty_varchar_assoc;
v_y ty_varchar_assoc;
v_inter ty_varchar_assoc;

[Code]....

Now i need to pass the values to the parameter from a table? How can i do that?

View 2 Replies View Related

PL/SQL :: How To Pass Table Of Record As Parameter To Server Page

Apr 20, 2013

Is there any way to pass table of record as parameter to the plsql server page? For instance, I can define a new type in package specification:

create or replace package tst_type
as
type tab_expr_sec is table of varchar2(50) index by pls_integer;

type rec_expr is record (
prim_expr varchar2(50),
expr tab_expr_sec
);
[code]....

View 5 Replies View Related

PL/SQL :: Input Parameter Of Table Type And Null Assigned To It

Jul 17, 2012

I have few questions regarding PL/SQL tables

1) If I don't specify the "INDEX BY" clause, it is indexed by PLS_INTEGER by default, right?

2) Consider this package specification

CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);


END Testxyz;So I created a table (I hope it is defaultly indexed by pls_integer data type) and I am passing it as a parameter to a procedure. Because I want this parameter to be optional I am assigning null into it.

Now I change the definition of the table to:

CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
[code]....

3) Because I need this parameter to be optional, I use the first declaration:

CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
END Testxyz;

Now I create an anonymous block and want to assign something into the table

DECLARE
   vtab_Numbers TESTXYZ.tab_Numbers;
BEGIN
vtab_Numbers(1) := 5;
END;
/When trying to run this, I got:
06531. 00000 - "Reference to uninitialized collection"

Is it possible to have input parameter of PL/SQL table type and have defaultly null assigned to it?

View 5 Replies View Related

SQL & PL/SQL :: Column Access In Table Based On User Parameter Input

Nov 10, 2010

Need to access data in a table base on user parameter input where the data is stored like a spreadsheet with column headings JAN, FEB, MAR... and the rows are the years. Is there a way to create a generic SQL statement so that I don't have to have 12 if statements in the procedure?

View 5 Replies View Related

SQL & PL/SQL :: Execute Stored Procedure With Table Of Number As In Parameter?

May 31, 2011

I have one stored proc with three in parameters as

number, varchar and table of number

what statement I need to write in pl/sql to execute it ...

execute getdetails(1,'xyz', ????????????)

View 5 Replies View Related

Stored Procedure For Searching Data From A Table By Passing Tablename As A Parameter

Feb 6, 2012

This procedure is not working properly.

create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;

the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated

ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1

View 1 Replies View Related

SQL & PL/SQL :: IF ELSE ENDIF On Stored Procedures?

Apr 8, 2011

I come from a Microsoft SQL background and am having trouble adjusting to the PL/SQL syntax. I'd like to build this up to pass back a certain value back into a web form.

IF (SELECT COUNT(*) FROM EXCURSION WHERE EXCURSION_ID = 75)= 1 THEN
DBMS_OUTPUT.PUT_LINE 'True';
ELSE
DBMS_OUTPUT.PUT_LINE 'False';
END IF;

I'm having trouble writing values out at the moment

View 7 Replies View Related

Where Server Side Procedures Are Stored

Jun 2, 2009

Where are the server side procedures are stored? Where are the server side procedures are visible??

View 5 Replies View Related

Stored Procedures Using Oracle Tables

Jul 22, 2010

Let us say there is an oracle table A. How do i find out what stored procedures/packages/views are using this table A.

Reason to know: When some DDL changes happen on table A, how do i know what are the impacted sp/pkg/views which should also be modified. Is there any query with which i can find this?

View 5 Replies View Related

PL/SQL :: Trapping Errors In Stored Procedures?

Feb 27, 2013

I have to run queries on a gov't computer. There are errors in some of the stored procedures that will NEVER go away. Fact of life, and I have to live with it (I'm just a data analyst--not the developer of the procedures)So I do this, in the hope of trapping the error

BEGIN
run_stored_proc (my_cursor, my_id, param3);
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
END;Well, it can still crash.
SQL> @c:mysql
un_test
ERROR IN ID (2692). The error was -20100 -ERROR- ORA-20100: Exception occurred in some_other_stored_procedure

ORA-01422: exact fetch returns more than requested number of rowsSo it looks like run_stored_proc calls some_other_stored_procedure which is having issues. I was hoping that by trying to capture the error in the top level procedure, it would also capture the error in a lower-level "sub-procedure". This is the way a TRY-CATCH in java works. If an error happens anywhere along the way inside the TRY-CATCH, it is caught. Sure, PL/SQL ins't java, but is there a way for me to catch a lower level error and not have this code crash?

Fortunately for me, it actually doesn't crash that often. But I have to put this code in a loop, and run it for many different my_id's. And when it does crash, it isn't significant from a data analysis standpoint. I can't do without a small percentage of the data. But I'd love to be able to just run my script and not have to watch it for unexpected crashes.

View 11 Replies View Related

PL/SQL :: Java Stored Procedures And Triggers

Oct 31, 2012

I want to to use Java stored procedures and triggers within the Oracle 11g XE ?

View 3 Replies View Related

SQL & PL/SQL :: How To Pass Output Of Regexp_substr As Number Input To A Cursor

Sep 13, 2012

I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.

Here is an example.

initial input : '5545|4124|12456'
using the sql below to break the string into columns.
select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual
connect by regexp_substr(, '[^|]+', 1, level) is not null;

But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.

View 12 Replies View Related

PL/SQL :: How To Pass Object Type As Input For Search Criteria

Mar 26, 2013

I have search function that takes the input parameters and returns all the matching rows. this is straight forward only. My problem is having multiple types as a input parameter. that is the reason i am not able to pass the input value for these types.

My Input Type table looks like this.

CREATE OR REPLACE TYPE T_T_PARTY_REQUEST_CRITERIA
AS TABLE OF T_O_PARTY_REQUEST_CRITERIA;
/
CREATE OR REPLACE TYPE T_O_PARTY_REQUEST_CRITERIA
AS OBJECT
(
SYSTEM_IDENTIFER VARCHAR2(50),
PROCESS_TYPE VARCHAR2(50),
[code]........

Last two inputs are type again.my question here is how to pass the values for these two T_T_PARTY_KEY_IDENTIFIER and T_T_ADDTN_IDENTIFIER_INFO. I have defined the the last two types following.

CREATE OR REPLACE TYPE T_T_PARTY_KEY_IDENTIFIER
AS TABLE OF T_O_PARTY_KEY_IDENTIFIER;
/
CREATE OR REPLACE TYPE T_T_ADDTN_IDENTIFIER_INFO
AS TABLE OF T_O_ADDTN_IDENTIFIER_INFO;
/
[code].......

I have pasted the query here from my function. when i pass the null as part of input for these 2 types my query is working. otherwise it is saying invalid Identifier.First I tried with first Type.

I am passing the value as

(PRKYTP.PRTY_KEY_TYP_NM = ITTPRC.PARTY_KEY_IDENTIFIER.PARTY_KEY_TYP_NM OR ITTPRC.PARTY_KEY_IDENTIFIER.PARTY_KEY_TYP_NM = 'ALL' OR ITTPRC.PARTY_KEY_IDENTIFIER.PARTY_KEY_TYP_NM IS NULL);

Error is Error(34,147): PL/SQL: ORA-00904: "ITTPRC"."PARTY_KEY_IDENTIFIER"."PARTY_KEY_TYP_NM": invalid identifier

SELECT DISTINCT T_O_PARTY_REQUEST_IDENTIFIER(PR.PRTY_RQST_ID) BULK COLLECT INTO T_T_P_R_CRITERIA
FROM TABLE(CAST(I_T_T_PARTY_REQUEST_CRITERIA AS T_T_PARTY_REQUEST_CRITERIA)) ITTPRC,
PRTY_RQST PR
JOIN BUSN_APPLC BIAP ON BIAP.BUSN_APPLC_ID = PR.BUSN_APPLC_ID
[code]........

View 4 Replies View Related

SQL & PL/SQL :: Why Do Stored Procedures And Functions Improve Performance

Feb 10, 2012

Why do stored procedures and functions improve performance?

A. They reduce network round trips.
B. They reduce the number of calls to the database and decrease network traffic by bundling commands.
C. They reduce the number of calls to the database and decrease network traffic by using the local PL/SQL engine.
D. They allow the application to perform high-speed processing locally.
E. They postpone PL/SQL parsing until run time.

I think the answer should be A and B but i came across answers as B and E Can u explain me what is the difference between option A and B and does it postpone parsing till run time?

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

SQL & PL/SQL :: Bind Variables / Developing Stored Procedures In Developer

May 16, 2010

Am using Oracle 9i and developing stored procedures in SQL Developer. Have a situation where one stored procedure A is calling another B. Stored proc B builds the SQL based on parameters passed in. For one of the parameters i would like to use a bind variable in the SQL that proc B builds. The bind var is passed back to proc A as a part of the SQL string. In proc A, i then try to bind that variable to a parameter(value), however, the bind does not seeem to work.

The SQL string contained in v_SQLStatement defined as VARCHAR(4000) that is passed back to proc A looks like:

SELECT em.event_title AS event_name,
e.start_date AS start_date,
e.end_date AS end_date
FROM d_event_ml em
inner join d_event e
ON em.event_id = e.event_id
WHERE em.language_id = 46
AND e.end_date >= SYSDATE
AND e.stream_id IN ( :v_x1 )
AND e.event_id IN (SELECT event_id
FROM d_events_seas
[code]....

and o_EventList is defined as REF CURSOR. i'm experiencing is that :v_x1 stays as :v_x1 and does not change.This is my first attempt at using bind vars. URL....

View 7 Replies View Related

Client Tools :: How To Convert SQL Server Stored Procedures

Sep 3, 2013

In SQL server 30 stored procedures are there how to convert all the stored procedure from SQL server to Oracle 11g..

View 5 Replies View Related

SQL & PL/SQL :: How To Call Stored Procedures Or Functions From A Database Trigger

Oct 14, 2011

How to call stored procedures or functions from a database trigger?

View 1 Replies View Related

Advantages / Differences Of Methods Defined Types Over Stored Procedures

Aug 31, 2011

We are trying to use the methods/constructors in the object types and find it more similar to the procedures and functions in the packages. I am wondering how they are different from stored procs and functions and what are the advantages?

View 2 Replies View Related

.NET Stored Procedures :: How To Send Execute String To Oracle Database

Oct 31, 2013

I would like to send a raw command to the Oracle server. from .NET (System.Data.OracleClien), but I got invalid statement (ORA-00900) exception

.Excecute string:string SQL = "EXECUTE SP_THREADFILE_INSERT('" + Guid.NewGuid() + "','" + filename + "',utl_raw.cast_to_raw('" + content + "'))";

View 0 Replies View Related







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