I am trying to insert In multiple table where one tbl PK is FK of other , I achieved that functionality using Instead of Insert trigger.(PK is handled by another trigger) -- main functionNow On successfully insert i need to captured the PK in Hidden variable to pass it on further for more insert on other table (Pk again being Fk to this table)--- secondary function
View 6 RepliesI have a procedure I need to feed a number into in order to generate some thumbnails.
I'm trying to automatically update the value every time a new row is inserted but I'm having trouble with the code.
DECLARE
varInt NUMBER :='SELECT MAX(IMAGE_ID) FROM IMAGES';
begin
create_blob_thumbnail(varInt);
end;I get the error +'ORA-06502: PL/SQL: numeric or value error: character to number conversion error'+
If I run the query SELECT MAX(IMAGE_ID) FROM IMAGES, it returns the last row ID I created just as it should. The create_blob_thumbnail(varInt) procedure works fine if I manually put in a number.
I have the following tables:
CREATE TABLE test_abc(id NUMBER PRIMARY KEY, col1 NUMBER(10), col2 NUMBER(10), col3 NUMBER(10));
INSERT ALL
INTO test_abc VALUES (1,12345,34567,87654)
INTO test_abc VALUES (2,17345,37567,87754)
INTO test_abc VALUES (3,12745,34767,87674)
INTO test_abc VALUES (4,17045,30567,80754)
INTO test_abc VALUES (5,12740,34067,87604)
SELECT * FROM dual;
CREATE TABLE test_b (id NUMBER, col VARCHAR2(10), coltype_id NUMBER);
What I need to do is to convert the columns col1, clo2, col3 in test_abs into rows in test_b. but I need to do this so that the column name is what determines the value of colytype_id.So in test_b values will look like:
id col coltype
1 12345 1
1 34567 2
1 87654 3
and so on.
I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.
strQuery :='INSERT INTO TAB3
(SELECT t1.c1,t2.c2
FROM TAB1 t1, TAB2 t2
WHERE t1.c1 = t2.c1
AND t1.c3 between 10 and 15 AND)' ....... some other conditions.
EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.
On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.
I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
[code]...
I have 2 tables. Cbxrd and Cbxrdlog. If the Cbxrd table having creation date column. when it inserts the row. if the column is null value. then the entire insert script row will be inserted into Cbxrdlog table sqltext column.
i have attached the trigger script. when i execute the table but i shows error like
"Ora-0756 Quoted String not properly terminated".
"Ora-04098: trigger os_wm_sit_owner.cbx trigger is invalid and failed revalidation.
I'm starting with APEX, I would like to know how to insert the contents of a file. Csv in a table, how to map a file to a table, how to insert file contents. Xls in a table.
View 1 Replies View Relatedi have a table say 'temp' with 4 columns as seq, name, event, id and i have a pg with 3 text fields as name, event, id now ma issue is,if i enter values into those 3 text fields in that page and on a button click (say create), i wanted dose values to get inserted into 'temp' table andregarding , 'seq' column i hav created a sequence for it. so
1. how to insert values into the table by entering values in the page
2. wat shd b done for seq column.. (shd anyth b done from apex pages or wil it automatically get values for each row entered)
I have made Horizontal Wizard Progress List over 3 pages.I also made those 3 pages with some simple text fields, checkboxes and so on.On the last page I did the INSERT Process (session variables P100_Date ect).It worked fine! But now I tried a different approach
On each of the 3 pages I made a "Form on a Table or View" and on each pageI hide some of the form. (the advantage of this situation is, that apex gives you a visaul hint about the null fields and so on).But on the last page I cant do the INSERT with the session variables.(I can do the INSERT without session variable, with static values)...
Getting starting with diving deeper into APEX and reading the developers guide all 800 pages of it! Thing is I'd like to get started with some of the more interesting stuff.
I have created a form page based on the "Form on a Table with Report". The report part looks fine. What I'd like to do now is use an API I've built to change how the insert and update works. So when the submit button is pressed it rather performs an API call to the procedure/function.
1. Should the API be a function or procedure. In my playing around I saw that a procedure might work better or easier to implement
2. How would I go about creating a call for the button to use instead of the current processes?
----------------------------
Apex 4.2
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Linux version 2.6.32-279.19.1.el6.i686
----------------------------
What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.
View 9 Replies View Related i have create service rate card Form for transport Service and i have declare transport component into master lookup table .There is 11 component into master.To Difine rate for a particular transport service i have insert these component name with there rate into another table transport_rate _card.
To do this i have created page with these 11 component name item and there 11 rate item to enter component rate with respective services. Problem is if i create 11 component name text item and 11 text item to enter there conmponent rate but if user add 3 or 4 or 5 more componenet name into master then i'll be need to add extra component name text item and component rate text item.so i need to create form where if user add more component into master then component name item and component rate item should be increase automaticaly.
How i can create form where item added auotmaticaly acording to component which are exist into master table. there is only component name into master table.
i have to build up a form for visitor pass in my company..for that i have to capture visitor's image by web cam. my problem is that how to capture image form web cam to my form for each visitors.
How to integrate my web cam with my oracle 6i form builder.
how to make a form with report and an insert form in the same page, these two forms are related to the same table. Our customer wants a user can add new row to the table in a form and see all of rows created by this user in a report, this report should provide edit link as well. the problem is: whenever I inserted a new row or edit a row or delete a row, and submitted, and return to this page, all of hidden items lost their values, so report is blank, and some display only items also lost their values.
View 6 Replies View RelatedMy scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
I want to capture video using webcam through developer 6i. i m using ora_ffi package. my package specs are:
PACKAGE CAM IS
WM_CAP CONSTANT PLS_INTEGER:= 1024; -- EQUALS TO '&H400S'
WM_CAP_DRIVER_CONNECT CONSTANT INTEGER := WM_CAP + 10;
WM_CAP_DRIVER_DISCONNECT CONSTANT INTEGER := WM_CAP + 11;
WM_CAP_EDIT_COPY CONSTANT INTEGER := WM_CAP + 30;
WM_CAP_GET_STATUS CONSTANT INTEGER := WM_CAP + 54;
WM_CAP_DLG_VIDEOFORMATCONSTANT INTEGER:= WM_CAP + 41;
[code]....
package body are:
PACKAGE BODY CAM IS
/* Declare the library and function handles. */
USER_LIB_HANDLE ORA_FFI.LIBHANDLETYPE;
AVCP_LIB_HANDLE ORA_FFI.LIBHANDLETYPE;
FH_SM1 ORA_FFI.FUNCHANDLETYPE;
FH_SM2 ORA_FFI.FUNCHANDLETYPE;
FH_SM3 ORA_FFI.FUNCHANDLETYPE;
[code]....
now i call preview window through 'when-button-pressed' code is as:
DECLARE
IDEVICE INT := 0;
IHEIGHT INT := GET_ITEM_PROPERTY('BLOCK3.IMAGE',HEIGHT);
IWIDTH INT := GET_ITEM_PROPERTY('BLOCK3.IMAGE',WIDTH);
BRETURN BOOLEAN;RS INT;
S CAM.CAPSTATUS;
IMAGE_ID INT;
[code]....
when i run form, i will get "FRM-40734:internal error:PL/SQL error occurred.". i checked form through debugger,it gives me "PDE-PPU007" error on this function BEGIN CLAUSE execution.
FUNCTION CAPCREATECAPTUREWINDOWA(LPSZWINDOWNAME VARCHAR2,DWSTYLE INTEGER,X INTEGER,Y INTEGER,NWIDTH INTEGER,NHEIGHT INTEGER,HWNDPARENT INTEGER,NID INTEGER) RETURN PLS_INTEGER IS
BEGIN
RETURN (FF_CAPCREATECAPTUREWINDOWA(FH_CPW,LPSZWINDOWNAME,DWSTYLE,X,Y,NWIDTH,NHEIGHT,HWNDPARENT,NID));
END CAPCREATECAPTUREWINDOWA;
I have one requirement,i need to capture the details keystrokes of what user is entering in the form.
View 4 Replies View RelatedIs there a way on oracle forms input field to capture each key stroke. Like if I type "abcd".... After each letter is typed can I check contents of input field.
I tried almost all triggers in the input field however none of them capture every keystroke.
I have recently setup a 2 node Rac on oracle linux 5.4 with oracel 11gR2, the installation went smoothly and all the cluster resources are up and running however the data is not syncing across the nodes, when I create a table it shows up on the other node but when I insert rows into the tables they don't show up on the other node and when I restart the cluster the inserted rows are completely gone even from the node where I inserted them.
View 4 Replies View RelatedI need to store a date that will always be x years before the current date. Is doing something like
select sysdate -x from dual
Going to result in any weird issues down the road? Also, because I haven't explicitly specified the date, when queried, will the value always be sysdate - x (What I want to happen) as opposed to dateRowWasCreated - x?
I am trying to store an image in a table. What is the easiest way to do this?
View 8 Replies View RelatedI have a varchar field in a table. When I store the following string, some undefined character is storing.
String to be stored: Test String
String actually stored: Test ¿ string
Please note that here '' is not Hyphen. It is a special character that msword has. when you copy this and paste in a word document you find the difference.
I have listed the character set for CHAR and NCHAR from NLS_DATABASE_PARAMETERS
NLS_CHARACTERSET - WE8ISO8859P1
NLS_NCHAR_CHARACTERSET - AL16UTF16
I'm writing a procedure that takes a table name as a parameter and I would like to print out the column name with the supporting row entry for each row. I know the logic I'd like to use, but how do you query the metadata to return the column names and store them.
View 3 Replies View Relatedis there any method to store a value in "copy buffer" ?
I mean, for example, if i have defined a global varaiable as
Quote::global.name := 'orafaq';
Then what i need is a command something like:
windows_copy_buffer := :global.name ;
So that later on when i press control+v, then it paste the word "orafaq".
I have a requirement to execute the query only once for all the columns mapped to a particular table. Lets take an example..
table t1 has the following columns mapping.
col1 col2 col3 col4
variable 1 table abc col1_abc ----
variable 2 table abc col2_abc ----
variable 3 table xyz col1_xyz ----
variable 4 table xyz col2_xyz ----
variable 5 table xyz col3_xyz ----
variable 6 table xyz col4_xyz ----
In col4 , we have to update the values of col3 (column) ... it is very easy to update the values by executing the query for each column but my requirement is ... I want to execute the query once for a table ( example select col1_abc,col2_abc from table_abc ... this will return always a single value after adding other conditions in where clause) and why I want to do this? .. because there can be multiple (might be 30 variables values)
from a single table.
I have converted the columns into rows by using listagg function now I have lets a dynamic query ( for the above example).
for i in 1.2
loop
l_query := ' select col1_abc, col2_abc from table_abc';
open c1 for l_query;
end loop;
my problem is : how to hold these values and update the above table?
i basically need to setup a schema for globalization. i need to handle brittish dollar signs, american dollar signs. etc.what is a global character setting i can use to handle almost everything?
my problem right now is that when i store a brittian dollar sign it converts it to a box. i need this preserved so i can select it and use it later.will the WE8ISO8859P1 character setting work? where is a good place to view all of the types of character settings? and what they are for. etc. and also how do you change such things? not for the session, but for a schema wide
here are my current sessions
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
[code]...
My current task is to store XML file from local system ( OS :XP)into server (OS : Linux) specified location with PL/SQL procedure.
Below is the XML file,
<?xml version="1.0" encoding="ISO-8859-1" ?>
<EMPLOYEES>
<ItemGroupData ItemGroupOID="Emp1" ItemGroupRepeatKey="1">
<ItemData ItemOID="EMPNO" Value="11"/>
[code]......
How to complete this task.
Since I need to do something along the lines of inserting an xml document into the database for later to use.
Anyway, the problem I get with the below is
Quote:
Error at line 95
ORA-00984: column not allowed here
Script Terminated on line 95.
It's on the INSERT line
declare
xmlInput clob;
begin
xmlInput :=('<?xml version="1.0" encoding="windows-1256"?>
<XLAAPRPT>
[Code].....
I am working with oracle 10g / 11. I need to operate with CLOB values of some table, unknown for me: I read them from .Net (3.5), and have to save them to files (Windows) to be loaded after this with DBMS_LOB. Lets say that table contains 3 columns (1 - CLOB) and several rows.
I want to store each CLOB value to separate file, and to store it after this into another DB table (another Instance, also). I cannot use dblinks and other techniques like this. The point is - how to avoid dealing with encoding?
I mean, that in the best scenario, I just want to save the CLOB values to the files, with no meter about Database Instance character set, language, and so on. How this can be accomplished? If I use default for .Net StreamReader UTF-8 format, the loading with DBMS_LOB failed.
If this is not possible, what will be the best way to determine the encoding, and to convert Oracle used encoding to .Net one?
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.