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

SQL & PL/SQL :: How To Call Function Using String Variable

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

Forms :: Executing Procedure Based On Variable Name

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

Executing Function From Ksh Script?

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

Executing Function In A Package?

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

PL/SQL :: Executing A Function With RETURN X%ROWTYPE Is Used

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

Executing Function To Return Username In Oracle

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

Executing Stored Procedure / Function From Database Link?

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

Performance Tuning :: Removing Nvl Function Then Query Executing In 2 Min Condition

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

How To Concatenate A String With Variable

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

SQL & PL/SQL :: Read Date From String Variable

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

PL/SQL :: Bind Variable As A Literal String Value

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

PL/SQL :: Passing Json String Into Varchar2 Variable

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

ORA-01458 - Invalid Length Inside Variable Character String

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

Precompilers, OCI & OCCI :: ORA-01458 / Invalid Length Inside Variable Character String

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

SQL & PL/SQL :: Passing Variable Into TO_DATE Function

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

SQL & PL/SQL :: Passing Variable Into TO_DATE Function?

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

PL/SQL :: Binding Variable In Procedure And Function

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

SQL & PL/SQL :: Function To Split A String?

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

SQL & PL/SQL :: Concatenate String In Function?

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

Function To Replace All Occurrences Of A String?

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

SQL & PL/SQL :: Function Returns Searched String?

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

Application Express :: 4.2 Translate (Function And Global Variable Declaration) Data?

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

SQL & PL/SQL :: Function To Check Length And Build A String?

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

SQL & PL/SQL :: Split String Function For Large Number Of Records?

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

SQL & PL/SQL :: String Function - Extract Domain From Email Address

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

PL/SQL :: Translate Function Remove Alpha Characters From String

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

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

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

ORA-02085 - Database Link String Connects To String

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

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

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







Copyrights 2005-15 www.BigResource.com, All rights reserved