SQL & PL/SQL :: Declare Returned Value - Getting Error
Jan 28, 2013
while trying to execute this
declare ret_val number;
begin
exec p_buildinfo('252657020001', to_date('20120820','YYYYMMDD'),to_date('20120928','YYYYMMDD'),ret_val, 0);
DBMS_OUTPUT.PUT_LINE('Value Returned Is : '||ret_val) ;
end;
I getting the below error
ORA-06550: line 3, column 10:
PLS-00103: Encountered the symbol "P_BUILDINFO" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "P_BUILDINFO" to continue.
the procedure structure is
CREATE OR REPLACE
PROCEDURE p_buildsinfo ( var_p_cod CHAR := NULL,
var_p_dat_from DATE := NULL,
var_p_dat_to DATE := NULL,
po_var_l_nxt_seq IN OUT NUMBER,
var_p_consol_flg NUMBER default 0
)
View 14 Replies
ADVERTISEMENT
Dec 19, 2011
I am getting an error when trying to run this DECLARE statement and I'm not sure why. I'm fairly new to PL/SQL and have looked up how to format a DECLARE statement and this seems right to me.
DECLARE
v_eventstart date;
v_maxhbdate date;
v_6monthPOS date;
[code]...
The error is stating "encountered the symbol "end-of-file" when expecting one of the following... " and is refering to the beginning of this statement.
View 6 Replies
View Related
Apr 5, 2011
I'm working with sqlldr and i try to insert data from a csv file to a CTL file. One field of my table contains 5 characters but one row has 6 characters in this field, so it's rejected by oracle. (Logical, you can't insert 6 chars in a 5 chars field)
an error is visibly returned, so i wondered how you could catch the value of this error?is it a code? a message?
I'd like to add to my script a condition so that the end of the script would continue even if this error code is returned for that CTL execution.
View 11 Replies
View Related
Dec 22, 2010
the same query i put below but in oracle.
I'm trying to declare and set parameters in oracle
I'm trying to do something similar as the follow SQL Server Sentence:
------------------------------------------------------
DECLARE
@Year SMALLINT,
@City varchar(255)
SET @Year = 2010
SET @City = ''NY','NJ''
[Code]....
View 3 Replies
View Related
Feb 20, 2012
Can we have any exception in DECLARE block (shown are just for example)?
Declare
exception occured
begin
code
end;
declare
variables;
begin
declare
variables;
begin
code;
end;
end;
View 6 Replies
View Related
May 22, 2013
When I am running script in oracle 10g ,getting error message as "PLS-00201: identifier 'UTL_SMTP' must be declared".
how to declare identifier 'UTL_SMTP' in oracle 10g or how to give execute access to current user
View 6 Replies
View Related
Jul 19, 2012
Can I declear a variable in PLSQL as the row type for a table, who's name is unknown during compile time, but will be determined when the PLSQL is runnning. The code is like following:
Procedure operTable( tableName IN VARCHAR2)
IS
TYPE ty_Row IS tableName%ROWTYPE
v_Row ty_Row;
CURSOR v_quey_cur
[code]...
View 5 Replies
View Related
Dec 21, 2012
how do you declare a variable in a store procedure
View 11 Replies
View Related
Jan 25, 2011
Can we able to declare a user define exception name as per defined exception name?Ex: we have predefined exception called :"NO_DATA_FOUND "
Now in my PL/SQL block can i able to declare "NO_DATA_FOUND" like
Begin
no_data_found exception;
//statements
raise no_data_found;
end
View 6 Replies
View Related
Feb 24, 2013
i just what to understand the differnce between the variable declared under the Begin and variable declared under Declared. to understand this i tried a sample plsql procedure as show below
create or replace
PACKAGE BODY TEST_DEV AS
PROCEDURE TESTING(I_NAME VARCHAR2,
I_AGE NUMBER) AS
INTEGE binary_integer := 100;
[Code] .......
the Line L_nums1 NUMBER := 100/0; is throwing error(PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:)
if i remove the line L_nums1 NUMBER := 100/0; and just declare the variable out side the begin , and using inside the same variable inside the begin works fine
Why question is why we are not able to decalre, initialize and use the same Number variable insde the begin
View 4 Replies
View Related
Aug 5, 2010
I have a problem :
In package head I declare a variable , which reference 'level' type , but I don't know how to declare the variable , because I don't know what type of 'level'.
View 9 Replies
View Related
Oct 6, 2008
I want to call a value from one from to another form in d2k forms 4.5. I think this can be done only by declaring a global variable. I don't know how to declare global variable and also calling the same in other forms.
View 14 Replies
View Related
Feb 26, 2013
I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).
View 11 Replies
View Related
Mar 11, 2010
I have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...
So I declared all these in the header
Like this
CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS
type table_name_i is varray(100) of VARCHAR2(30);
c_add constant number := 1;
c_del constant number := 2;
c_select_frm_tmp constant number := 1;
[code]......
Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?
View 9 Replies
View Related
Mar 9, 2010
I am getting some odd results from a Database Insert Function. The function receives an Array of elements. The elements are a defined structure (containing around 21 data items). I need to insert these records into an Oracle table.For each element of the Array the function reads the structure into a local c structure of the same type.
I then go through this local structure and get a copy of the data into local variables declared in the EXEC SQL BEGIN DECLARE SECTION of the function. I then use the local vars to do the insert, using null INDICATOR variable to handle those variables that could be empty. The local variables look like this....
EXEC SQL BEGIN DECLARE SECTION;
int dbServiceTypeId;
int dbRecordType;
char dbRbmCustRef[MAX_CUST_REF_LEN];
int dbServiceSeq;
[code]...
BUT.. when I uncomment the dbCOS variable (even though I don't populate it or try to include it in the insert) I get the following in the table (The RATE value goes missing completely and the multiplier is wrong)...
ID SERVICE CODE R/T RATE MULTI
41325-SCODE-1084 1 542139762
11326-SCODE-1086 1 542139762
11326-SCODE-1086 2 542139762
21327-SCODE-1087 1 542139762
21327-SCODE-1087 2 542139762
21327-SCODE-1087 3 542139762
21327-SCODE-1087 3 542139762
However, a printf statement just before the insert based that returns the variables shows the following...
Service Type ID: '4'
Record Type ID: '1'
Service Cust Ref: '1325-SCODE-1084'
Service Unit Rate: '1200.00'
Indeed, for this record the Multiplier doesn't even get populated. The other odd thing is if I recomment the dbCOS but remove the dbOraDateFmt variable definitions, it corrupts the data again, though different fields. I can't understand why individual local variables are behaving this way. Is this a problem with the way variables are declared in this section?
View 3 Replies
View Related
Oct 24, 2011
i want if user is not type the @ in email text box than from show the message "check your email address" but i cant do that
declare
ok_flag number :=0;
begin
ok_flag :=instr('emailaddress','@');
if
ok_flag < 1 then
message('invalid email');
else
message('thanks');
end if;
end;
View 8 Replies
View Related
Aug 11, 2011
I need to create a function where in data from 5 rows is clubbed into one row. Like this I have around 425 rows which should be clubbed to 85 rows. Requirement is similar to pivot but not exactly like a pivot as different columns need to be taken from those 5 rows. This is for reporting purpose in order to get data in the desired report format.
SQL mentioned below works fine. It does return data.When below code is used as a normal procedure with OUT parameter as Index by table of Record type code works fine. It returns data. Functionality is met. But when used as a pipeline function, it returns no data.
Below code gets compiled but returns nothing. I didn't find anything on Google or any website for same.
CREATE TYPE r_report_mth_rec_obj AS OBJECT (
acct_num VARCHAR2 (20),
acct_name VARCHAR2 (80),
fund_group VARCHAR2 (80),
fund_type VARCHAR2 (80),
share_class_code VARCHAR2 (10),
share_class_description VARCHAR2 (20),
curr_code VARCHAR2 (10),
[code]...
View 16 Replies
View Related
Dec 16, 2011
declare
type osd_refone is ref cursor;
osd_ref osd_refone;
l_status number;
[code]......
abc_reports in this pack "ab_report" it is the function it having the ref cursor as out parameter . when am executing the above anonymous block am getting the below error,so how can i print the out ref cursor data in my block.
ERROR at line 8:
ORA-06550: line 8, column 12:
PLS-00221: 'OSD_REF' is not a procedure or is undefined
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
View 6 Replies
View Related
Apr 4, 2013
I have a business need to have a db function that would construct and return a (temporary) CLOB value.
here is its sample code:
create or replace package PKG_TEST_CLOB
as
function FN_TEST_TEMP_CLOB
return clob;
[code]....
when this function is invoked from a SQL Statement...
***
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB from dual;
***
... the NOCACHE_LOBS counter in V$TEMPORARY_LOBS for my session is incremented by 1
when this function is invoked via a PL/SQL block...
***
declare
l_clob clob;
begin
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB into l_clob from dual;
end;
/
declare
[code]....
... the counter doesn't budge
In real life, this function will be used by a Reporting Tool (cognos) via SQL. I tested it, and it seems that it is allocating a new temp lob segment with every invocation.
View 0 Replies
View Related
Aug 2, 2013
If running the following query:
select s.effective_date
from SECURITYDBO.DERIVATIVES s
where s.security_alias=100014320 and s.src_intfc_inst=0;
There are no rows returned.However, if I am running the other query:
select MAX(s.effective_date)
from SECURITYDBO.DERIVATIVES s
where s.security_alias=100014320 and s.src_intfc_inst=0;
There is a BLANK row returned.
View 11 Replies
View Related
Sep 26, 2010
difference in the values that are returned?
select count(*) from aaa;
COUNT(*)
----------
1000001
select num_rows from dba_tables where table_name = 'AAA';
NUM_ROWS
----------
994202
View 5 Replies
View Related
Sep 17, 2010
DECLARE
l_query VARCHAR2(4000);
TYPE cursor_type IS REF CURSOR;
[Code].....
How can I get the total number of rows returned by the query?
I want to be able to check omething like c1.ROWS = 0
View 4 Replies
View Related
Feb 21, 2013
I have employees under a supervisor defines as below:
select LOGIN_USER_ID from APPWMS.VIEW_EMP_LATEST_INFO where SPVSR_LOGIN_USER_ID='erbrand' and EMP_STAT_CODE='ACTIVE'
Now I need to determine if all above employees are clocked in , clocked out or not clocked between yesterday and today using following:
select to_char(CLOCK_IN_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockIn,to_char(CLOCK_OUT_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockOut ,LOGIN_USER_ID,--CLOCK_IN_DATE,CLOCK_OUT_DATE, CLOCK_OUT_DATE-CLOCK_IN_DATE,trunc(sysdate) , trunc(sysdate-1),
case when CLOCK_OUT_DATE is null then
'Not clocked out'
else
[code]....
The first SQL gives me 66 rows while second gives me 40 rows. For 26 people , no rows are returned which means these people donot have a clock in record between two timestamps.
How can we modify query to show those 26 people as 'Not clocked In'
View 7 Replies
View Related
Oct 9, 2012
I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.
how to proceed further and the ways how can I write this requirement.
View 1 Replies
View Related
Apr 5, 2013
How Can I delete the returned two rows?
1 select s.reg_no,s.course_code,
2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag
3 from attendance a ,src s
4 where a.semester_code=1
5 and a.semester_year=2013
6 and s.semester_code=1
[code]....
View 6 Replies
View Related
Jul 3, 2011
is it possible to use the records returned by a query as column names in a select query.
select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%')
from v$database;
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
View 3 Replies
View Related
Oct 15, 2012
For one of my row its returning as below lpad('abcdef', 4 , 'Z') returning abcd
but instead of this if no of characters is greater than 4 i want the actual data without lpad should be returned.
View 7 Replies
View Related
Feb 24, 2011
I am oracle clinical 4.6. In Oracle clinical when i am trying to submit "Print DCF" it should run and generate the report in PDF format. But I am getting the following error message.
MSG-00010: 328900: SRW.RUN_REPORT failed.
Updating failure text.
REP-1825: Before Report trigger returned FALSE.
View 11 Replies
View Related
Nov 10, 2010
I need to work on this requirement.
There are FOUR tables ( T1 , T11 & T2, T22) ALL store order information.
One of four conditions are possible for each Supply Reorder Number:
•Both table queries return no records
oPopulate all the output fields with nulls
•T1 returns a record, but T2 returns no records
oPopulate output fields with values from the join of T1 and T11.
•T1 returns no records, but T2 returns one record
oPopulate output fields with values from the join of T2 and T22.
•T1 returns a record, and T2 returns a record
oIf the latest order is in T1, then populate output fields with values from the join of T1 and T11.
oIf order dates are equal from both join results, then populate output fields with values from the join of T1 and T11 .
oIf the latest order is in T2, then populate output fields with values from the join of T2 and T22.
How do we filter the dataset based on result of table join ?
View 1 Replies
View Related
May 25, 2011
I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?
The code I have is as follows:
CREATE OR REPLACE PACKAGE qi_test IS
TYPE typ_qi_data IS RECORD(
iQIFlag NUMBER(1),
iIPFlag NUMBER(1),
iRiskIndicator NUMBER(1),
iDenominator NUMBER(8),
iNumerator NUMBER(8)
[code]........
I want to be able to execute the above function using dynamic SQL. Initially tried:
DECLARE
f2_data qi_test.typ_qi_data_tab;
BEGIN
EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;'
USING OUT f2_data;
[code]......
...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:
f2_data := qi_test.get_f2_data;
...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.
View 10 Replies
View Related
