I'm trying to pass a JSON string into a stored procedure that has a single VARCHAR2 variable.
begin
SAVE_JSON('{"app_id":"800414","merch_id":"84657156","user_id":"6985","P25_DBA_NAME":"TEST"}');
end;
/
I'm getting the ORA-20001: ORA-06502: PL/SQL: numeric or value error
I have a stored proc which takes IN parameter of datatype varchar2.When I am trying to run the proc it is throwing error that "input buffer too small".The datatype assigned to IN parameter is of varchar2(200) but actually the length of the parameter passed is around 500 characters.the way to increase the length of Input parameter to 500 characters??
View 16 Replies View RelatedI want to pass a variable in unix to oracle exp command
I have a file with owner and password
I did
export user=$(head -$riga $file | tail -1)
echo $user
let riga+=1
export psw=$(head -$riga $file | tail -1)
echo $psw
exp $user/$psw file=exp_$current.dmp
but this doesn't work.
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
[Code]...
select to_date('13:14:00', 'HH24:MI:SS') FROM DUAL;
what is output of this?
and why this result is coming?
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 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 procedure named 'GetShipperinfo' which takes i_name as input and needs to build a cursor taking i_name as input
i.e.
The following sql when executed at sqlplus prompt gives correct results.
select dept, supplier, shipper_id
from shippers
where upper(shipper_name) like upper('Frank Robert%');
How can I transform this inside a cursor within a procedure passing 'Frak Robert' value as i_name input.
i.e I should be able to call the procedure as follows
sql> variable v1 varchar2;
sql> exec pkg_shipment.GetShipperinfo('Frank Robert',:v1);
sql> print :v1;
Should the cursor inside the procedure be built as follows
cursor c1 is
select dept, supplier, shipper_id
from shippers
where shipper_name like ''||upper(i_name'%''||)'';
Iam unable to build the sql for the cursor.
4.1.2theme 24
I have a .js process in the before header of a page which has this pl/sql process using htp script javascript function {code}
function check(flag_val){ if(flag_val=="N")
document.getElementById("btn").disabled = true;
else
document.getElementById("btn").disabled = false; }
{code}
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
<td align="right">
<button class="btn btn-success" type="button" id="btn" onclick="check(flg);"
title="Accept Lead" style=" width:50px;">Flag value</button> </td>
{code}
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='%'
[code]....
All I want to do is run a procedure (DO_H_RUN) that returns a number into "v1" and then use that variable to return the result sets in three selects. This is all in TOAD. If I do not have a where clause, the code executes fine (allowing me to see the data in multiple Grid tabs)! But, I want to filter the rows.
I've tried doing a number of different things (finding all sorts of stuff on the Web) like declaring cursor variables and the like but after spinning on this for a couple of hours, I'm stuck.
Here's my attempt (the names have been changed to protect the innocent!):
declare
v1 Numeric;
BEGIN
DO_H_RUN('Me', v1);
--DBMS_OUTPUT.PUT_LINE(v1); --This line works okay!
select * from h_run where h_run_id=v1; --NO GO
[code]....
I am using the below code to update specific sub-partition data using oracle merge statements.
I am getting the sub-partition name and passing this as a string to the sub-partition clause.
The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.
We are using Oracle 11gr2 database.
Below is the code which I am using to populate the data.
declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin
[code]....
I am trying to replace the string_name in the following execute immediate statement with a concatenated
string_||v_variable.
execute immediate
'select
table_name
from
user_tables
where table_name = :a'
into v_table
using 'string_name';
I tried different declaration and concatenations but nothing worked so far.
In our mainframe file we have date coming as format June 1,2013 . How to read and convert it in 01/06/2013 format to load in oracle table?
View 11 Replies View RelatedI have a package includes 22 functions, each function just returns a sql template (clob type).
I also have a stored procedure called query_builder, query_builder has applicationName and statementName as parameters. I need to call these functions in the package based on the given applicationname and statementname.
CREATE OR REPLACE PROCEDURE Query_builder (ApplicationName varchar2, StatementName varchar2) IS
SQLSkeleton varchar2;
BEGIN
PackageName := ApplicationName||'_SKELETON;
SQLSkeleton := PackageName.StatementName; -- I know this will not work, but how can i call these function dynamically?
I'm trying to find a simple way of getting around this. I have a PL/SQL procedure which loops through a list of values in a table. These values are the actual names of the functions I want it to run in sequence. The problem is I can get the string value each time but I need to store the return value of each function into a number variable e.g.
BEGIN
open all_tests;
fetch all_tests BULK COLLECT INTO test_tabl;
close all_tests;
for myindex IN 1..test_tabl.count LOOP
time_taken := test_source1 ==> this will work but how do I avoid hardcoding the name of the function to be executed and use the test_name value instead?? time_taken is declared as a number (ie the return value of each function is a number)
test_detail := test_tabl(myindex).test_name; ==> test_detail now contains the string name of the function
dbms_output.put_line('Test detail is ' || test_detail);
end loop;
Oracle Database 10g
Enterprise Edition Release 10.2.0.5.0
- ProdPL/SQL Release 10.2.0.5.0
- ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0
- ProductionNLSRTL Version 10.2.0.5.0
- Production.
I have a problem when creating a dynamic statement. The problem is in the bind variable ':OLD.CUST_NAME' ,..my question is is there an escape character to treat the bind variable as a literal string?
{code}v_str2 := '''CUST_NAMES='''||'||'|| ':OLD.CUST_NAME' ;
EXECUTE IMMEDIATE 'create or replace trigger trg_' || SUBSTR (rec_cur.table_name, 1, 26) || ' before insert or update or delete on ' || rec_cur.owner || '.' || rec_cur.table_name || ' declare begin if UPDATING then FIFAPPS.ibug.log_errors('|| v_str2 ||' ); end if; end;';
{code}
I want the output in a trigger something like this:{code}
if UPDATING then FIFAPPS.ibug.log_errors('CUST_NAMES='||:OLD.CUST_NAME );{code}
Where I am copying the data from one database to another I am getting this error.
ORA-01458: invalid length inside variable character string
This is applicable for only character string.
The source Database is
Oracle9i Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
The destination database is
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production
I want to insert data into a RAW column in a table. The below code snippet is used for the same
OCIRaw **l_rawPtr = new OCIRaw*[10];
for(int i=0;i<10;i++)
{l_rawPtr[i] = NULL; }
int max = 0;
for(int i=0;i<10;i++)
{
OCIRawAssignBytes(l_environment, l_error, l_char, strlen((const char*)l_char), &l_rawPtr[i])
[code].....
When the statement is getting executed, i am getting the following error. Error - ORA-01458: invalid length inside variable character string
i am fairly new in the oracle arena, but what would cause a statement such as
ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL
to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.
I'm trying to set up a PL/SQL dynamic action that is triggered by clicking on a button. The dynamic action sets two values in a table and then I would like to redirect to another page. Setting the values works fine. The code is below:
declarel_information varchar2(255) := :P94_FILENAME;BEGINcommit;UPDATE VDI_METADATASET CREATEUSERLISTNAME = l_information, CREATEUSERLISTON = sysdate();commit;htp.init;owa_util.redirect_url('f?p=&APP_ID.:81:&APP_SESSION.::NO:::');END; The redirect fails with the following eror: "Error: parseerror: JSON.parse: unexpected non-whitespace character after JSON data"
how to redirect to a page from within PL/SQL . None of them result in success.
I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me
1,10,11,2,....like that.
What is the best alternative to using VARCHAR2 if the output gets truncated after 4000 bytes in a PL/SQL function?
View 10 Replies View Relatedin our application we are using clob column instead of varchar2 because varchar2 does not allow more that 4000 chars, so Using clob allows to put data of any length, will it cause performance issues ? we have this column in almost in all tables .
View 2 Replies View Relatedcreate table members(
memberID varchar2(7),
locationID number(5),
roomID varchar2(5)
)
/
[Code]....
MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2006253 1 1-A
2006253 1 1-A
2006265 1 1-B
2006240 1 1-C
2005064 1 1-D
2007836 1 100
2007211 1 101
2007075 1 102
2007110 1 105
[Code]...
Function created.
SQL> select * from members
2 order by safe_to_number(roomID);
MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2007185 1 2
2007089 1 3
2007023 1 4
[Code]....
How can I sort the output to get the result like below
2007209 1 1
2006253 1 1-A
2006253 1 1-A
[Code]...
and so on...
Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.
This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.
We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.
I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.
how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that
I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.
create table A(Col1 number);
Inert into A values(1);
Inert into A values(2);
Inert into A values(3);
Inert into A values(4);
Create table B(Col1 Varchar2(100));
Insert into b value ('1,2,3');
Select * from A where col1 in (select col1 from b)
Error: Invalid Number
Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.
How to convert a varchar2 column to CLOB when there is a thousands of records in it.
View 15 Replies View Related