SQL & PL/SQL :: Executing Function From String Variable
Oct 2, 2013
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;
View 2 Replies
ADVERTISEMENT
Aug 6, 2010
I 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?
View 6 Replies
View Related
Apr 1, 2011
I need to execute a procedure based on a value in a form. So the procedure name will be changing for value selected in a list.
I need to know a method where i could store the procedure name in a table and when ever i select a value from the list, the respective procedure needs to be executed.
View 1 Replies
View Related
Mar 5, 2009
I don't know how to control if a call to a PL function return errors.
I have this ksh:
executeFunction () {
echo "[executeFunction ]-> Phoebus DB started"
sqlplus -s /nolog <<!EOF!
CONNECT $USER/$PWD@$DB
@/sql/ejecutaFuncion.sql
EXIT;
!EOF!
}
executeFunction
And the code of "ejecutaFuncion.sql" is:
executeusers.startProcessing();
EXIT
How i can controle if the "startProcessing" function has any problems from the ksh?
View 1 Replies
View Related
Mar 24, 2008
I have created one function in the package.
function : Tier_wh
package : Order_DESC
Function defined as
function TIER_WH( message in out Xorder_desc)
...
...
end;
Xorder_desc is defined as
create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);
how to execute this function which is defined in the package .
View 1 Replies
View Related
Jul 6, 2012
This error is returned when executing a Function with the RETURN X%ROWTYPE is used.Here is the code.
CREATE OR REPLACE function FE_GET_addr (
p_PIDM IN addr.addr_PIDM%TYPE,
p_atyp1 IN VARCHAR2,
p_atyp2 IN VARCHAR2,
p_atyp3 IN VARCHAR2,
p_atyp4 IN VARCHAR2
[code]....
View 5 Replies
View Related
Feb 6, 2006
I have created a Package Body and Package Spec for a function to select a username and a password from a table and return the username.
The code i have created is this:-
CREATE OR REPLACE PACKAGE BODY USER_LOGIN
AS
FUNCTION user_select (USERNAME_IN VARCHAR2,
PASSWORD_IN VARCHAR2)
RETURN VARCHAR2 IS
USERNAME_OUT VARCHAR2(12);
BEGIN
[code]........
The package body and spec compiles successfully but i am having trouble when i execute this function. I am trying to do this :-
VARIABLE RETVAL VARCHAR2(12)
EXEC User_login.user_select('HELLO','HELLO',:RETVAL );
but i am getting the following error
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'USER_SELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
View 5 Replies
View Related
Feb 24, 2011
I have 2 databases:
Database A
Database B
In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.
I would like to execute my stored procedures and all associated functions in database A on my data in database B.
Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.
Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...
I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.
View 2 Replies
View Related
Aug 10, 2011
when am trying to use nvl for one condition it is taking lot of time to execute but when am removing nvl function then the query executing in 2 min. condition is given below
(HOI2.ORG_INFORMATION1)=nvl(TO_CHAR(:p_set_of_books_id) , HOI2.ORG_INFORMATION1)
but when am using the same condition as below the querry executing in 2 min
(HOI2.ORG_INFORMATION1)=TO_CHAR(:p_set_of_books_id)
my query given below
(SELECT cust.customer_number cust_no, cust.customer_name customer,
cnv.item_no, SUM(wd.shipped_quantity) shp_qty_nos,
0 rtn_qty_nos,
ROUND(SUM(cnv.cnf * wd.shipped_quantity), 3) shp_qty_tons,
0 rtn_qty_tons, 0 net_shp_qty_nos, 0 net_shp_qty_tons
[code]...
View 30 Replies
View Related
Mar 5, 2008
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.
View 2 Replies
View Related
Jul 29, 2013
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 Related
Nov 5, 2013
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}
View 11 Replies
View Related
Jan 11, 2013
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
View 3 Replies
View Related
Sep 30, 2010
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
View 4 Replies
View Related
Apr 16, 2009
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
View 3 Replies
View Related
Apr 14, 2011
select to_date('13:14:00', 'HH24:MI:SS') FROM DUAL;
what is output of this?
and why this result is coming?
View 32 Replies
View Related
Aug 10, 2010
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.
View 9 Replies
View Related
May 28, 2013
I have started reading ORACLE 11g R2 PL/SQL programming doc, and I am stuck at bind variable. I just copied the example in the books and found error -
First I have written below procedure and compiled successfully -
CREATE OR REPLACE PROCEDURE FORMAT_STRING ( string_in IN OUT VARCHAR2) AS
BEGIN
string_in := '[' || string_in || ']';
END FORMAT_STRING; Then I have written below function and also compiled successfully -
create or replace function join_strings (str1 varchar2, str2 varchar2)
[code]......
View 3 Replies
View Related
Oct 18, 2004
I have a string like 10.01.03.04.234 or 234.05.07.032.� Is there a simple function in Oracle that would give me
10
01
03
04
234 as seperate values in a select clause.
I would like to do a
Select function(10.01.03.04.234)
into var1, var2, var3, var4, var5
from ....
I can accomplish the same with substr/length/instrs in a procedure...but looking for a simple function, if any, instead of writing a proc/function....
View 39 Replies
View Related
Jul 29, 2010
i m create one function in which i have to concatenate result likeDate is 19/07/2010'. i tried to concatenate in select statement. in this case function is created successfully but it gives error when it run.
CREATE or replace function TEST(FINYEAR VARCHAR2)
RETURN VARCHAR2 AS
QRY_STRING VARCHAR2(1000);
BALANCE VARCHAR2(100);
[code]...
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at "TEST", line 11
View 19 Replies
View Related
Jan 9, 2007
Years ago, someone created a database in Oracle that was carried over and now sits in Oracle 10g. I am developing an application that queries this database and returns the result as XML.
Many (thousands) of rows have an item description that contains an ampersand. I want each of these pieces of data to have it written as & amp; (had to add a space so it would show up here, but you know what I mean) instead of &, but I don't feel like doing thousands of UPDATEs to change this.
Does Oracle have any global find/replace functions that I can call? I'd rather do one update statement that replaces all occurances of & with & amp; but I can't seem to find a function that will do this.
I'm thinking something that would work like:
UPDATE table SET column1=REPLACE(column1,oldstr,newstr);
View 4 Replies
View Related
Nov 6, 2010
I just think about write a function with gives me a searched string from table.
select col1, col2 from the_table
col1 col2
--------------------------
SMS yes
Melodies no
Java build
...
And I would like to make a function or something like that where input parameter is from col1 and result is col2. For instance:
select the_function('SMS') from dual;
Result: yes
how to write it?
View 13 Replies
View Related
May 1, 2013
i 'm using APEX 4.2.1.00.08 and i 'm wondering if there is a way to translate "Function and Global Variable Declaration" textarea. Every other script textarea is available for translation, but not this.
I know that this is code is loaded on header but may contains important alert messages of global functions.
View 1 Replies
View Related
Dec 18, 2012
I have created a VIEW with a column containing user-defined aggregated values(stores the output in varchar2 data type). Some of the records exceed the maximum limit, for example varchar2(4000). I was suggested a PL/SQL function to check the length and build a string if necessary..
CREATE OR REPLACE
FUNCTION SUBURL(col1 IN VARCHAR2, col2 IN varchar2)
RETURN varchar2
[Code].....
Here, I got the following error,
Error(4,25): PLS-00103: Encountered the symbol "," when expecting one of the following:
:= . ( @ % ; not null range default character
View 14 Replies
View Related
Aug 6, 2010
I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function
Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)
CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED
[code]....
But here I am facing 2 problems.
1. The function is not accepting a large string & I am getting the error
ORA-01704: string literal too long
2. The function is going for an infinite loop.
View 10 Replies
View Related
Oct 12, 2011
I'm trying to extract the domain from email addresses like from clay500@msn.com I want to pull msn
I figured I would use the POSITION function, but it fails, even when I cut and pasted the example from the syntax guide
SELECT POSITION ('CAT' IN 'CATCH') FROM DUAL;
the guide says you get the answer 1
but I get ORA-00907: missing right parenthesis
What gives?
View 8 Replies
View Related
Feb 7, 2013
I am on 11g.
I need to remove the alpha characters from a string, leaving only numbers, but I am getting unexpected results:
SQL> SELECT TRANSLATE('3N', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL) a FROM DUAL;
A
-
I thought this would leave the 3 from the 3N, but it is returning an empty string. For my application, the string '3N' could be any length, will only contain letters and numbers, and the letters will always come at the end, but there could be more than one letter
VALID INPUT samples:
4
25
11F
361NG
8ABC
View 6 Replies
View Related
Aug 30, 2011
I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
View 2 Replies
View Related
Jun 19, 2012
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 ?
View 7 Replies
View Related
Mar 14, 2013
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
View 3 Replies
View Related