SQL Create Table Doesn't Support %TYPE / But PL/SQL Does
Oct 12, 2010
oracle 10G who knows why Oracle SQL doesn't support %TYPE when create a table. But it supports it when we use it in PL/SQL.
SQL> create table wg_1
(account_category JNL_WORK_LIST.ACCOUNT_CATEGORY%TYPE)
;
2 3 (account_category JNL_WORK_LIST.ACCOUNT_CATEGORY%TYPE)
*
ERROR at line 2: ORA-00911: invalid character
SQL> desc JNL_WORK_LIST
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_NO NOT NULL NUMBER(10)
BILL_REF_NO NOT NULL NUMBER(10)
BILL_REF_RESETS NOT NULL NUMBER(3)
JNL_EARNED_THRU_DT DATE
[code]...
we are facing an issue with the dbms scheduler jobs, which is not processing the synonym which is created via dblink from anthoer schema.
Let me explain the situation.
Table in Schema :APP_COMMON DB: APPL -------------------------------------
CREATE TABLE TEST_LOG (A VARCHAR2(10));
INSERT TEST_LOG VALUES ('TESTED');
GRANT SELECT ON TEST_LOG to APP_GEN ;
Table in Schema :APP_GEN DB: APPL -------------------------------------
CREATE SYNONYM TEST_LOG FOR APP_COMMON.TEST_LOG;
SELECT * from TEST_LOG;
-- it returns the value
Table in Schema :APP_GEN DB: REPORT -------------------------------------
This is the different DB (we have 2 DB's, one for report db and one for application DB). here we create the DB link (connected DB menthod) . Since we have the password sync between the databases, we create the DBLINK without user id and password.
CREATE DATABASE LINK "APPL_LINK" USING 'APPL' ;
CREATE SYNONYM TEST_LOG FOR TEST_LOG@APPL_LINK;
SELECT * from TEST_LOG; --it returns the value.
Now in the same DB, we have a scheduler which will run for every min.
Now Scheduler is not selecting this table. Rather not processing the synonym(TEST_LOG). Not able to capture the exception also.
I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...
create type UNIVERSITY as object ( U_RANK number(2), U_SCHOOL varchar2(150), U_COUNTRY varchar2(150), U_SCORE number(3) )
Can we create TABLE type object by using %ROWTYPE in SQL.
I am bale to create PL/SQL table type object .But i am unable to create SQL type
SQL> declare 2 type table_emp is table of scott.emp%rowtype index by binary_integer; 3 employees table_emp; 4 begin 5 select * bulk collect into employees from scott.emp; 6 end; 7 / PL/SQL procedure successfully completed SQL> create or replace type table_emp is table of scott.emp%rowtype index by binary_integer; 2 /
Warning: Type created with compilation errors
SQL> show errors Errors for TYPE DBO.TABLE_EMP: LINE/COL ERROR -------- ---------------------------------------------------------- 1/19 PLS-00355: use of pl/sql table not allowed in this context 0/0 PL/SQL: Compilation unit analysis terminated SQL>
How can I create global table type object with %rowtype
I am working on a webservice call from ORacle.I have a button on my form application called verify.Wheni click on verify button , a pl.sql procedure should be invoked and that procedure will call .net webserive to validate the address , the result from the webserivce will be in xml.I have to extract the xml into some variables and return these varibles to Forms application..I am plannig to use pl/sql table to store the result from web service call.
here are the output values:
Customer_Id varchar2(20), ErrorCode varchar2(30), ErrorDesc varchar2(3000), Fcount number, FErrorCode -- this is array,-- if fcount >1 then these values will be repeted. FErrorDesc -- this is array, FStatusCode -- this is array, FStatusDesc -- this is array, Street varchar2(3000), Street2 varchar2(3000), Suite varchar2(20), City varchar2(20), State varchar2(2), Zip_Code varchar2(10)
create record type and pl/sql table for these.I want to return pl/sql table as a out parameter to the form.
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.
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;
I have a SQL query with a construction like: select a, b from tablea ,select * from (select a as a from tableb) where ....
The issue is that in sqldeveloper I don't get any errors and the select works fine.I use the same select in myEclipse report builder and I get an table or view doesn't exist.
I have query in which having some issues with outer join.When I run the inner query, I am getting 121 rows and when i put outer query and run it, I am getting 69 rows coz the code_combination_id does not exist in the outer query table.But even when I say ( + ) , its not giving 121 rows..
create table t1 (c1 number,c2 number); CREATE OR REPLACE TYPE REC IS RECORD ( R1 T1%ROWTYPE, R2 NUMBER );
I'm trying to create one RECORD type with all the columns from table T1 + one new field R2. But gives me an error.
The point to use T1%ROWTYPE and not to hardcode the columns from T1, is due to if we add a new column to T1, is created when is executed again RECORD definition and not to add the column.
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)
vlanke@tank_db> desc filetable; ID NULL NUMBER(6,0) FILE_CONTENT NULL
I am new to Database field.
- I created a table with 2 columns id (datatype as number) and file_content datatype as BLOB. - My question is under desc filetable; query why does it not show me a data type as BLOB. - create table query does not return any error to me. How do I verify that FILE_CONTENT column has datatype as blob.
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;
When as scott, I run the following command select * from tab, I get the output that has been attached as pdf. This output contain some strange objects like-
exactly what permissions I need to execute the following SQL statement.
SQL> CREATE OR REPLACE TYPE NUM_ARRAY as table of number; 2 / CREATE OR REPLACE TYPE NUM_ARRAY as table of number; * ERROR at line 1: ORA-01031: insufficient privileges
When I grant DBA to the ID which runs the SQL it works fine.
