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


ADVERTISEMENT

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 :: Getting Stuck With To_date Function / ORA-01858

Apr 22, 2011

I always get stuck with to_date function.

I keep getting errors when I run this query.

select * FROM table1 WHERE AS_OF_DATE = to_date('9/30/2010 12:00:00 AM','YYYYMMDD HH24:MI:SS')

Error code is : ORA-01858

View 12 Replies View Related

Passing Variable To Exp In UNIX?

Sep 20, 2013

I 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.

View 4 Replies View Related

SQL & PL/SQL :: Passing The Char Variable In Cursor

Apr 9, 2013

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]...

View 9 Replies View Related

Passing Sh Variable Value To Input Of PL/SQL Procedure

Aug 30, 2012

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]...

View 8 Replies View Related

SQL & PL/SQL :: Passing Input Parameter To Like Variable In Procedure

Dec 27, 2012

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.

View 3 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

Application Express :: Passing Variable To Javascript

Aug 28, 2013

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.

View 0 Replies View Related

PL/SQL :: Passing Char Variable In Cursors - Not Taking?

Apr 9, 2013

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]....

View 4 Replies View Related

Client Tools :: Passing Variable And Displaying Select Data In TOAD?

Apr 22, 2010

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]....

View 10 Replies View Related

SQL & PL/SQL :: Passing Cursor To Function

May 6, 2010

I am strugling hard to pass a cursor to my function as in parameter.here is my code

Function migrate_audits (sys_audit_ids SYS_REFCURSOR ) return number;
Function migrate_audits (sys_audit_ids in sys_refcursor ) return number
is
v_return number;
v_sys_audit_id number;
begin
LOOP FETCH sys_audit_ids INTO v_sys_audit_id;
[code]....

passing cursor to a function is not possible in oracle? what other option I have to pass collection to the function ?

View 14 Replies View Related

SQL & PL/SQL :: Passing Multiple Values To IN Clause In Function?

Jun 22, 2010

I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :

where account_code = P_CODE.

Eg. The value of :P_CODE is 'CS'.

I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.

I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0

View 3 Replies View Related

SQL & PL/SQL :: Passing Hierarchy As Input To Function That Returns XML?

Aug 3, 2012

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.

AAAA
----AAA
----BBB
----CCC
-------CC1
-------CC2
-------CC3

[code]....

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

View 12 Replies View Related

SQL & PL/SQL :: Function Invoked By Passing Date Parameter

Oct 18, 2011

get rid of the below error

CREATE OR REPLACE FUNCTION fn (
p_salesrep_id IN jtf_rs_salesreps.salesrep_id%TYPE,
p_org_id IN jtf_rs_salesreps.org_id%TYPE,
p_cnf_date IN emcint_ord_headers_all.creation_date%TYPE

[Code]....

Invoking Functions

select fn(-3,293,'1/1/1952'), resource_id from jtf_rs_salesreps
where rownum < 5
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

View 8 Replies View Related

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

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 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

To_date - Using Just Dd / Mm And Not Year

Jun 18, 2008

i am sure it may be wrong because it doesn't mention year however i have been told to get a list of all students who enrolled in MAY. and i have tried doing

select * from table where enrolDate > to_date('01-05','DD-MM') and enrolDate < to_date('31-05','DD-MM') and it doesnt work (says no rows selected when in fact there are some rows!

i can do it fine when using YEAR ie '01-05-2000','DD-MM-YYYY' another problem.

I am trying to do this:

Select to_char(to_date(01012008,DD-MM- YYYY),DAY) from dual;

and it is giving me the error however if i do this:

Select to_char(to_date(16012008,DD-MM-YYYY),DAY) from dual;

View 3 Replies View Related

SQL & PL/SQL :: Why To_Date Not Returning Whole Year

Jan 16, 2011

I run following query.

select to_date(sysdate,'DD/MM/YYYY') Dte FROM DUAL

Result is

Dte
--------
01/17/0011

why it is not returning '01-17-2011'?

View 3 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

PL/SQL :: To_date In Insert Statements In Cursor

Jan 2, 2013

I was wondering if I could use a to_date in an insert statement in a cursor.Would the following piece of code be valid?

create table main_table
( eff_date date,
version varchar2 (5),
user1 varchar2(10));
[code]....

View 11 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

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function

Feb 4, 2013

I have the following C code:

class Factorial {
  public:
  int getVal (int a);
};
[code]....

/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.

Just in case, listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

Passing Ref Cursor

Oct 14, 2010

We receive hand punches (clock data) every day. Normally a person badges in(hand punch) which creates a row in the clock_tran_processed table. The information from that hand punch is the employee id (emp_id) the date hand punch occurred and a work_summary id (wrks_id). At the end of the day, the employee badges out (hand punch out) and another entry in the clock_tran_processed table is created. The new row will have the emp_id (employee name), date the hand punch occurred and the same work summary id from the morning.

Normally hand punches should occur in pairs. One in, one out... or one in, out for lunch, in for lunch, out for day. I am seeing intervals of three and five. Meaning the employee clocked in twice and out once, or in once and out twice. This shouldn't happen.

I am writing a report that will show number of clocks per for all employees that have three(3) or five(5) clock entries.I wrote a ref cursor that gives all the employees that have a count of 3 or 5 and the employee id (emp_id). I need to pass that employee id to another query that will then get me the dates of the clocks.Here is the ref cursor thus far (I am printing those with a count of 3 and 5).

CODEDECLARE

TYPE ClockTran_Refcur IS REF CURSOR;
ClockTran_cur ClockTran_Refcur;
NumClock_num number :=0;
NumClock_name clock_tran_processed.emp_id%TYPE;
[code]...

How do I pass that variable to the get the clktranpro_time's? I am using "Easy Oracle PL/SQL Programming" but I am not seeing this type of example(pgs 140-148).

View 5 Replies View Related

SQL & PL/SQL :: Passing Value From One Cursor To Other One

May 24, 2011

I would like to know whether a value obtained from one cursor can be passed to the other cursor as a parameter and by passing it i want to retrieve a list of records and print only the records obtained from the second cursor where the value is passed.

View 3 Replies View Related

SQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function

Jun 10, 2010

What is advantage of Deterministic function over normal function?

What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?

View 4 Replies View Related

Passing Parameter To In Statement?

Oct 9, 2008

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)
{

[Code]....

oCmd.Parameters.Add(":InStatement", OracleDbType.Varchar2).Value = InStatement;

View 2 Replies View Related







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