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
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 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 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 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.
I have a problem with passing procedure name dynamically with record type i/p parameter..I'm not attaching any insert/create table statements, as I'm unsure of forming the sql statement dynamically..
CREATE OR REPLACE PACKAGE med_order_pkg AS TYPE deid_med_order_typ IS RECORD(....) L_deid_med_order_typ deid_med_order_typ; PROCEDURE RULE_MASTER_PRC (P_IN_RULE IN deid_med_order_typ); END;
[code]....
From the above, I need to execute a procedure with record type as i/p parameter..V_SQL should form the statement & execute another procedure which comes into the variable V_MSG_PROC .I'm having difficuly in forming the statement...(I did it by hard-coding the procedure with parameter in the next line which is commented out & it works...So how can I modify V_SQL in the above statement?
Procedure countyname(i_lat IN Number, i_lon IN NUMBER, o_countyname OUT VARCHAR2);
The procedure works fine when the input parameter values are small precision like 30.653, -618.765 etc.it fails if the Input parameter values have more precise like 35.694872140886...I think the IN Number can only take upto certain precission.
Is there any way I can specify the precision for a NUMBER input parameter in a procedure?
ex: Procedure countyname(i_lat IN Number(30,10), i_lon IN NUMBER(30,10), o_countyname OUT VARCHAR2);
when I tried the above statement it doesn't compile it gives PLS-00103 error.
CREATE OR REPLACE PROCEDURE SCOTT.GetEmpList(p_recordset OUT SYS_REFCURSOR) AS BEGIN OPEN p_recordset FOR SELECT * FROM scott.emp WHERE deptno=10; END GetEmpList;
Now i would like to execute this procedure in sqlplus which will display the following:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7782 CLARK MANAGER 7839 6/9/1981 2450 10 7839 KING PRESIDENT 11/17/1981 5000 10 7934 MILLER CLERK 7782 1/23/1982 1300 10
I am trying to pass many characters to the in mode parameter using procedure , but i am getting the below error.
ORA-06550: PLS-00172: string literal too long
CREATE TABLE USR_DETAILS ( LOAD_ID NUMBER NOT NULL, LOAD_DATE DATE, USER_VALUE VARCHAR2(4000 BYTE), USERID VARCHAR2(4000 BYTE) ) insert into user_details values('1','2/10/2011','PROD1','USER1'); insert into user_details values('2','2/10/2011','PROD2','USER2'); [code]......
I have written the code as below...
Procedure concept :
Here i will seperate the strings(input parameters) using comma(,) symbol . Here users may pass more than 5000 characters to single in mode parameter, so i have tried with clob datatype but no luck.
CREATE OR REPLACE procedure user_details_proc (user_value1 IN varchar2, user_value2 IN varchar2, user_value3 IN varchar2, user_value4 IN varchar2, user_value5 IN varchar2, userid IN varchar2 ) as [code].......
I am reading in a selection of parameters. I have created a new variable which I want to set according to the value of one of the input parameters.
I am doing this straight after declaring the variable, but before the cursors and BEGIN statement It is throwing an error when I do this - but I have to do it before the cursors.the variable I am setting is: v_fptransType you can see the IF statement towards the end of the code.
the error I am getting is:Error(28,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "IF" to continue.
beginning of the
create or replace PROCEDURE "P_GLPOST" (i_entity IN varchar2, i_transType IN varchar2, i_startDate IN VARCHAR2, i_endDate IN VARCHAR2, i_accountPeriod IN VARCHAR2, i_includeInternals IN NUMBER, i_chargeable IN NUMBER, i_trialPost IN NUMBER, i_postingReport IN NUMBER, TESTER IN VARCHAR2) is --set serveroutput on size 1000000;
