I have a question regarding the optimal way to code a dynamic SELECT INTO statement. Below are the 2 posiibilities I know of:
1. Dynamically executing the SELECT statement and making use of the INTO clause of the EXECUTE IMMEDIATE
statement
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
[code]...
2. Encapsulating the SELECT INTO statement in a block and dynamically exectuting the block
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
[code]...
which way would be preferred? I know the second method uses a bind variable for the INTO clause, but does the first one also use bind varialbes (no semi-colon)? Any differences in terms of efficiency or speed?
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
I have one problem in trigger execution. I have a small plsql block in trigger and, I want to execute it as a dynamic way. but it is giving the error. Please find the trigger code. Here my intension is that, the column name used in trigger should be dynamic. In future, if I want to switch the column name, I have to do without modification in trigger. The error im getting is "ORA-01008: not all variables bound".
CREATE OR REPLACE TRIGGER ETM_AR_IU AFTER UPDATE ON EXTERNAL_MAPPING REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
T1 is having 2 columns t1_c1, t1_c2 T2 is having 3 columns t2_c1,t2_c2,t2_c3
I need to design a form which should be like
there must be a pop item (Dropdown menu) in a separate block.
If I select 1 from the pop item, T1 with 2 columns should display in a tabular form. If I select 2 from the pop item, T2 with 3 columns should display and T1 with 2 columns should hide.
So the form should be dynamic based on the selected pop item. Is there any chance to do this?
I am working on Oracle forms 10g (Windows 7 OS).I have got one requirement to show one LOV for COLOR table. Say i have three colors BLACK, RED, BLUE in color table. Suppose in a multi record block if a user selects BLACK from a lov for one record then for the next record lov it should show only RED and BLUE. How to filter the COLOR from a LOV record group query which are already selected?
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].......
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.
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.
While writing a procedure I went into this problem. Whenever I write Query : Select * from dba_pending_transactions It works fine.
But whenever I use same Select Query inside PL-SQL block it gives error Table or view not exist. Dba_pending_transactions is view.
SQL> declare 2 v_count number(2); 3 begin 4 execute immediate 'select count(*) from dba_ending_transactions' into v_count; 5 dbms_output.put_line(v_count); 6 end; 7 / declare * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at line 4
Same error I get when i use it inside a procedure.
I have a problem that i have hard coded the username.tablename in each select statement of all forms of my application. Now i want to use a dynamic variable in place of username in each select statement throughout the application. The example is:
select * from scott.emp and i want to write it as: select * from variable.emp
But at compilation of the form the compiler should know the above variable name.
I have tried to use following select statement but it does not work.
select user into :global.username from user_users
I think perhaps my problem would be solved with Dynamic SQL Statement but i have no experience by using this statement.
When a user selects a Plan Type (Parent Select list) Plan codes (Child select list) to be filtered, working OK. When a plan code is selected I need to do conditional display of regions. Example: Plan code = A then display Region A Plan code = B then display Region B etc.
I have a DA on Plan code select list but it's not working as it's being a cascaded (child) select list and the 'Change' event is already fired when I selected the Plan type, I also tried with other event options with no success.
how can i track the exception for three select statement in one pl-sql block. here is synario.......
begin select * from emp where empno=1234; --statement 1 select * from cusotmers where cust_id=125; --statement 2 select * from products where product_id='a-3'; --statement 3 end;
i want to track exception any one for ex no_data_found for all these three different statement.
I know if i put this three statement in three different pl-sql sublock then i can trap it....
SELECT SUM(salary) FROM employees WHERE department_id =60 O/P is : 28800But when i use the above query in anonymous block it gives me 684400 DECLARE v_sum_sal NUMBER;
[code]....
The above output statements gives me 684400 as output.. But the expected is 28800
I need to implement a "Select All" function in a Data Block with All the Check boxes. It's a Database data block with 10 records to be displayed at a time. Check Box Items are in default layout. The requirement is when user checks/un-checks that select all check box which is placed on the left hand side of the other check boxes, it should select/deselect all the check box database items in that particular record.
i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example
Cheque amount in master block = 291
Invoice wise receiveable amount is as follows
Invoice No , receivable amount , Received amount
10, 196 , 0 20 , 95 , 0 30 , 54 , 0
Result should be as follows:
Invoice No , receivable amount , Received amount
10, 196, 196 20 , 95 , 95 30 , 54, 0
Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
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]....
I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."
BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.
BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.
THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.
I 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?
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
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.
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.
