SQL & PL/SQL :: How To Determine The Data Type Of A Variable
Sep 15, 2010Code to determine the data type of a variable in oracle 10g.
View 5 Replies
ADVERTISEMENT
PL/SQL :: How To Access Type Object Variable Declared Inside Another Type Object
Mar 29, 2013I 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
SQL & PL/SQL :: How To Execute Type Variable Procedure
Apr 26, 2013I 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]...
PL/SQL :: Get Distinct Value From Array Type Of Variable?
May 14, 2013i 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].....
PL/SQL :: Declare Variable As Row Type For A Table?
Jul 19, 2012Can 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]...
SQL & PL/SQL :: How To Declare A Variable Which Reference Level Type
Aug 5, 2010I 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'.
SQL & PL/SQL :: Want To Define Dynamic Type For A Variable In Oracle 10g
Jul 17, 2013I 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]....
Forms :: How To Parse Paramlist Type Variable
Mar 22, 2010how to parse paramlist type variable to get names of the fields and their values in the paramlist?
View 1 Replies View RelatedPL/SQL Program Units - Global Variable Specification / Type
Jul 31, 2012This 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.
PL/SQL :: Dynamically Assign Values Of Any Column Of Row Type Variable
Nov 12, 2012need 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?
SQL & PL/SQL :: Inserting Result Set Of Query Into Corresponding SQL Table Type Variable?
Aug 28, 2012I 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]...
Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type
Oct 17, 2011Is there some functions to convert the long type field data to varchar2 type?
View 2 Replies View RelatedVariable Usage In Type Of Table Declaration Statement And Execute Immediate Statement
Aug 10, 2011HOW 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
Application Express :: Object Type As Application Level Variable
Jan 2, 2013My 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.
Store Data In CLOD Data Type - How To Create A Unique Index
May 20, 2013We have been recommended to store data in CLOD data type.
Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.
We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?
SQL & PL/SQL :: Extracting Data From LONG RAW Data Type Column
Oct 7, 2011I have come one requirement where i need to extract data from a LONG RAW data type column.
View 7 Replies View RelatedInserting Table Data Into A Variable
Sep 3, 2012I am trying to insert a column into a variable from a trigger.
Here is the code that i have:
CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
FOR EACH ROW
DECLARE
v_key varchar2(10);
BEGIN
select ID into v_key from buyer;
insert into message_log_table (table_name, message_comments)
values
('Buyer', 'Buyer '||v_key||' has been updated');
end;
/
When I run the above I get the following compiler error:
[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier
Since ID is defined in my BUYER table I do not understand what the error means.
Here is my create table statement:
CREATE TABLE BUYER
(
ID VARCHAR(50) NOT NULL PRIMARY KEY,
FNAME VARCHAR(50) NOT NULL,
LNAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP_CODE NUMBER(5) NOT NULL
);
SQL & PL/SQL :: Getting Data From Record Type
Jul 19, 2010I have one requirement. We have a Package which consists of 2 Stored Procedures which has a RecordType output Parameter.
FUNCTION GET_NFE_INFO(O_status_code IN OUT NUMBER,
O_error_message IN OUT VARCHAR2,
O_message IN OUT "OBJ_FM_NFE_DOCHDR_REC",
I_fiscal_doc_id IN FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE)
return BOOLEAN is
I am working on a one Stored Procedure which would display the output from the above function as,
set serveroutput on size 9000;
DECLARE
L_stg_status FM_FISCAL_DOC_HEADER.STATUS%TYPE := 'NFE_P';
L_fiscal_doc_id FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE := 12325;
L_dummy VARCHAR2(1) := NULL;
L_status_code NUMBER(1) := 0;
L_error_message VARCHAR2(255) := NULL;
L_message "OBJ_FM_NFE_DOCHDR_REC" := NULL;
[Code]..
Here for each and every data value i am Printing it using DBMS_OUTPUT, L_message record has more than 100 columns. Is there a easy way of handling it instead of using DBMS_OUTPUT ?
SQL & PL/SQL :: What Should Be The Data Type For The Column
May 9, 2012I have to create a table which should store data at Week level. The table have the following columns
Product id, Loc id, Business group id, FISCAL WEEK , Revenue,
Fiscal week column will have data as '2011-W01', '2011-W47' etc.
What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.
XML DB :: Variable Datatypes In Select Of Data From XML To Oracle
Jan 30, 2013I'm new to XML to Oracle data integration. I do have clob column that has the XML data and trying to bring it into the Oracle relational tables, as a part of it we are developing PL/SQL procedure that calls this CLOB column, I want to know if we can variablize the data types ( Just like tablename. column name%type) in the select of the XML data:
select v.instrid, v.endtoendid, v.txid, v.cd, v.ccy,
replace(v.intrbksttlmamt,'.',',') as intrbksttlmamt, v.chrgbr, v.bic1, v.nm1, v.adrline11, v.adrline12, v.ctry1, v.iban1,
v.bic2, v.bic3, v.nm2, v.adrline21, v.adrline22, v.ctry2, v.iban2, v.cdtrref, v.addtlrmtinf
from the_data w,
xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:pacs.008.001.01'),
[code]....
I do define the data types after every column name , the problem is incase there is change in datatype I do need to manually edit the procedure to change the datatype is there a way I can variablize them something like tablename.columname%type.
User Defined Data Type
Jun 19, 2012I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express. The create type and select statements execute fine and select confirms the ssn_t is a type. The create table statement fails with the invalid data type error.
Here is an example.
CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11));
SELECT object_name, object_type
FROM user_objects WHERE object_type = 'TYPE';
CREATE TABLE Z (A CHAR(4), B SSN_T);
SQL & PL/SQL :: How To Get Date From The Timestamp Data Type
Oct 13, 2011How can i get just date from the timestamp data type.
Suppose i have a column timestamp with has data like "2011-05-16 16:19:22.579764-07" when i select from table i just want the date like 2011-05-16.
SQL & PL/SQL :: How To Insert Varying Type Of Data
Jul 14, 2010I want to insert varying type of data from one table to another like i want to insert name from one table and salary from another,sysdate too.How should i consider doing it?
View 3 Replies View RelatedServer Administration :: RAW Data-type?
Apr 3, 2013RAW datatype in oracle,any problems we will face using this datatype,Reason is we have column which stores session id in RAW datatype using sys_guid, drawbacks in using this datatype.
View 5 Replies View RelatedSQL & PL/SQL :: ORA-00902 Invalid Data Type
Nov 2, 2012I have created the following type
create or replace type type_sh_fld_rec
(
s_count count,
s_name varchar2(200),
e_date date);
/
create or replace type t_table_s_field as table of type_sh_fld_rec;
/
I have used these types in package pkg_shippers PROCEDURE process_shipments(userid);
when I run the following sql
select * from table(cast(DEV.PKG_SHIPPERS.process_shipments(324) AS T_TABLE_S_FIELD));
I get the following error.
ORA-00902 Invalid datatype.
I have tried to create the types and table from the sql prompt instead of the package spec, still no luck
Note: The sql statement works fine if I run it as schema owner but not as user who has all the privileges.
PL/SQL :: Retrieve XML Data In Clob Type?
Dec 5, 2012I have a table created as below
create table tab123( x clob);
Inserted a row as below
insert into t values ('"<ProcessBatchRequest xmlns:inp1="http://services.abc.com/" ServiceVersionNumber="" xmlns="http://services.abc.com/GlobalBatchServiceV1.0">
<inp1:BatchDetail>
<inp1:ApplicationID>test.123</inp1:ApplicationID>
<inp1:ApplicationBatchID>test.123</inp1:ApplicationBatchID>
<inp1:MessageTimeStamp>2012-11-28T11:05:32.000-05:00</inp1:MessageTimeStamp>
[code]....
Now i want to retrieve the value present under a given xpath.
XML Type Column Data XML Query?
Mar 16, 2013I have extracted in the following XML document some Mpeg7 visual descriptors from an image and I saved it in an XMLType column. I would like to use XMLQuery to extract the data from the Value node. how write such a query. I could not get the proper Xpath to any node.
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Mpeg7 xmlns="http://www.mpeg7.org/2001/MPEG-7_Schema" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance">
<DescriptionUnit xsi:type="DescriptorCollectionType">
[code]...
Need Clob Variable To Write Large Volume Of Data?
Oct 6, 2010I am facing a problem with utl_http.write_text in my pl/sql application. My requirement is to write data of size>32k. So I used a clob variable in write_text. But still it is showing numeric or value error when the data size is above 8k.
I have read that chunked transfer encoding will work. But I couldn't find out how this is done.
SQL & PL/SQL :: Create Table - Tablename With Data From Subselect (variable)
Sep 2, 2013I wish to create a table with week-number as suffix _36 or _30
CREATE TABLE TEST1_$WEEKNUMBER AS (SELECT * FROM TEST1);
$WEEKNUMBER is this statement:
SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') from DUAL
CREATE TABLE doesn't accept subselect
How to do that with SQL?
Client Tools :: Inserting Data With Substitution Variable?
May 24, 2012I want to insert the value with '&'. But this is treated as substitution variable by oracle.So how can it is possible to do like this
INSERT INTO DEPT (DEPTNO, LOC, DNAME)
VALUES (50, '&NEW YORK', 'SALES');
