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;
/
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.
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
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)
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?
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;
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?
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?
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?
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
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
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?
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.
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.
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]........
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?
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....
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?
