XML DB :: Variable Datatypes In Select Of Data From XML To Oracle
Jan 30, 2013
I'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:
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.
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
I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:
SELECT Location, LEVEL,
FROM S_ORG_EXT
where Location = 'North America' and LEVEL ='Software' OR location = 'North America' and Active = 'N'
in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.
this is what I have tried: SELECT Location, LEVEL, Active = 'N' --I want to change this in the to N or Y so I can get different results.
FROM S_ORG_EXT
where Location = 'North America' and LEVEL ='Software' OR location = 'North America' and Active = 'N'
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below.
Insert Into Test1 values(10,'Thomas','Manager','LA'); Insert Into Test1 values(11,'George','Chairman','LA');
declare MTest1Test1%RowType; str1VarChar2(1000):='Name'; MValue1Varchar2(100); begin Select * Into MTest1 from Test1 where ID=10; str1:='Select MTest1.'||str1||' from dual'; execute immediate str1 into mvalue1; dbms_output.put_line('mvalue1 '||mvalue1); end; /
I am getting the error:
ORA-00904: "MTEST1"."NAME": invalid identifier ORA-06512: at line 9
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below. sort it out?
Create table Test1(ID Number(10), Name varchar2(100), Description varchar2(1000), Site varchar2(100));
[Code]...
I am getting the error:
ORA-00904: "MTEST1"."NAME": invalid identifier
ORA-06512: at line 9
My objective is to get the name 'Thomas' in the variable - MValue1. I don't want to use the statement MValue1:=MTest1.Name because, moving forward, I want to have the column name to be dynamic, taken from an array of column names.
I need to achieve it by giving only one select query to get entire record and fetch the column values one by one, by substituting the column name from an array of column names, instead of writing that many lines of code / querying that may times to the database.
I'm trying to return the number of records in my link table that contains the excursion_id I pass in by counting them. It doesn't seem to like the select count(*) into my output variable.
create or replace PROCEDURE BOOK_PASSENGER( EXCURSION_ID IN excursion_booking.excursion_id%TYPE, PASSENGER_ID IN excursion_booking.passenger_id%TYPE, NUMBER_BOOKED OUT NUMBER) AS BEGIN INSERT INTO EXCURSION_BOOKING VALUES(EXCURSION_ID, PASSENGER_ID, NULL); SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID; END BOOK_PASSENGER;
the moment my 11g database is connecting to a php web front end. this following procedure is the one I'm having trouble with.
CREATE OR REPLACE PROCEDURE "BSISSONS"."CREATE_EXCURSION" ( min_places IN excursion.min_places%TYPE, max_places IN excursion.max_places%TYPE, additional_charge IN excursion.additional_charge%TYPE,
[code]...
I can select into an output variable to return the value of the primary key of the newly inserted row back into the webpage, but i need to be able to 'select into' a temp variable to insert this value into another table on the same procedure. I get complie errors when i try to 'DECLARE' a variable after the 'AS' keyword
I have a problem that i have hard coded the username.tablename in each select statement of all forms of my application. Now i want to use a dynamic variable in place of username in each select statement throughout the application. The example is:
select * from scott.emp and i want to write it as: select * from variable.emp
But at compilation of the form the compiler should know the above variable name.
I have tried to use following select statement but it does not work.
select user into :global.username from user_users
I think perhaps my problem would be solved with Dynamic SQL Statement but i have no experience by using this statement.
Yesterday only I have Installed Oracle 11G & created DataBase - JafferDB And from Oracle SQL Developer, I have created a connection called - JafferCon and SID also given..and the role is - SYSDBA And I excute the below statement
insert into MyTable1 Values ('AAA1', 'BBB1', 'CCC1') insert into MyTable1 Values ('AAA2', 'BBB2', 'CCC2')
Then I checked by Select statement, it has shown the values....No Problem.... But, as a test, I deleted the connection and created a new connection tio the same DataBase with different name and when I checked by Select Statement....., it has not shown the values....?
I am using oracle 10g i have a table on my computer that i made for a friend when i load it on their computer the select statements say no data found if i use select * from table name all the data will show
if a column name select * from table name where duty_date = '05-JAN-11'no data found
I have a column in a database that contains both numerical and char data. I would like to be able to do two different things (two different queries)
1. divide the numerical data in the column by 10 and leave the char data alone (just return it)
2. detect the numerical data in the column and treat is as a different value so I can run averages & counts on it while disregarding the char data
I'm not at all sure how to do number 2. I thought a CASE statement would work for number 1, but then I realized CASE doesn't like different datatypes:
select case when '1234' = 'checked' then 'checked' when '1234' = 'gen.nograde' then 'gen.nograde' when '1234' = null then null else '1234'/10 end as "GRADE" from dual
On DB 11.2. I am dynamically building a DB after update trigger that compares old/new column values. I wanted to include a nvl statement in my if conditions as some field values may be blank.
Since I have many different datatypes (varchar,date,number,blob,clob), is there a single nvl comparison I can use that will work against all of these? I was hoping not to have to check the datatype and change my nvl comparison depending on what type it is. i.e.
DECLARE cursor c1 is select /*+ INDEX(NI04.NI_DPR_DEALER IX_DPR_DEALER) */ DEALER_CODE from ni_dpr_dealer where not exists (select null from dealer_processed where ni_dpr_deale r.DEALER_CODE = dealer_processed.DEALER_CODE); type cur_type is REF CURSOR; [code].......
show errors;
I am getting errors as below
Entering Dealers count ::13236 entering conditions Dealer name at 1 => HOL202 DECLARE * ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got - ORA-06512: at line 27
I have writen PL/SQL packages for data loging through pipe lined function for better peformance.The below packages has been compiled sucessfully but during the run time it shows an error like "ORA-00932: inconsistent datatypes: expected - got -".
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load AS PROCEDURE sp_final_load_mkt_hub; FUNCTION fnc_pipe_tot_lvl_idx_mon_hub (pi_input_cur IN SYS_REFCURSOR) RETURN tot_lvl_idx_mon_tt PIPELINED;
[code]...
SHOW ERRORS
Error:
ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got - ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 33 ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 55 ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 92 ORA-06512: at line 1
types scripts:
create or replace type tot_lvl_idx_mon_ot as object (SSIA_INDEX_ID VARCHAR2(60), start_date date, CURRENCY VARCHAR2(10), LEVEL1 NUMBER(31,11), TYPE VARCHAR2(31) ,
I am working on a data migration project where I am extracting data from Oracle8i database and writing it to a Text file using File_UTL utility. In one extract procedure, I need to write a LONG datatype with some VARCHAR2 datatype in extract file. Procedure compiled fine but I am getting the "ORA-00932: inconsistent datatypes" error while executing the final procedure to write data to a file.
below the code snipplet. I am trying t write Account_Primarykey, Account_name and it's AText (LONG datatype) together in a extract file:
create or replace procedure FORCE_W_ACCOUNT as Begin declare file_handel UTL_FILE.FILE_TYPE;
Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.
I am fetching more than one column data from a table repetedly(By different query string each time) through ref cursor using concatenate function successfully. But I can see if one concatenate function is getting missed due to human intervention, it's causing failure for entaire process and returning oracle exception as "Failed - ORA-00932: inconsistent datatypes: expected - got -".
My table looks like this -> Dp_value 124325 2434 3536 3536
Code is -> (case when CL.DECIMALPLACES = 0 and CL.FIELDTYPE = 'D' and cl.DATATYPE = 'N' then trim(dp."Value")*1000 else dp."Value" end) as dp_value,
What I am trying to do -> From another table cl I want to say if decimal place is 0, fieldtype is decimal (D/C) and datatype is number (N/T)then multiply the number with 1000.
If not then leave it as it is.
When I run my code I get the error: ORA-00932 : inconsistent datatypes: expected NUMBER got CHAR for else dp."Value" end)
I 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:
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 );
I 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.
