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]...
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'.
how do you declare a variable in a store procedure
View 11 Replies View RelatedI 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 RelatedI 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 RelatedI 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?
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;
I am unable to insert the result set of query into corresponding SQL Table type variable where as same functionality can be accomplished by PL/SQL table type variable. Can't we access the same by using SQL type variable?
Ex:
Step 1:
SQL Object type , Table type Objects creation :
drop type sql_emp_tab_type ;
drop type sql_emp_type ;
create or replace type sql_emp_type as object
(
empno number,
ename varchar2(20),
[code]...
Step 2: Accessing the table type object from PL/SQL block
SQL>
SQL> declare
2
3 tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 begin
6
7 tab_type_var.extend(10);
[code]...
Step 3: Instead of SQL Table type , if we define the corresponding PL/SQL table type variable
SQL> declare
2
3 --tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 type pl_sql_emp_type is record
6 (
7 empno number,
[code]...
Write a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:
If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month".
If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month".
If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".
For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1
AS OBJECT
(
SYSTEM_IDENTIFER VARCHAR2(50),
PROCESS_TYPE VARCHAR2(50),
abc typeObj2
)
/
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
I passed the values like this in proc execution.
T_T_A_I_V := typeObj3('asdasd',NULL,NULL),
typeObj3('String654',NULL,NULL),
typeObj3('abcdef',NULL,NULL));
T_T_A_I_I := typeObj2('CampusCode',T_T_A_I_V),
typeObj2('PlanNumber',T_T_A_I_V);
What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct
I have created the following procedure. Since I am using this first time I don't know how to execute this.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package
IS
TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;
PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots );
END;
/
[Code]...
Code to determine the data type of a variable in oracle 10g.
View 5 Replies View Relatedi have written one procedure which is giving an error PL/SQL:ORA-00902: invalid data type, how to get the distinct value.
there is one table xyz and this table having so many attributes and file_data one of the attribute which is having CLOB data type. I have to find out distinct value in each record of file_data.
suppose for example
TABLE XYZ
col1 col2 file_data
A B <CLOB VALUE>------------------------ this CLOB VALUE containing duplicate record
V X <CLOB VALUE>------------------------- this CLOB VALUE also containing duplicate record
create or replace procedure test_dealer_upload
IS
t_out_file UTL_FILE.file_type;
t_buffer VARCHAR2(32767);
[Code].....
I m trying to give table name as parameter to this function. It is compiling properly.But when an anonymous block is created to call this table value, it has to be fetched into a variable of Rowtype of this inputted table. So I am not able to create any ROWTYPE variable for this table dynamically.
//Function
create or replace function instant_tabula(tabula in varchar) return sys_refcursor
as
cur sys_refcursor;
str varchar2(20000);
begin
str := 'select * from ' || tabula;
open cur for str;
return cur;
end;
[code]....
how to parse paramlist type variable to get names of the fields and their values in the paramlist?
View 1 Replies View RelatedThis assignment allows you to pick your own subject in which you Code a Package Header that contains the following:
Global Variable Specification
Type
Four (4) Procedure Specifications
Two (2) Function Specifications
Submit your code in a series of Notepad or Wordpad documents. Include the code for the assignment and the output that is realized from running the code. Note: You are responsible for creating the database on which the project will run. This includes the responsibility for loading the database with sufficient data.Grading for this assignment will be based on answer quality, logic/organization of the project to include the compilation, and execution of the project code.
need to dynamically assign value of each column of a row type variable:The example is like that:
Table "Student_list" is as follows:
---------
COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50)
[code]....
Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?
i am trying to run a script in which a command tries to create or replace a type.
i get this error:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --create a test user:
SQL>
SQL> create user tuser identified by tuser
[Code]....
Table created.
SQL>
SQL> --then change the type:
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
create or replace type t1 as object (obj_type number(15))
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table:
SQL>
SQL> drop type t1 FORCE
2 /
Type dropped.
SQL>
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
SQL>
SQL>
SQL> --if i re-create it - my table is still corrupted:
SQL>
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
Type created.
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
--if i re-create it - my table is still corrupted:
create or replace type t1 as object (obj_type number(15))
/
desc dpntnt_table
[/code]
1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?
2. I understand that this type is already assigned to some object, but i can't seem to find out which one.
how do i find out which object is depending on the type i want to create or replace?
I have created the below types and oracle objects.
create or replace type T_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
--
create or replace type T_EMA_NP_RANGE_LNPTICKET_REC as object
(
ticket number
)
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following type is created in the Package specification
type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
I have the following two procedures
Procedure getResponse(p_call_request_id IN number, p_resp_rec IN t_setdel_resp_rec,
p_range_ticket_tab IN t_range_icket_tab, p_endof_event IN varchar)
PROCEDURE ProcessResponse(p_call_request_id IN number, p_resp_rec IN t_resp_rec,
p_ticket_tab IN t_ticket_tab, p_endof_event IN varchar2)
The get Response procedure is a wrapper procedure exposed to Java to pass values. The Process Response procedure is a main procedure where all logics and business rules are handled.
The Problem is:
How can I pass the values from get Response procedure to Process Response procedure. So that rules and validations are applied. Please note the p_ticket_tab may have many ticket numbers corresponding to p_call_request_id.
Values E.g. :
p_call_request_id = 1
p_resp_rec (1234, 'Error found')
p_range_ticket_tab (1,2,3,4,5)
p_endof_event = 'Y'
When i tried to drop a type using below command , i received errors
DROP TYPE JAM_ACAS_MSG_TYPE
ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents
I have been creating lot many threads around the same problem, however i thought i knew but realized I do not know or else do not know how to. I have created object type with an attribute READINGVALUE NUMBER(21,6)...How can i use type attribute on this object while declaring variable.....can we use type attribute on NESTED TABLES, similar to the db tables?
example
CREATE TYPE READING AS OBJECT(READINGVALUE NUMBER(21,6));
CREATE TABLE INTERVALREADINGS OF TYPE READING;
how to convert a record type to a table type?
View 4 Replies View RelatedMy question is, if I define an object type (create type ... as object ...) is it possible to hold a variable of this type as the session level? For example, if I am creating an order using multiple web pages, it would be much easier (and intuitive) to create and manipulate an order object than to hold the data in generic collections.
If that is not possible, is there some way to make a generic Apex collection appear more application specific? By this I mean some way to map the columns C001, C002, ... to more intuitive names such as CUST_NO and ORDER_DATE. I had considered possibly creating a view of the APEX_COLLECTIONS view which would rename the columns but then I would also have to create a bunch of instead of triggers if I wanted to update it as well.
i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)
View 1 Replies View RelatedI 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]......
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]....
I am using temporary table.
PROCEDURE Return_Summary(WX IN dbms_sql.varchar2_table,
WX OUT SYS_REFCURSOR) IS
Begin
FOR i IN 1 .. Pi_ WX.count LOOP
/* I need to put this results in a temp table or table object Can I use temp table for this or do we have any other recommended method. The loop might execute max of 10 times and for each run it might return 100-200 records. */
select WX_NM,
WX_NUM
from TAB A, TAB B, TAB C
where A.KEY1 = B.KEY1
and B.KEY1 = C.KEY1
and C.WX = WX(i);
End Loop;
End;
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;
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
