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; /
During developing Domain Index for Oracle 11.2.0.1.0 (problem also appears in 12с) i was faced with misunderstanding of parameter types of function ODCIIndexInsert in case of creating indextype WITH ARRAY DML option.According to Oracle documentation [URL}...
In case of WITH ARRAY DML option Oracle will invoke ODCIIndexInsert with following signature
FUNCTION ODCIIndexInsert( ia ODCIIndexInfo, ridlist ODCIRidList, newvallist varray_of_column_type, env ODCIEnv) RETURN NUMBER
In my case indexed column has datatype NUMBER so i defined varray_of_column_type as SYS.ODCINumberList STATIC FUNCTION ODCIIndexInsert(ia in sys.ODCIIndexInfo, ridlist in sys.ODCIRidList, newvallist in sys.ODCINumberList, env in SYS.ODCIEnv) RETURN NUMBER
Indextype was created as
CREATE INDEXTYPE test_index_type FOR test_eq(number, number) USING index_methods WITH ARRAY DML(number, sys.ODCINumberList)
[code]...
(problem occurs in all cases) CREATE TABLE test_table (id NUMBER (19,0)); CREATE INDEX test_index ON test_table(id) INDEXTYPE IS test_index_type;
When attempting to insert data in the table insert into test_table values (1);
oracle raise exception
CODEError starting at line 53 in command: insert into test_table values (1) Error at Command Line:53 Column:1 Error report:
[code]...
So my question is.Is it normal behavior of oracle (according to documentation)?What is correct signature of ODCIIndexInsert function in case of INDEXTYPE creation with 'WITH ARRAY DML' option and fact that indexed column has NUMBER datatype?
By the way if i define indextype without 'WITH ARRAY DML' option signature is clear, and working. But this approach doesn't satisfies our performance needs.Also if i define index type with option 'WITH ARRAY DML WITHOUT COLUMN DATA' and use signature static function ODCIIndexInsert(ia sys.odciindexinfo, ridlist sys.odciridlist, env sys.ODCIEnv) return number
Everything works too. But this approach doesn't satisfies our business needs.Is it a way to define ODCIIndexInsert parameter types (in case of indexing number column) so that batch inserting works according to documentation ?
FUNCTION ODCIIndexInsert( ia ODCIIndexInfo, ridlist ODCIRidList, newvallist varray_of_column_type, env ODCIEnv)
I am attaching full sql script to recreate environment and reproduce the problem.Type definition:
CREATE OR REPLACE TYPE index_methods AS OBJECT ( step number, STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER, STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER, STATIC FUNCTION ODCIIndexAlter (ia sys.ODCIIndexInfo, parms IN OUT VARCHAR2, altopt number, env sys.ODCIEnv)
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.
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.
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);
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);
I am writing a procedure in which I have a input string parameter in the following way..('NYC,ATL,OKC,KAC,LA'). I need to use that string to search values in the table.
Example
create or replace procedure search_city(p_string varchar2) /* paramater will be like ('NYC,ATL,OKC,KAC,LA') */ is v_city varchar2(40) begin for i in (select city_name from cities where city_code in (p_string ) ) loop
I have the procedure with out parameter is ref cursor.
l_sql VARCHAR2(32767); BEGIN l_sql := 'select query with appending procedure IN aparameters'; OPEN rc_rpt FOR l_sql;
Here procedure IN parameter is a string with comma separated value which is appended in the dynamic query IN clause.So some time the size exceeded more then 32767 and getting error.If i am using normal parametrized cursor this issue is not there,but i want to return only ref cursor for some java purpose.My oracle version is 10g.
i am trying to create a procedure that will take an input and based on that input it will delete or update a row, if the input is wrong we will dbs_ouput a line telling the user to enter the input again. can i have a input parameter in a procedure and insert multiple rows with a single insert on a procedure?
I have a requirement when i will pass the table name as a parameter in the procedure then the series of stametmemt is performed on the table whose name has been passed.
code look like below
PROCEDURE PROC_CREATE_PARTITION(TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2) AS V_PART_NM VARCHAR2(20); V_PART_CNT NUMBER; V_DATE DATE; V_SCHEMA_NAME VARCHAR(15); V_TABLE_NAME VARCHAR2(30);
[code]....
I am getting a error PL/SQL: ORA-00933: SQL command not properly ended
this error is show in the first select statement line where i have used the variable in the from list of table.
CREATE OR REPLACE PROCEDURE GET_NUM_ROWS(TABLE_NAME VARCHAR2) AS NUM_ROWS NUMBER; BEGIN SELECT COUNT(*) INTO NUM_ROWS FROM TABLE_NAME; DBMS_OUTPUT.PUT_LINE(NUM_ROWS); END;
When I try to compile it, the compiler says:
ERROR at line 4: PL/SQL: ORA-00942: table or view does not exist.
I have a pl/sql procedure having IN, OUT and IN-OUT parameters, this procedure in called from front end application. Now I need create a script to run this procedure from back end (sql prompt) and the result must be same as the front end application call to this procedure.
For the procedure I don't want to pass IN parameter instead to pick the value from the package where the derivation is defined. how to run this procedure from sql prompt without passing value for IN parameter.
I am wanting to write a procedure which takes in a TIMESTAMP datatype as a parameter. I only really want the time, not the date part.I am struggling to understand however what format the inserted TIMESTAMP would take. E.g
I created a procedure with cursor to inquiry a table. There is more than 1 million records on the table. For each record, I would like to transfer two columns on table as parameter to shell and feedback to procedure as varcahr2.
This is my sample(I only use 10 records for the testing).
create or replace procedure task_file(task_check in varchar2,file_name out varchar2) is k number :=0; v_task_number number :=0; v_task_filename varchar2(30) := null; cursor jobchk is select task_number,task_filename into v_task_number,v_task_filename from my.tasktable where task_filename=task_check and rownum <=10; [code]........
************************* SELECT vendor_id, summary_flag FROM ap_suppliers WHERE vendor_id = :param; --'4551'
The Results:
************ VENDOR_ID SUMMARY_FLAG ----------------------- 4551 N
Then I create the procedure:
***************************** CREATE OR REPLACE PROCEDURE myproc4 ( p_vendor_id IN ap_suppliers.vendor_id, p_summary_flag OUT ap_suppliers.summary_flag ) AS BEGIN
[code]....
Warning: compiled but with compilation errors.
I want to create a procedure that call vendor_id (parameter) and the output like the this:
VENDOR_ID SUMMARY_FLAG ----------------------- 4551 N
how to use ref_cursor as out parameter for insert procedure
This is the procedure iam having and when compiling it is througing error is
create or replace procedure proc_insert_policies_temp1( i_policy_id in int, i_corporate_name in varchar, i_corporate_address in varchar, i_divisionid in number,
I have a procedure where it contains an XML as input parameter.And i have to debug this procedure in pl/sql developer to check whether this is the correct XML and inserting the XML data in to my table. But when debugging, I am getting an Error "EXPRESSION IS OF WRONG TYPE". Below is my
declare px_xml xmltype; begin px_xml:='Xml content'; --call the procedure package_name.procedure_name (parameter1 => :parameter1, px_xml => px_xml, pvo_out_mesg => :parameter3); end;
Is there any other way to debug XML in plsql developer.
I have an procedure with four in parameters in it. Now, I want to add an out parameter to it. The issue is the procedure is called from many other procedures and triggers. Since we can't set default value to out parameter as we can for in parameter, how we can achieve the requirement without having to update all the places where this procedure is being called.
I've the following package having a procedure with OUT parameter as refcursor .The following is the sample example having same structure with different table name,exception handling in my dev environment
CREATE OR REPLACE PACKAGE TEST_PACK IS Type refCursor is REF CURSOR; PROCEDURE TEST_PROC (out_data out refCursor); END; [code]...
i am getting the follwoing errror ORA-00932: inconsistent datatypes: expected - got.
