SQL & PL/SQL :: Find Packaged Procedures Names For Particular String
Nov 7, 2011I want to search for some specific string in packaged procedures source code, and want to list the name of those procedures along with package names.
View 17 Replies
ADVERTISEMENT
SQL & PL/SQL :: Find All Column Names In All 20 Tables That Have Same Names?
Jul 7, 2010I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
create table t1 (
col1 varchar2(10),
col2 varchar2(10));
create table t2 (
col1 varchar2(10),
col3 varchar2(10));
create table t3 (
col1 varchar2(10));
.NET Stored Procedures :: How To Send Execute String To Oracle Database
Oct 31, 2013I would like to send a raw command to the Oracle server. from .NET (System.Data.OracleClien), but I got invalid statement (ORA-00900) exception
.Excecute string:string SQL = "EXECUTE SP_THREADFILE_INSERT('" + Guid.NewGuid() + "','" + filename + "',utl_raw.cast_to_raw('" + content + "'))";
SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String
Mar 14, 2013I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
SQL & PL/SQL :: Find All Partition Names - Illegal Use Of LONG Datatype
Mar 5, 2012I need to find all the partition names below or equal given value.
select
partition_name
from
user_tab_partitions
where
table_name = 'RB' and
HIGH_VALUE <= 1234
Above Query is giving error "ORA-00997: illegal use of LONG datatype"
Any other alternative ? (I know its because of LONG Datatype of HIGH_VALUE ) ...
SQL & PL/SQL :: Logic To Find Table Names And Data In Oracle?
Feb 1, 2013I have a schema DEF and I have a column_name CREATE_DATE.
I wanted to write a procedure which will give me list of tables whose CREATE_DATE data is prior to year 2009.
SQL & PL/SQL :: Find Names Of All Stored Proc Invoked On Execution Of Main Api?
Oct 1, 2013I have a main procedure in oracle which invokes many procedures inside it. These internal procedures also calls functions and procedures inside it.This continues to many levels.
For ex:
Proc A
call c
call d
end............
[code]...
This loop goes on and on . I want to find the names of all procedures invoked at run time when main api is executed. Is it possible to find all of them using toad ? Is there any tool for doing this ?
Forms :: How To Find Table Names For The Fields In A Horizontal Form Of ASCP
Jun 25, 2013I have a requirement of pulling the data from a field in horizontal form of Advanced Supply Chain Planning responsibility(ASCP). When I go into help-> Diagnostics, it shows the block name of the form as 'Horizontal Plan' and field name as 'Pivot Table'. Not pretty sure on how to pull the data source into horizontal form with the info I have.
View 3 Replies View RelatedSQL & PL/SQL :: Find A Match Within String And Compare?
Aug 2, 2012Below is a column 'ADDR' with the data (single column)
ADDR
--------
/shared/Folder_1 :^BIAdministrator:^BIAuthor:^BIConsumer:BISystemUser:OracleSystemUser:System:weblogic: :F
[Code]....
SQL & PL/SQL :: How To Manipulate String (Find And Replace)
Jun 29, 2012I am required to manipulate a string in this way.For example ABCDEF)* to $ABCDEF). So I should find * and delete it and instead put $ in the beginning of string.
View 9 Replies View RelatedSQL & PL/SQL :: How To Find Greatest Values In A String
Jul 22, 2011I am trying to update the greatest value in a column from a string of other column.
Ex: f the value is shown 10M+16M+25M-DG, then populate 25 only
so for that I had written query as follows:
update ANCHOR set IEL_STRAND_SIZE= greatest(
substr
(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','),
0,
length(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','))-1))
The output is given as 10,16,25,but not as 25.so how could i write it?Do I need to implement procedure or arrays for it.
SQL & PL/SQL :: Find Occurrences Of Some String In DB Objects?
Jul 11, 2012How can i identify all the occurences of raise_application_error(-20XXX, '<the message>'); within all database objects, and replace them with other text?
View 4 Replies View RelatedSQL & PL/SQL :: Find A Negative Number In String?
Apr 24, 2012I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
SQL & PL/SQL :: Way To Find Out Ending Digits In A String?
Dec 5, 2012Is there a way to find out the ending digits in a string?
Examples of input and outputs:
'ABC123' -> 123
'ABC' -> NULL
'123ABC' -> NULL
'123ABC456' -> 456
'123ABC456QP98' -> 98
I have figured a way to do it by doing Reverse and re-reversing using: reverse(regexp_substr(reverse(p_string), '[ [:digit:]]*'))
But I'm sure there is a way to do it backwards without using reverse function, which I am not able to properly put in the syntax.
SQL & PL/SQL :: How To Find Out Lowercase Char In A String
May 16, 2008How to find out lowercase char in a String in a SQL query
for example
"ORAclE" here c and l are lowercase so how can i find out this condition... is there any build in function in oracle?
SQL & PL/SQL :: How To Find Any Character Is Repeating In A String
Sep 8, 2011How to find whether any character in a string is repeating or not
Eg: '123LH563' should return 'YES' , as 3 is repating there
'1234567' sould return 'NO' as there is no character which is repeating
PL/SQL :: Find And Replace Value In Delimited String
Sep 4, 2013I have a requirement, where i need to find and replace values in delimited string. For example, the string is
"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".
The 4th column gives month and year. I need to replace it with previous month name. For example:
"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".
I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?
SQL & PL/SQL :: How To Find Extended ASCII Characters From A String
Sep 19, 2013I have a table like mentioned below
create table test1( test_no number, test_description varchar2(100));
insert into test1 values (1,'ABC£¥');
insert into test1 values (2,'BCD£¥');
Now I am selecting from the above table and the expected rest it should have shown is as shown below
TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABC£¥
2 BCD£¥
But instead of showing the extended ascii characters, it is showing some different characters as shown below
select * from test1;
TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABCLY
2 BCDLY
I have an requirement where i need to fetch the exact ascii characters for £ and ¥ instead of L and Y respectively.
SQL & PL/SQL :: Compile Specific Packaged Procedure
Jul 20, 2012I want to recompile a single procedure that is part of a package, without re-compiling other procedure/functions present in that package, is it possible?
View 1 Replies View RelatedSQL & PL/SQL :: How To Find Nearest / Closest String For A Column Data
Jun 1, 2011Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.
Miss
Mississ
Mississipp
I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)
If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2)
Is this possible ? Here is the code for table creation and data.
create table names (name varchar2(20));
insert into names values('Miss');
insert into names values('Mississ');
insert into names values('Mississipp');
commit;
SQL & PL/SQL :: Calling Packaged Procedure With Refcursor As Out Parameter Over DBlink?
Oct 7, 2011I have a requirement in which I have to call a packaged procedure created in one database DB1 to other database DB2 through a DBLink. The packaged procedure to be called has refcursor as OUT parameter.
When I run the procedure in DB1 , it works fine:
Declare
v_ref_data SYS_REFCURSOR;
a1 Number;
b1 varchar2(100);
c1 varchar2(100);
Begin
apps.XXCU_DB1_PKG.get_DB1('101062','9138', v_ref_data);
FETCH v_ref_data into a1,b1,c1;
dbms_output.put_line(a1|| ' '|| b1|| ' '|| c1);
End;
When I run the packaged procedure from DB2 using DBlink, it gives error:
Declare
v_ref_data SYS_REFCURSOR;
a1 Number;
b1 varchar2(100);
c1 varchar2(100);
Begin
apps.XXCU_DB1_PKG.get_DB1@dblink('101062','9138', v_ref_data);
FETCH v_ref_data into a1,b1,c1;
dbms_output.put_line(a1|| ' '|| b1|| ' '|| c1);
[code]...
I read somewhere on other forums that refcursors can not be passed through the DB links, is it true??
PL/SQL :: Valid Objects Need To Be Compiled While Running Packaged Procedure
Aug 8, 2012I have a requirement to create a packaged proc which lists down a set of database objects and its statuses whenever the status of objects is changed as valid/invalid in user_objects. Also, those valid objects need to be compiled while running the packaged proc.
View 5 Replies View RelatedSQL & PL/SQL :: Multiple Values Of RULES Tables To Find Records Containing A String Format
Sep 4, 2013Insert into PROFILE
(INSTANCE, PROFILENAME, USER_DATA, UPDATE_DATE)
Values
(138, 'Test A', 'SRC!-1,ARCHIVE_OPT!-1,DATE_FIELD!155,DATE_RULE!1,DISTINCT!1', TO_DATE('01/20/2005 13:35:33', 'MM/DD/YYYY HH24:MI:SS'));
/
Insert into RULES
(ID, NAME)
Values
(155, 'DATE_TEST');
I want a code something of this sort:
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
Application Express :: Packaged Application Is Missing Tables
Dec 5, 2012We installed the packaged application P-Track in a schema called cpd in Apex version 4.2.0.00.27. When running it, the error below occurs. Essentially it appears that some of the tables are missing as the package does not compile due to tables not being found. We had no trouble running other sample packaged apps. I would really like to look at P-Track. Any problem with installing P-Track?
The following error has occurred while executing the error handling callback:
ORA-04063: package body "CPD.EBA_PROJ_FW" has errors ORA-06508: PL/SQL: could not find program unit being called: "CPD.EBA_PROJ_FW" ORA-04063: package body "CPD.EBA_PROJ_STAT_UI" has errors ORA-06508: PL/SQL: could not find program unit being called: "CPD.EBA_PROJ_STAT_UI"
Technical Info (only visible for developers)
is_internal_error: true
apex_error_code: APEX.AUTHORIZATION.UNHANDLED_ERROR
ora_sqlcode: -4063
ora_sqlerrm: ORA-04063: package body "CPD.EBA_PROJ_STAT_UI" has errors ORA-06508: PL/SQL: could not find program unit being called: "CPD.EBA_PROJ_STAT_UI"
component.type: APEX_APPLICATION_AUTHORIZATION
[Code]....
Application Express :: 4.2 - Packaged Application Installation Error
Nov 9, 2012URL....The upgade from 4.0 to 4.2 went fine, existing applications work, Builder works fine. But none of the packaged applications install, all of them fail with that error. Is there some post-upgrade step to complete to get this to work?
Also, it doesn't appear like we can select the application id when installing these applications! Our environment has a convention, of sorts, for application ids. Is there a way to over-ride the app id?
ORA-02085 - Database Link String Connects To String
Jun 19, 2012I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
How To Use Member Procedures
Dec 13, 2009create type employee is object(
name varchar2(30),
member procedure change_name(new_name varchar2)
)
[Code]....
Now how to use the change_name procedure to change the name of the employees in the table?
PL/SQL :: Drop All Procedures At One Go?
Aug 29, 2012I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.
ORACLE 11G
I am using sql developer 3.2 version tool
Monitoring Runtime Of Procedures
May 23, 2013runtime monitoring procedures....need a sample script
View 1 Replies View RelatedExecuting WHOLE Package (of Procedures) At Once?
Aug 30, 2007I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??
Its not possible but i could call each procedure from ONE procedure