SQL & PL/SQL :: Same Source Code Giving Different Results
Sep 23, 2013
While I was debugging the code of already existing application (Oracle apps fnd_global package), I copied a package to make changes in it.
It was strange that with Original package fnd_global I am getting below error when I call it over database link:
"ORA-20001: Oracle error -20001: ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction
has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls."
PACKAGE CALLS ARE AS FOLLOWS:
Database 1 (Oracle Apps LSH module)
CUSTOM procedure to call CDR_PUB_API_GVA
has CDR_PUB_API_GVA
INITIALIZATION BLOCK OF CDR_PUB_API_GVA CALLS Fnd_Global.apps_initialize (when copied to fnd_global1, then it's call don't throw any error)
Database 2 (External application)
has dblink to Database1
calls custom procedure over dblink
While the copied package is not throwing any error (I copied fnd_global to fnd_globa1).
View 8 Replies
ADVERTISEMENT
May 30, 2012
I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
View 5 Replies
View Related
Jun 20, 2011
nlsb> select file_name,bytes from dba_data_files where tablespace_name='ARIAN_DATA';
FILE_NAME BYTES
------------------------------------------------------------ ----------
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf27lcn_.dbf 5368709120
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1txnm_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tr6v_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tonc_.dbf 8589934592
[code].....
dbms_metadata is giving me code that will create more datafiles than the original, and furthermore won't run: firstly, because two files are named with a null string, and secondly because it includes RESIZE commands which won't work because they nominate OMF file names. Or is dbms_metadata unreliable?
View 1 Replies
View Related
Oct 15, 2009
I would like to backup all source code (packages, procedures, functions, etc) every day automatically. Is there a built-in feature of Oracle to do this, I've been going through the Toad IDE and its getting to be too time consuming.
I assume I could use the export command, but not entirely sure what to specify.
I would like to backup a few tables along all code and set something in place (trigger maybe?) to send me an email when a modification is made to the production code environment.
View 2 Replies
View Related
Sep 4, 2013
i have a existing table called table_1 to did some changes to it, but i need to do a source search to find where all in the code that we reference this code to ensure that there is not a variable declaration that sets this to a specific number. how do i do a source search. i alter a existing column (overbook_max) to number(2) tonumber (3)
View 8 Replies
View Related
Feb 3, 2008
I want to wrap the source code in oracle, i am able to do it using wrapping utility (through DOS prompt command i.e wrap iname= inputfilename.sql oname=outputfilename.plb).
Other option is using DBMS_DDL package as follows
*********************************************
DECLARE
l_source DBMS_SQL.VARCHAR2A;
l_wrap DBMS_SQL.VARCHAR2A;
[Code]....
This above will replaces the original function source code with wrapped code, but here they are concatinating the code manually.
My requirement is i will get the source code (text) from user_source view in a cursor and line by line code will get in a loop. I am sending that output to above function dynamically , but it raising this error.
ORA-24230: input to DBMS_DDL.WRAP is not a legal PL/SQL unit.
View 2 Replies
View Related
Jun 7, 2012
How to write code in unicode/utf8 and used non-english variable written in different cases?The compiler is supposed to be case-insensitive. I just wonder how it handles case insensitivity for unicode or ut8 encoding.
View 7 Replies
View Related
Jul 1, 2010
i build a form to get the source code of an object from the database and export the result into a text file but i face a problem I cannot get more than 780 line of code because i have a package with 24080 line and i have to put all this line in the text file
I attached the form
I know that the problem with the size
CM: removed unnecessary colour coding - it doesn't make it easier to read you know.
View 7 Replies
View Related
Sep 24, 2010
I got html source code inserted into the table as CLOB (or BLOB). And I would like to search a some word from that.When I find a some value I can write this one into the column.It would be easy if this code is xml but isnt.
View 12 Replies
View Related
Nov 9, 2009
I would like to create a trigger that will execute a stored procedure when a package/function/procedure is compiled. I tried creating an update trigger on user_objects, but it statues aI cannot create that trigger tyoe on views.
View 1 Replies
View Related
Feb 12, 2009
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
View 8 Replies
View Related
Feb 13, 2012
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
View 7 Replies
View Related
Mar 30, 2013
I am looking for a pl sql package which can give the following grants:
1. select_catalog_role
2 select any table
we want to run this package multiple names and each time we will give username as input during runtime.
View 1 Replies
View Related
Apr 19, 2010
How do you specify the value in START WITH dynamically during runtime. For example consider the bewlo scenario:
CREATE TABLE ISCT
(
ITEM_NO VARCHAR2(15 CHAR) NOT NULL,
ITEM_TYPE VARCHAR2(3 CHAR) NOT NULL,
ITEM_TYPE_SCO VARCHAR2(3 CHAR) NOT NULL,
[Code].....
Now if you create a view:
create or replace view test_v
(ITEM_NO, ITEM_TYPE, ITEM_NO_SCO,ITEM_TYPE_SCO)
as
SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO
FROM
[Code] .......
Then run the below query:
select * from test_v where item_no_sco = '00245905'
--
the output is got within a second. but the real scenario is that both table icont and isct have millions of rows. In that case even this small query of the view takes 10minutes as there a FULL TABLE JOIN.
How do I give the START WITH in the view to make it faster i.e. even with millions of rows the output of this comes in seconds:
SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO
FROM
(
SELECT NVL(C.ITEM_NO_CHILD, I.ITEM_NO) ITEM_NO, NVL(C.ITEM_TYPE_CHILD,
I.ITEM_TYPE) ITEM_TYPE, I.ITEM_TYPE_SCO, I.ITEM_NO_SCO
[Code] .......
MC: Remove code tags on INSERT, CM take care to not exceed 80 characters when you add coce tags: reformat yourself or do not do it.
View 15 Replies
View Related
Apr 1, 2011
i am trying this query
SELECT
CASE
WHEN TYPE = 'COUNTRY' THEN (SELECT CODE FROM LIST_T)
END mi
FROM LIST_T
and in that subquery i am returning two or more than records so it is giving me the error like ORA-01427: single-row subquery returns more than one row
how do i solve this error and i must to fetch two or more records from the subquery if it possible in any other
View 11 Replies
View Related
Feb 3, 2012
SQL> select count(*) from emp where empno not in ( select mgr from emp );
COUNT(*)
----------
0
SQL> select count(*) from emp T1
2 where not exists ( select null from emp T2 where t2.mgr = t1.empno );
COUNT(*)
----------
9
I fired above query and I totally confused that why both are giving different output.
View 6 Replies
View Related
Apr 29, 2011
I have a question. If i insert some values to a table and then write a create statement. But if the create statement gives me error (eg: table name already exist). And without commiting if i come out the session will the insert commit?
View 9 Replies
View Related
Sep 14, 2012
I have written a trigger as below:
CREATE OR REPLACE TRIGGER LOGONTRIG
AFTER LOGON ON DATABASE
DECLARE
v_temp NUMBER;
V_bypass_trg_flag VARCHAR2(1);
[code],...
This trigger got successfully compiled. and when am trying to connect to the db with other user its giving me error "ora-06512 : at dbms_session".When i grant dba privillage to that user am successfully able to login to db.
View 2 Replies
View Related
Jun 12, 2013
I want to change the color of push button & give a label to it in form 6i..
For that I change the iconic property to yes and then change the color of push button but by this the label get disappeared.
View 2 Replies
View Related
Dec 9, 2010
how can i authenticate through check constraints or plsql procedure to update one value in a column for giving password by superuser?
View 2 Replies
View Related
Jul 28, 2011
i'm not able to open layout in the custom form.automatically it will be closing and giving this error. Oracle Forms Designer has encountered a problem and needs to close.
View 5 Replies
View Related
Aug 18, 2010
i hav create a report and use order by in the query of the report when i run my query on sql prompt it gives output in a sorted manner but when i am using it in report builder then it is not giving the output in sorted order.
View 2 Replies
View Related
Mar 13, 2012
I am attempting to make a job on an active database and I keep getting the error "An error occurred checking if the script file directory exists. Correct the error and try again. Error message: Agent Unreachable"
I can't find any information about the error on the logs and I don't knwo what to do about the error. I really need the script the job makes but how to get it.
View 10 Replies
View Related
Sep 5, 2011
How we can resolve :
Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory
View 15 Replies
View Related
Sep 30, 2013
We just upgraded to APEX 4.2.2.00.11 and we are getting ORA-24247: network access denied by access control list (ACL) errors form apex_mail. This had been working correctly prior to the upgrade.
View 4 Replies
View Related
Sep 20, 2012
My VNC Hangs after completing all the steps in dbca while installing 11.2.0.3.0 after giving finish button to install database.
Completed All software installation.
Red Hat version: Red Hat Enterprise Linux Server release 6.2 (Santiago)
Linux xxxxxxx.xxxxx.net 2.6.32-220.el6.x86_64 #1 SMP Wed Dec 7 10:41:06 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
View 3 Replies
View Related
Mar 28, 2007
I am using Oracle 10g Express Edition on Fedora core 5 32+ bit os. The problem is when I use the SQL Plus command line to make SQL statements I can not get the previously typed command back at the prompt when I use the up and down arrow keys on my keyboard. This is quite easy when I am using a shell, but here with this Oracle command line interface it is not working at all. Here is the example as what actually is happening whe I press the up or down arrow keys.
SQL> drop table mailorders;
Table dropped.
SQL> ^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A
View 2 Replies
View Related
Jun 3, 2011
I am running the following delete query and it has been running for over 2hrs:
delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
[Code]..
Here is the explan plain result:
explain plan for delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM,
[Code]....
PLAN_TABLE_OUTPUT
Plan hash value: 611392786
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2604G| 260T| | 9018K (91)| 30:03:37 |
| 1 | DELETE | ACCOUNT_FACT | | | | | |
|* 2 | HASH JOIN | | 2604G| 260T| 369M|
[Code].....
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID="$kkqu_col_1")
I have all constraints disabled. How do I make this delete finish faster? We're trying to remove duplicates from this table using the criteria giving in the statement.
View 16 Replies
View Related
Jan 14, 2011
I am using below command to import a schema using network link. Command is :
impdp system directory = IMP_DIR schemas = XYZ network_link = PQR remap_schema = XYZ:XYZ exclude=view: "= 'XYZ.VW_ACCEPTDETAILS'"
This command is giving below error
LRM-00116: syntax error at 'view:' following '='
When I have tried Like in place of '=' sign i.e. EXCLUDE = VIEW:"LIKE '%VW_ACCEPTDETAILS%'" , it gives me below error:
UDI-00014: invalid value for parameter, 'exclude'
View 4 Replies
View Related
Jul 9, 2007
I am new to oracle... i have one view called c1.. I want to know table of this view..
View 4 Replies
View Related
