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 Replieshow to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...
View 7 Replies View RelatedI 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.
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?
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 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;
Here is a problem. I have to pass an array e.g ('Account','Marketing',Computer' ) from java to Oracle 9i.
Would I be able to do in Oracle, if yes then how ?
For Nested table i have done it in follwing way? This is pl/sql stored procedure.
CREATE or REPLACE PROCEDURE TEST(
activationStartDate IN DATE,
activationEndDate IN DATE,
[Code]....
My JavaCode is
--First getConnection
--prepare sql string : sql = "{ Call Test(?,?,?,?,?) } ";
--Use prepareCall function on Connection object and passed this sql string and retrived CallableStatement class object.
[Code].....
Then what if i want to select more than one column from EMPLOYEE and pass to my javaCode......How my Pl/Sql will look like?........ I know how to retrieve all the coloumns of resultset in java.
I want to ask how pl/sql code will look like, i know the retrieveal process in java.
List<List> result = new ArrayList()<List>;
List col = new ArrayList();
ResultSet rs = (ResultSet)stmt.getObject(5);
ResultSetMetaData rsMetaData = rs.getMetaData();
[Code].....
I've one package in which one record is created. associative array is craeted on that record.create procedure on associative array.using forms 6i i want to call this procedue.(package_name.procedure_name(paramerters)). but my problem is what paramerter shuld i provide to excute the procedure?like PK_EXCEL_TO_DB.PR_DO_INSERT(LIST_ROUTE); but i am getting error while doing this.
CREATE OR REPLACE PACKAGE PK_EXCEL_TO_DB IS
TYPE ROUTE IS RECORD (COL_ROUTE VARCHAR2(255), VAL_ROUTE VARCHAR2(4000));
TYPE LIST_ROUTE IS TABLE OF ROUTE;
PROCEDURE PR_DO_INSERT(i_lData IN LIST_ROUTE);
[code]...
I have to write a PL/SQL procedure, which is supposed to take an array of strings as input. This array will have simple strings as elements, like
'000887S','000780S'.
Now I have a query in the procedure, which will return a row, for each of the array elements. For example:
SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
[code]......
In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.
Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method too. Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.
what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set. Like we can use arrays, table type data,ref cursors, etc.
I created a table and some type definitions in oracle 10.2.0.3 DB as follows. There is also a procedure that I defined and is shown below.
SQL> create table test_proc
(
test_name varchar2(10),
test_ver number (3,3),
active_flag number(2)
);
Table created.
SQL> select * from test_proc;
TEST_NAME TEST_VER ACTIVE_FLAG
---------- ---------- -----------
AFP 1.5 2
PSA 11.89 0
EHIV 99.5 3
aHAVM 1.45 9
[code]....
Now from Pro*C function I want to pass a similar array of structure to this procedure and return it via the out parameter of the procedure back to Pro*C. How do I do it?
I am using the attached program but its giving me compiler error as follows..
Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);
1
PLS-S-00306, wrong number or types of arguments in call to 'PROC_MODIFY_TDEFS'
Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);
[code]....
how to you execute a stored procedure in ORACLE..For example in SQL SERVER its just
EXEC Proc_Name ParameterValues
How the hell do you do this in oracle i just want to test if my stored procedure works.
After compiling a simple valid SP in Toad for Oracle 8.5.1:
CREATE OR REPLACE PROCEDURE proc_test
AS
DECLARE v_name VARCHAR2(20) := 'Ed Edson';
BEGIN
DBMS_OUTPUT.PUT_LINE('Hi, my name is ' || v_name);
END;
The SP is not added to the schema. Also the three status fields (status, created and Last Update in the Procedure Editor remain <Unknown>. It is not a rights or privilege issue, because the creation works perfectly in SQL*Plus!
How to create a list in oracle stored procedure and that list contains two columns.
View 1 Replies View RelatedIam using oracle10g . when i created a simple stored procedure,got an error
PLS-00428: an INTO clause is expected in this SELECT statement
here is my code
create or replace procedure sp_TrialLiswt
as begin
select * from mivrs_studyinfo;
end;
I need to execute a .sql file in an oracle stored procedure.
View 9 Replies View Relatedoracle stored procedure which sorts date, meaning the recent records needs to be shown first.
View 6 Replies View RelatedHow to handle special charater '&' in stored procedure.
Following is a test code I want to Implement
CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS
BEGIN
INSERT
INTO TempTable(QueryID,LineID,data,Datetime)
SELECT
1,6,Emp,startdate FROM salary
[code]....
The code above resuts into INVALID state of stored procedure and cannot be executed.
How to call Oracle Form using a Stored Procedure?
Under
create or replace procedure ........
Begin
.......
End
Inside such procedures , is it possible to call a form ?
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].....
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 need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
View 14 Replies View RelatedI am trying to return a value of an oracle stored procedure using Excel VBA.
OS: Win XP SP3
Excel 2003
Ora Client: 11g
By trying different things I have noticed, that I could have troubles with the ODBC-connection. Maybe I am not using the right one. To store data returned from select statements I have an ODBC-Connection (Driver: Oracle in XE), which works perfectly, e.g.:
'-----------------------
Sub Extract_Data()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db_name, UserName, Password As String
cn.Open db_name, USerName, Password
[code]....
When debugging the connection string, I find the Provider=MSDASQL5.1.
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
View 7 Replies View RelatedExecute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4
All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like
create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have this in one of the packages WCL_LIB:
TYPE vc2_255_arr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
and i use it in one of the procedures as
PROCEDURE "WCL_EVENTS"
(p_event_id IN NUMBER,
p_event_arr IN Wcl_Lib.vc2_255_arr,
p_model IN VARCHAR2 DEFAULT NULL,
p_model_code IN VARCHAR2 DEFAULT NULL
but the calling procedure doesnt have any array.... Can I declare something like in the procedure to be called
p_event_arr IN Wcl_Lib.vc2_255_arr DEFAULT NULL,
I tried, but doesnt seem to work? so how to call the procedure, which has a array as mandatory, but calling one doesnt have any?
I have requirement to create a procedure that accepts an Array as IN parameter , Query a table using this array and return the result as refcursor . I tried to get it as below but not working .
CREATE OR REPLACE TYPE ARR_ID AS TABLE OF VARCHAR2(20);
/
CREATE OR REPLACE
PROCEDURE TEST_ARRAY
(P_ARR_ID IN ARR_ID,
P_CUR_OUT OUT SYS_REFCURSOR)
AS
BEGIN
OPEN P_CUR_OUT FOR
SELECT * FROM EMPLOYEE WHERE EMP_ID IN (SELECT * FROM TABLE(P_ARR_ID ));
END;
/
Oracle 11.2.0.1
Windows
create type np_type is varray(3) of number
/
create type cn_type is varray(3) of number;
/
create type cxn_type is varray(3) of varchar2(2000)
/
I created TEST table by below pl/sql block :
declare
execstr varchar2(2000) :='create table test(';
begin
for i in 1..80 loop
[Code]....
*ERROR at line 5: ORA-06550: line 5, column 8: PLS-00103: Encountered the symbol "MYPROC1" when expecting one of the following: := . ( @ % ;
The symbol ":=" was substituted for "MYPROC1" to continue.
SQL>The above procedure will insert the supplied numbered string into the test table which split the numbered string based upon supplied splitids of sp_table.
Currently some jobs created in WBT scripting need to converted into oracle,plsql.There is one job in WBT scripting, which will invoke the oracle reports inside and generate the PDF files in the destination path as follows:
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?USERID=%LOGONINFO%+server=rep_pscm9722+destype=file+desname=D:ORACLE10G\%CCALLRptName%+desformat=PDF+PARAMFORM=no+report=PCCALL.RDF")
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?
[code]...
Now, i want to convert this into oracle,plsql? Is it possible or not?
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]....