3

Would REPLACE do any good? See line #6 and result of DBMS_OUTPUT.PUT_LINE. SQL> create or replace procedure first_test(i_tx varchar2, o_count out number) is 2 qry varchar2(1000); 3 begin 4 qry := 'select count(*) from z_test_a where c_num = :1'; 5 6 dbms_output.put_line(replace(qry, ':1', i_tx)); --> like this 7 8 ...


2

You want to see employee name out of aggregated professions list - so you must concatenate it to aggregating expression, not to separate profession in arguments. If do this directly select cust_number, '['||MAX(cust_name)||':'||listagg(cust_profession,',') within group (order by cust_number)||']' Cust_profession from CUSTOMER_PROFESSION group by ...


2

Creating the symlink $ORACLE_HOME/lib/libobk.so pointing to the media management library is not a strict requirement. As part of RMAN channel allocation, the path of vendor speficic library can be specified in the SBT_LIBRARY parameter. I never tried this, but just by allocating the SBT channels with different parameters should make this work. UPDATE: For ...


2

The output shows that your sessions use the "Thin" driver (JDBC Thin Client), which is incapable of using TAF. Transparent Application Failover Transparent Application Failover (TAF) is a feature of the Java Database Connectivity (JDBC) Oracle Call Interface (OCI) driver. Sometimes the JDBC OCI driver is also called as the "Thick" ...


1

In Oracle 11g this cannot be audited. There are ways of doing it in 12c and up. That said, only users with the "select any dictionary" privilege should be able to select from that table, and anyone with that privilege should be trusted.


1

I would use regex_like: select column_value , case when regexp_like(' '||column_value||' ','\s(drop|execute|truncate|create|insert|update|delete|merge)\s', 'i') then 'Yes' end as illegal from table(ora_mining_varchar2_nt ('drop table customers', 'just dropping by', 'undroppable')); COLUMN_VALUE ...


1

I'm not sure there is a good answer. In general, string concatenation to build your SQL is always a bad idea and should be avoided whenever possible. That said, it would be difficult to avoid in your case except for replacing user_id references with bind variables. Other than that, you will have to come up with some custom logic to validate the content of ...


1

Well, if you have just one table, and your outer select is not accessing other tables, I would guess that "Second Location" would be the only one the optimizer looks at. In practice, easy to test: just do an explain plan on the two options, and see. Then invoke it both ways, and verify what happens. Oracle is pretty smart in doing the right ...


1

What you used there should not cause a problem, because it is a scalar subquery, meaning its result would be automatically cached, and as it has only 1 distinct value, even if test1 has 5 million rows, the inner query would not be executed 5 million times. But you can use the analytic version of count: select c_num, c_first_name, c_last_name, ...


1

How about constructing your dynamic query like this: with params (amount1, amount2) as ( select 100, 200 from dual ) select z.* from params p cross apply ( select c_num from z_test_a intersect select c_num from z_test_b where amount = p.amount1 -- add dynamically ) z; That last select under the ...


1

You got it wrong, I'm afraid. This is what your query evaluates to: with binds as (select :bind1 as amount1, :bind2 as amount2 from dual) select t.c_num from z_test_a t, binds b --> this is "B" where 1 = 1 intersect select c_num from z_test_b where amount = b.amount1 --&...


1

The value of sessions can not be lower than processes by definition. Even if you try to set it, the database will just ignore it: SESSIONS Default value Derived: (1.5 * PROCESSES) + 22 Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead. ...


1

Use DBMS_SQL. The fine manual has plenty of examples. Sample TBD


1

split the variable and fetch each element. That is, use a cursor


Only top voted, non community-wiki answers of a minimum length are eligible