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]...
I have a requirement where in I have to store large data in one of the database columns using stored procedure.
I have declared the column as CLOB as it can store upto 4GB and also the input parameter for the procedure as CLOB. But when I am trying to pass large data it is not allowing to store as it is throwing literal string too large error.
Is there any restriction in the data size to be passed to the stored procedure?
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?
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 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.
create or replace procedure ab(a in varchar2, b in varchar2) is test varcha2(8); begin if (a is not null) then for i in(select c from t where c between ||'''||a||'''|| and ||'''||b||'''||) loop test:=i.c end loop; end if; end;
I want both parameter input values to be enclosed in quotes so that it considers both parameter values as char.Receiving ora 00936 missing expression error.
select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times from ( select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date , mod(rownum,14) mod_result from all_objects
[code]...
need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.
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;
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.
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 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;
I have an requirement to create an function which takes table or hierarchy of tables as input and returns xml output in hierarchy. Below given is the Tables hierarchy.
Requirement: Initially input was table name and using table as the root node output should generate xml of all the records of child tables.But now requirement is to give the flexibility to user to select what hierarchy he needs i.e he may select AAAA, CCC and in the nodes C1,C2,C3 and C4 if he doesn't want C3 then that node should not be shown in output.
I have created Hierarchy table having 3 columns SI.No, ParentNode and ChildNode and entered the above hierarchy relation.
1. What is the best to way (design)to pass input parameter for the function.
2. How to generate hierarchy in xml using DBMS_XMLGEN
how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me.
xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.
i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is taking
here is the detailed program ... why this is not taking and tell me how to pass the values through it..
declare v_name char(6) ; cursor c1(c_name char) is select name, parent,child,status from relation start with name='%' connect by prior parent=child union
I am trying convert number value in date. I know somewhere I doing mistake. But I cant get it.
Here is my Partial Code
create or replace PROCEDURE "REPORT_ARTICLEMOSTVIEWED2" (
[Code]....
Error starting at line 5 in command: EXEC REPORT_ARTICLEMOSTVIEWED2(null,null,null,null,:RC) Error report: ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "IIS_ORACLE_11GR2_LIVE.REPORT_ARTICLEMOSTVIEWED2", line 22 ORA-06512: at line 1 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation. RC
How do I put condition for Null value in this procedure And set dateTo = sysdate if v_day,v_month,v_year are null.
How to use date as an input parameter,im supposed to use varchar2 as the data type
CREATE OR REPLACE PROCEDURE mail1 ( recievers VARCHAR2 ,p_date in varchar2 ) IS sender VARCHAR2(30) := 'xyz@gmail.com'; mailhost VARCHAR2(100) := 'host address'; TAB VARCHAR(2) := CHR(9); mail_conn utl_smtp.connection;
[code].....
cursor c1 is
--select activity_date,procedure_name,status_message,error_desc from staging_activity_log where rownum between 1 and 10 ; select activity_date,procedure_name,status_message,error_desc from staging_activity_log where error_desc is not null and trunc(activity_date) >= to_date(p_date,'DD-MON-YYYY') ; BEGIN
[code].....
when i execute i get
BEGIN mail1 ('xxx@gmail.com,yyy@gmail.com,'28-jan-2008'); END; Error at line 1 ORA-06502: PL/SQL: numeric or value error ORA-06512: at "MAIL1", line 27 ORA-06512: at line 1
and then there is the actual pl/sql process which has a cursor
{code} declareflg varchar2(1); ..begin selectflag into v_flagfrom table where rownum = 1; -- actual conditions are different but this returns one value.
Then below using htp package I had html table with something like
For some reason the script function is not called if I use onload. If I change the onload to onclick it works. Also, if I pass check(flg) it does not work. If I use check(''N'') then is works fine. question is how do I pass a pl/sql variable flg in this case to a javascript function as a parameter? I basically, want the value of flg checked on page load and then if it is N then disable the button. Or else keep it enabled.
i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is takinghere is the detailed program ... any body can tell why this is not taking and tell me how to pass the values through it..
declare v_name char(6) ; cursor c1(c_name char) is select name, parent,child,status from relation start with name='%'
I have a scenario where I have to pass a paramter to "in statement". When I run the query from SQL plus its working fine.......but when I run the query at run time it doesnot come back with the results. I am sure its formating issue.
This is how I create a paramter
_sbInStatement.Append("("); foreach (ListItem item in _listBox.Items) {
I have a procedure with 20 parameters, acutely it is to update a table and each param represents respective columns in a table. I want to update only few selected columns(random), as of now am passing Null as param values for remaining.
Is there any way to ignore the unnecessary parameters instead of passing NULL value.My Proc call looks like...
we have a table which name is empl have two column (comapnyname,jobdate) i want output after passing parameter which name is month which value is like(feb 2011,or mar 2011 or apr 2011 or jun 2011 etc) output should come the nameof company with jobdate whose jobdate is equal to 3 or greate than three
name of company,jobdate whose job date in particular month is greater than three or equal to three the purpose of this we want to find only the name of company where we visit morethan three times in particular month table structure is
insert into empl values('ABC','1-feb-2011') insert into empl values('ABC','10-feb-2011') insert into empl values('ABC','21-feb-2011') insert into empl values('xyz','18-feb-2011') insert into empl values('xyz','1-feb-2012') insert into empl values('xyz','1-apr-2011') insert into empl values('ABC','28-feb-2012')
