How can I do something like that...It has to be possivel... Not this way, but somehow..
CREATE OR REPLACE PROCEDURE access_reg_column (reg_user user%ROWTYPE)
IS
v_column_name VARCHAR (50);
BEGIN
V_column_name := 'name';
DBMS_OUTPUT.put_line (reg_user.'v_column_name'); ????
END;
Script for test:
CREATE TABLE TEST(empno VARCHAR2(4), empname VARCHAR2(50), empstd NUMBER(2))
insert into test values(0001,'A',2);
insert into test values(0002,'B',5);
insert into test values(0003,'C',2);
insert into test values(0004,'D',7);
insert into test values(0005,'E',9);
Now I want to get empno for the particular employees based upon subscript and I have written below
DECLARE
CURSOR cur_rec
IS
SELECT *
FROM TEST;
TYPE cur_type IS TABLE OF cur_rec%ROWTYPE;
v_cur_rec cur_type;
BEGIN
OPEN cur_rec;
FETCH cur_rec
BULK COLLECT INTO v_cur_rec;
[code]....
how to access individial item froma table of rowtype data.
CM: Added [code] tags, please do so yourself next time, see the orafaq forum.
using as template this table:
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.
What is functional diffrence between type, %rowtype and record.
View 3 Replies View Relatedi have created one varray whose elements are of record type. Now how can i access those record type elements?
structure of table t1:
select * from t1;
IDDESCRIPTION
1a
2b
3c
select * from t2;
ID1DESCRIPTION1
4aa
5bb
1cc
declare
type r1 is record (id t1.id%type);
type r2 is record (id1 t2.id1%type);
type r3 is record (id1 r1, id2 r2);
type var1 is varray(20) of r3;
[code].......
Is there any way (bean / oracle code) to convert report to image Programmatically.
View 2 Replies View Relatedcan we use set_block_property to program to switch multiple relationships. for example
if true then use relationshipA
else relationshipB
end if;
Here is an example
(
pnLeadID p_lead.lead_id%TYPE ,
)
IS
v_lead_rec p_lead%ROWTYPE;
v_lead_rec := dml_p_lead.get_rec(pnLeadID);
-- %TYPE is used to declare a field with the same type as
-- that of a specified table's column:
-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:
so pnleadid it gets the collumn in p_lead table
so for v_lead_rec is saying what ever is in the collumn pnleadid =v_lead_rec
This error is returned when executing a Function with the RETURN X%ROWTYPE is used.Here is the code.
CREATE OR REPLACE function FE_GET_addr (
p_PIDM IN addr.addr_PIDM%TYPE,
p_atyp1 IN VARCHAR2,
p_atyp2 IN VARCHAR2,
p_atyp3 IN VARCHAR2,
p_atyp4 IN VARCHAR2
[code]....
I am passing the column name for a rowtype dynamically and it is considered as a string by oracle and not giving the value of rowtype.column.i have ANONY 1 where i am passing value to attribute1 and ANONY 2 i am passing value as NULL.
IF l_comp_column IS NOT NULL AND l_mand_column = 'Y'
THEN
dbms_output.put_line(l_comp_column ||' IS NOT NULL');
l_flag := 'Y';
ELSIF l_comp_column IS NOT NULL AND l_mand_column = 'N'
THEN
l_flag := 'N';
END IF;
[code]....
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
how to assign values from a "rule table" to a rowtype-variable. The ruletable contains values for different columns in different tables.Now i need to assign those given values for given columns out of that rule table to the equivalent column in a rowtype-variable.
CREATE TABLE TBRULES
(
TABLE VARCHAR2(50 BYTE)
, COLUMN VARCHAR2(50 BYTE)
, VALUE VARCHAR2(200 BYTE)
);
[code].....
When I invoke SOAP Web-Service using APEX_WEB_SERVICE.MAKE_REQUEST, then I'm able to get response from web-service. However all German character are replaced by JUNK data. However data is coming fine when I test web-services using SOAP UI.
I tried to invoke web-service using UTL_HTTP. However when I use UTL_HTTP, then I'm getting following error.
ORA-24247: network access denied by access control list
<li> Why German characters are replaced by Junk data while invoking web-service from APEX, while it's working fine from SOAP UI
<li> When I can access web-service successfully using APEX_WEB_SERVICE, then why it's throwing ORA-24247 error when I call using UTL_HTTP?
DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
APEX: 4.0.2.00.07
Web-Server: EPG
SELECT * FROM NLS_DATABASE_PARAMETERS;
[code]...
I am having oracle database version 11.2.0.3.0 standard edition, where one of my users requirement is that he wanted to send mails from oracle database but he is getting below error
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "HF_REPORTING.SEND_MAIL", line 12
ORA-06512: at line 18
is it possible to send mails from oracle database in STANDARD Edition?
While Running the proc,it's raising the below error.
Error report:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "LISTER_SHIFT.SOLN_GENERIC_SENDMAIL", line 33
ORA-06512: at line 1
[code]....
Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
My procedure proc_ex is in when_validate_item trigger
I have one Multi Record data block in my form with values in its items
I need to Populate multi record block values to one Record Group using
add_group_row,
add_group_column,
set_group_char_cell to populate values to record group
Let us suppose my multi record data block looks like
item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80
how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies
I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the
LOAD DATA
APPEND
INTO TABLE post1.thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(file_type POSITION(1:5)CHAR,
[code]....
A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).
THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002
What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?
I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name
Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
now i have to select below 2 rows how can write select qurie for this?
1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
I have the following case to solve:
Example Table:
Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00
I create a data block on a table when I am inserting new record only one record have been saved. Last record no longer exist.
View 4 Replies View RelatedI have one multi record block in that i have one LOV..If i choose lov in first record that won't be display in second record lov...
View 4 Replies View RelatedUsing 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
What is the minimum access level ( or grant) needed be able to alter user defined (non sys or system) Stored Procedure in residing in SYS schema.
Example - User A need to alter SP sys.myStoredProc.
I have Oracle 11g installed in Windows 7 in a machine, now i have Linux distribution (OS) installed in the same machine(Dual boot). I have created database and have database tables available on the oracle database in Windows 7.
Is it possible to connect to the database from Linux distribution using shell script and access the data, execute a stored procedure etc?
How can we access Alert log using sql, without sys privilege in oracle 11g ?
View 17 Replies View RelatedSuccessfull installation of my Oracle Client was done.When i opened Enterprise Manage Console and i try to login to a database, the following error occurs
" The procedure entry point kpufhndl() could not be located in the dynamic link library OCI.dll "
What can be done to resovle this error?
I have a plan to migrate all MS Access 97 based applications into Oracle as backend database and .Net as Front end. I have done migration of MS Access data to Oracle using export, linked tables, etc. from time to time.
View 10 Replies View RelatedI am having one requirement that i have to restrict the access of oracle database from clients.
suppose i am having 10 users and they have all information related login like username, password, database servers details etc but then also they should not get login into my database, i have to restrict some of them like i have to provide access to 5 users only out of that 10 users.