PL/SQL :: Execute Procedure By Passing Number Array
Oct 26, 2012
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.
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 using database version Oracle Database 10g Release 10.2.0.1.0 - Production. I have multiple schema's in my database and want to read the data from multiple schema's and have to insert into a single table. For that i was thinking to pass the schema name as parameter and fetch data accordingly. While creating procedure i got the below mentioned error, yet i haven't got any result on this.
ORA-00936: missing expression ORA-06512: at "INTERNATIONAL.MPLS_PROC_TEST";, line 66 ORA-06512: at line 15
following is my procedure
CREATE OR REPLACE PROCEDURE MPLS_PROC_TEST ( P_CLIENT_CODE IN VARCHAR2, P_VARIANT_CODE IN VARCHAR2, P_START_DATE IN DATE, P_END_DATE IN DATE, P_MEDIA_CODE IN VARCHAR2, P_SCHEMA IN VARCHAR2 [code]........
I am trying to execute a STORE PROCEDURE from SQL*PLUS with no success:
SQL> execute PACKAGE.PROC(201011,'144792'); BEGIN PACKAGE.PROC(201011,'144792'); END;
* ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PROC' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
In fact, when i do: desc PACKAGENAME . I see that the procedure is waiting for 3 parameters and one of them is a REF CURSOR type:
SQL> desc PACKAGENAME PROCEDURE PROCEDURENAME
Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PWEEK NUMBER IN PCLIENT VARCHAR2 IN CRESULTS REF CURSOR IN/OUT
I need to count number of elements in the same catagory of an array.. For example, an array consists of {'a','b','c','c','a','d','c'} means, i need to display like a=2, b=2, c=3, d=1.
I have written the below code.
declare type array_val is varray(10) of varchar2(15000); counter number:=0; SMQ_NAME ARRAY_VAL:=ARRAY_VAL(); begin
[code]....
But its not showing exact output as my requirement..
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; /
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.
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);
I need a query to whome i will pass month in number and that should return name of month. If i input 1 it should return Jan,2,Feb but don't use decode etc.
Is there any query with Julian format or any other date function?
I have a procedure that accepts a date as an input parameter. This parameter is used in a select statement to match on a table column of date type.
1) Should the parameter be declared and passed in as a varchar2 and then converted using to_date() or declared as a date type? If it's declared as a date type what format should it be passed in as, ie. '01-MAY-2010' or '01-MAY-10' or etc.
2) When I run the sql query, knowing data exist, like "WHERE test_date = '22-NOV-10'" I get no results so I know I'm not matching the date correctly but if I use "WHERE test_date LIKE '22-NOV-10%'" I get the correct results. Trying to build a variable to match this, and use in a cursor, consistently gives me the 'non-numeric character was found...' error
3) formatting the test_date input variable to match on the date type column.
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.
how can I pass the sh variable (.i.e file name stored in sh variable called($F)) as a input of below mention procedure (YODEL_XL_ INS_SDG_ COMMER_ PROD)
for F in *.dat; do # echo $F # #sqlldr apps/apps control=$CONTROL data=$F # Below Part is used for Add the file name into table [code]...
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 have one package, that included so maany ref. cursor package..Now , i want to execute of one procedure in this package, how can i do it ..
CREATE OR REPLACE package Pkg_HR As Type Typ_Cur Is Ref cursor; procedure getHR_initiate(pvFinYr Varchar2, Cur_HR_Init OUT TYP_CUR); procedure getFin_Yr(Cur_Fin_Yr Out TYP_CUR); procedure getCutOFfStatus(pvAppsee1_Appsr2_Review3 Varchar2, pvFinYr Varchar2, Cur_HR_Init OUT TYP_CUR); procedure SetEmp_For_FinYr(pvFinYr Varchar2, Cur_Emp OUT TYP_CUR); End Pkg_HR ;
My Package Body is :
CREATE OR REPLACE package body Pkg_HR As procedure getHR_initiate(pvFinYr Varchar2, Cur_HR_Init OUT TYP_CUR) IS Begin Open Cur_HR_Init For Select HR_FINYR HR_FinYr, To_Char(HR_PERIOD_FROM,'DD/MM/RRRR') HR_PERIOD_FROM
My need is to execute procedure in cycle. I can run
EXEC zoo.pkg_z184.rep184_fill( date '2011-09-28' ); EXEC zoo.pkg_z184.rep184_fill( date '2011-09-29' );
But I'd like to do it in cycle when the period is much bigger. Here is my try to do it:
begin
for l_row in ( select date '2011-09-27' as arcdate from dual union all select date '2011-09-28' as arcdate from dual union all select date '2011-09-29' as arcdate from dual union all select date '2011-09-30' as arcdate from dual ) loop EXEC zoo.pkg_z184.rep184_fill( l_row.arcdate ); end loop; end;
It returns error:
ORA-06550: line 9, column 6: PLS-00103: Encountered the symbol "ZOO" when expecting one of the following: := . ( @ % ;
The symbol ":=" was substituted for "ZOO" to continue.
I have a procedure which will execute on every Monday. Same is not executed last Monday. Can I execute the Procedure on some other day with out changing the actual procedure?
I have written a package in that package i have written a procedure. That procedure has two i/p and two o/p variables. I am calling the procedure using the below syntax.
I have created the following procedure. Since I am using this first time I don't know how to execute this.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package IS TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER; PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots ); 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"
Is there a way to grant EXECUTE on a group of procedures/functions/packages to a particular role , in a single statement ? Or we have to do it one by one... like GRANT EXECUTE on event_main to role1, GRANT EXECUTE on event_main2 to role1, GRANT EXECUTE on event_main3 to role1,
I'm running a PL/SQL with a For Loop cursor, but when trying to execute it doesn't run. It is as if there is no data, but I ran the cursor separately in a SQL Plus session and it runs perfectly. I'm enclosing the file with the procedure.
