I am using Oracle 10g. I ran a procedure which is in dynamic sql. Once the procedure gets completed, it comes up with a warning message -
"Warning: unable to execute Dynamic SQL for adj_id: 02773"
Is there anyway to suppress the above message.
Here is good code and tips to run Dynamic Report from Oracle Forms 6i This utility uses tables and Views to design dynamic report. Just download zip file and unzip all files in a filder. Create database procedure attached, comile the form and use this.
View 3 Replies View RelatedOracle 11.2.0.3 - newbie on web services! Currently we have a pl/sql web service taking in 2 static parameters and returning a varchar2 response.
I was asked today if it's possible for a web service to take in a dynamic set of parameters and return a corresponding set of results. It's as if I need to pass the web service a table with two columns and return a table of one column.
For example:
Input Output
1 2 3
3 4 7
5 6 11output being in XML of course. It is possible?
I posted this in OC4J also - no response hence the re-post !
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 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.
I'm trying to build a dynamic sql inside an Oracle procedure, and return it as a SYS_REFCURSOR to ADO.NET using ODP.NET in Oracle 11R2. Below is my table:
CREATE TABLE "TFWDB_ENTIDADE"
(
"CD_ENTIDADE" NUMBER(10,0) NOT NULL,
"DC_ENTIDADE" VARCHAR2(100 BYTE) NOT NULL,
"NM_ENTIDADE" VARCHAR2(255 BYTE) NOT NULL,
"CD_TIPO" NUMBER(5,0) NOT NULL,
"ID_STATUS" CHAR(1 BYTE) DEFAULT ('1') NOT NULL,
CONSTRAINT "PKFWDB_ENTIDADE" PRIMARY KEY ("CD_ENTIDADE"),
CONSTRAINT "CHFWDB_ENTIDADE" CHECK (ID_STATUS IN ('0', '1'))
);
Below is the procedure I build to SELECT it:
create or replace procedure spfwdb_sl_entidade_1(
p_cd_entidade in number,
p_dc_entidade in varchar2,
p_nm_entidade in varchar2,
p_cd_tipo in number,
p_id_status in char,
[code]........
I'm calling this code from C#, and I get an empty Result Set. What am I doing wrong? Can I output the dynamic sys_refcursor that I created? Below is my C# code using ODP.NET:
string pConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASERVER)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORASID)));User Id=user;Password=password;";
OracleConnection oConn = new OracleConnection(pConnectionString);
oConn.Open();
string pNomeProc = "spfwdb_sl_entidade_1";
[code]........
I am trying to run a dynamic select statement form a function and return the result into a variable, everything goes fine but the return is always null!
CREATE TABLE AFESD.MAJOR_ACCOUNT
(
NUMBER0 NUMBER(2) NOT NULL,
SHORT_NAME CHAR(35 BYTE) NOT NULL,
FULL_NAME CHAR(50 BYTE)
)
--Actually any table can do
CREATE OR REPLACE FUNCTION F_GEN_SELECT_INT
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2, S_COLUMNS IN VARCHAR2)
RETURN NUMBER
AS
I_RETURN NUMBER;
S_SQL VARCHAR2(300);
--S_DB_ERROR VARCHAR2(100);
[code]....
B.S. I didnt delete the commented lines to have your review comments.
I have two reports in one page.One is the Interactive and other is Sql report. Based on the selection of the row(using checkbox) in the interactive report, the sql report has to display the selected columns.
Note :The report has to be loaded whenever a row is selected and without submitting a button.
I m trying to give table name as parameter to this function. It is compiling properly.But when an anonymous block is created to call this table value, it has to be fetched into a variable of Rowtype of this inputted table. So I am not able to create any ROWTYPE variable for this table dynamically.
//Function
create or replace function instant_tabula(tabula in varchar) return sys_refcursor
as
cur sys_refcursor;
str varchar2(20000);
begin
str := 'select * from ' || tabula;
open cur for str;
return cur;
end;
[code]....
i'm working in an Oracle 10g database on an IBM AIX server.
I have 3 tables (tables A, B and C).
Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
I also have a Master table which is used to store the data from Tables A, B and C via the insert statement.
I'm trying to create a dynamic SQL insert statement using a shell script to insert data from the columns in Tables A, B and C into my Master table. Master table does contains all columns from Tables A, B and C, although a column name could be spelled differently. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".
I build the dynamic query using a for loop in my shell script (see below).
The problem is that i can't get the correct columns in the Master table in the dynamic SQL for the insert because depending on the table i'm selection from, the columns are different. So how do i get the correct columns in the SQL for the Master table?
Example Shell Script
--Archive_Rates.txt contains: Table A, Table B, Table C (but the next time my process runs, Archive_Rates might contain Table D, Table E and Table F -- each which have different column...but all columns are still in the Master table)
for tbl in `more Archive_Rates.txt`
do
echo 'BEGIN WORK; ' > rc1.sql
echo ' ' >> rc1.sql
echo 'insert into Master' >> rc1.sql
echo '(prod, rate, rate_exp) ' >> rc1.sql
[code].....
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3
ORA-00932: inconsistent datatypes: expected NUMBER got CURSER
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at "TW.PRINT_REF_CURSOR", line 28
ORA-06512: at line 9
Here is my code:
set serveroutput on
exec DBMS_OUTPUT.ENABLE(1000000);
declare
l_cursor sys_refcursor;
begin
[code]....
Is there a solution or bypass?
I have one report requirement like below diagrams, My report having one repeating frame and one dynamic LOGO field. My repeating frame have 2 field say item_no, and item_name. This repeating frame will grow based on the items say min 1 to max 8 items.
My dynamic logo has some min and max movement position, Say in the below diagram my dynamic logo min movement position is line no 6 and max movement position is 9.
Suppose i have only 1 or 2 or 3 or 4 or 5 items then LOGO should fit in the line no 6, if i have 6 items then logo should come in the 7th line, if i have 7 items then logo should come in the 8th line ect.,like that max 8 item will come in the report, if items are 8 then logo should fit in the 9th line number.
----------------
1|*****ITEM1****|
2|**************|
3|**************|
4|**************|
5|**************|
6|*****LOGO*****|-- MIN LOGO POSITION
7|**************|
[code]......
Are oracle view have Dynamic view function?
View 8 Replies View RelatedStatic Tables Creation In oracle & Diff Between Static table ,Dynamic table
View 2 Replies View RelatedI am new to Oracle PlSql. I found PlSql block do not allow us to write DDL as DML queries. "How come DDL are allowed to work in Dynamic SQL using EXECUTE IMMEDIATE".
how come this become possible in EXECUTE IMMEDIATE and not in simple PL/SQL block. What extra feature EXECUTE IMMEDIATE has and why it is designed like this?will be outout of below and why?
Test1 Table
--------------
AB
----
1010
DECLARE
[code]...
I want get dynamic column names and its corresponding values...Consider the below sample code
CREATE TABLE LECTURER1
( ID NUMBER(5,0),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
MAJOR VARCHAR2(30),
CURRENT_CREDITS NUMBER(3,0),
PRIMARY KEY (ID));
[code]....
get the dynamic column values.
How do I add a column (Status) to a select query based on the data returned.
Select Col1, Col2
from
Table 1, Table 2, Table 3
Where
Table 1.Key1 = Table 1.Key1 and
Table 2.Key1 = Table 3.Key1 and
Table 1.Col1 in ('xyz','yty','ttr')
Output Expected:
Col1 Col2 Col3(Status)
xyx 1mst Sucess
yty Null No Data Found
ttr Null No Data Found
The Col1 value yty and ttr does not exist in the database
find the below scripts .
drop table test_arp;
create table test_arp
(
first_name varchar2(1000),
last_name varchar2(1000)
);
[Code]....
when i select the above table ,i will be getting the result as shown
first_name , last_name
CONTROL PANEL
ORACLE SEA
WILLIAMS
RAHUL KUMAR
I need to make a table which will be having the following data and structure
col1 col2 col3
CONTROL ORACLE RAHUL
.i.e , depending on the number of not null values in the first column (first_name), i will be creating a table dynamically ,which is going to have as many columns as the number of values and the naming convention is col1,col2,col...........
I designed the below procedure to CREATE OR REPLACE PROCEDURE ARP
AS
C NUMBER:=1;
BEGIN
FOR I IN (SELECT FIRST_NAME FROM TEST_ARP WHERE FIRST_NAME IS NOT NULL)
LOOP
IF C=1 THEN
[Code]...
But when i execute this code , i get the below error
ORA-00984: column not allowed here
ORA-06512: at "ARP", line 17
ORA-06512: at line 1
I have to create a WHERE clause like :
SELECT * FROM wc_claims WHERE part_nbr in l_part_nbr ;
I have three tables:
table A has - user_id, supplier
table B has - supplier, part_nbr
table C , wc_claims, has part_nbr and 78 other columns.
what i need to do is when user logs in with his user_id, he should see only that info from table C, corresponding to his supplier, and the part_nbr.
SELECT supplier from table_A WHERE user_id = 'abc' ;
supp_abc
SELECT distinct part_nbr from table_B WHERE supplier = 'supp_abc' ;
partA, partB, partC,........partZZ
SELECT * FROM wc_claims WHERE part_nbr in (partA, partB, partC,........partZZ );
I have created a function :
create or replace Function wcl_partnbr ( p_supp IN varchar2 )
RETURN CLOB
IS
[Code].....
so that I can use that in the WHERE clause, because one supplier can have thousands of parts...so it works fine, if there are 200 part numbers for one supplier, but above that.. it doesnt.
I having below table :
EXAM2@orcl> desc search_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROL NOT NULL VARCHAR2(7)
CNM NOT NULL VARCHAR2(30)
FNM NOT NULL VARCHAR2(30)
MNM NOT NULL VARCHAR2(30)
DOB NOT NULL DATE
YEAR NOT NULL NUMBER(4)
EXAM NOT NULL VARCHAR2(1)
MAIN_SUPP NOT NULL VARCHAR2(1)
In a dotnet application user is going to input/select all/any of the above column in where clause.If user has given value for rol column then ref cursor should be :
select * from search_table where rol like <given value>
endif
and/or
If user has given value for cnm column then ref cursor should be :
select * from search_table where rol like <given value> and/or cnm like <given value>
endif
I mean whatever value is being given; it should be part of where clause.For it i am going to :
EXAM2@orcl> CREATE OR REPLACE PACKAGE rollsearch AS
2 TYPE t_cursor IS REF CURSOR;
3 Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
4 c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
5 io_cursor IN OUT t_cursor);
6 END rollsearch;
7 /
EXAM2@orcl> CREATE OR REPLACE PACKAGE BODY rollsearch AS
2 Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
3 c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
4 io_cursor IN OUT t_cursor)
5 IS
6 v_cursor t_cursor;
7 sql varchar2(1000);
8 BEGIN
9
What should i write further; so that i may get ref cursor as whereed clause for given value. So that i may populate my datagrid view for that web form.
I have a stored procedure that updates a series of columns (set at varchar(500) just to ensure there is ample space for whatever data is inserted into it) .Once it has updated though I've wanted to trim the column width down to the maximum size of the column value. I determine the max size of the column length and use that in a pl/sql command to ensure the width isn't smaller than the largest data entry.
When I try to do this I get the following message:
ORA-01441: cannot decrease column length because some value is too big
Now after hunting this down it seems as though you cannot decrease the size of a column once data has been inserted into it.
Any success in reducing the column width of a table that has data in it?
Why do I need to do this? I am exporting this table from Oracle into a propriety application and I am trying to reduce teh size of the export table by removing any unecessary space the unused spaces in the column. This will benefit the local app in addition to reducing the impact on the filesystem storage.
I am working on an application that requires very dynamic access to data. Users will build queries through the interface and the queries will be stored in VARCHAR fields for later use. The function that later uses the query has no way of knowing the field names or data types used in the selection query.
This isn't an issue in any program language that I have used before but in this case, we want to do this in a package within Oracle rather than an external application.
how to reference a field by its location or position in the query.
We need to get the list of fields so we know what each field is named and we need to be able to get at its value dynamically as well. If possible, checking the data type would be useful too but that is less important in this case.If we were doing this in say PHP, we could simply reference the query row and use a command like...
foreach($myrow as $field=>$value)
and this would walk through each field in the row giving us the field name and its value.We need to do this same type of thing in our package.
I have column names stored in a table(say t1).
Inside a trigger I have to dynamically get the : old. values for only these columns.
This I need because during update trigger on a table (say t2) I am supposed pickup the coumn names stored in t1 and get the : old values of only these columns to insert into a new table .
I 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 wrote a simple procedure to copy the create timestamp and create user name to update timestamp and update user name of the same record. (See code below)
This works fine for a hard-coded table and primary key column. However, I cannot figure out how to get this to work with dynamic sql.
All my other procs, which don't use SELECT UPDATE FOR work fine with dynamic sql.
Proc that works:
CODECREATE OR REPLACE PROCEDURE proc_set_upd_columns
IS
CURSOR c1 IS
SELECT *
FROM mytable
FOR UPDATE
ORDER BY mycolumn;
c1rec c1%ROWTYPE;
[code].........
Partial proc that does NOT work:
CODECREATE OR REPLACE PROCEDURE LDEVORE.proc_set_upd_columns (
p_input_table_name IN VARCHAR2,
p_pk_id_col_name IN VARCHAR2)
IS
v_qry_str VARCHAR2(1000);
v_cursor_str VARCHAR2(1000);
v_create_tmstmp TIMESTAMP;
v_create_user_name VARCHAR2(30);
[code].......
finding out which instance is the resource master of a object.Also select * from V$GCSPFMASTER_INFO
returns zero rows.
Does this mean dynamic remastering is disabled???if so which instance will own a object?
i have creatd view,in which i need to show the entry of year in column wise,though the data is in row /record wise.By iteration i can manipulate the year in decode function like " (DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance))",but is there any way to chachge the alias name /column name,here is the query
select
SUM(DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance)) as year2010,
SUM(DECODE(a.Year,(select distinct max(year)from BI_BALANCE_SHEET) , a.Balance))as Year2011
from DEMO a
I do not know the table name but have a query that for sure returns the table name and I want to select a column value from the table into my PLSQL variable.
Here is the code :
DECLARE
v_VerTabName VARCHAR2(30);
v_Minor NUMBER(2);
[Code]....
But when I compile this I get ORA-00936 : Missing expression at the EXECUTE IMMEDIATE line.
I would like to use dynamic sql for an select query with where clause and then use the dynamic sql in pl/sql stored procedure. how to create dynamic sql (select query) and how to use it in pl/sql stored procedure.
View 4 Replies View RelatedI am using Oracle Form 6i. I have created pdf file from oracle report. On view button, I have written:
host('C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe C:dailypending.pdf');
I don't want to set this path on every machine. I want to set its dynamic path so that on which even I machine, I run the applicaiton, it will directly find AcroRd32.exe from any drive either C: or D: or anything else...
It might be Acrobat Reader 9.0 or 8.0 whatever version will be installed on client machine...I want to set path according to that..So that whatever version it would be, it doesnt affect the application..